excel函数调研

/ 技术 / 无站内评论 / 383浏览

poi包excel函数使用

文章内容包括: 1.常用函数 2.poi导入 3.poi导出 4.Excel锁定不可修改 5.注意

常用Excel函数

SUM(C3:D3:E3);              // 求和函数
AVERGE(C3:D3:E3);              // 求平均值
MAX(C3:D3:E3);                 // 求最大值
COUNTIF(C3:C20,">90");           // 统计满足条件的个数
IF(C3<60,"不及格","及格");     // 判断是否满足条件
ABS(C3);                       // 求绝对值
SUMIF(C3:C10,">=80",E3:E10)    // 第i行的C列满足条件则将i行的列求和

poi导入

首先需要添加poi的jar,导入需要的对象包括:输入流、工作簿、sheet表、row行、cell单元格;

// 创建输入流,读取Excel
InputStream is = file.getInputStream();
// jxl提供的Workbook类
Workbook wb = new XSSFWorkbook(is);
// Excel的页签数量
int sheet_size = wb.getNumberOfSheets();
// 创建一个sheet页对象
Sheet sheet = wb.getSheetAt(0);
// 获取某一行数据
Row row=sheet.getRow(i);
// 获取单元格
row.getCell(0)
// 获取单元格内容,含有公式时需要使用第二个
row.getCell(j).getStringCellValue();
row.getCell(j).getNumericCellValue();

EXCEL存在着系统自带函数与用户自定义函数,系统函数如一些数字、字符、数组、日期、筛选等函数;自定义函数用户可以通过VBA进行设计。但是无论是哪种函数,都是动态确定的,不可能在代码中写死。那么,实际运行函数时就必须用到反射。

// 创建对象实例
Object object=className.newInstance();
// 获取对象相关属性名
Field[] fields=className.getDeclaredFields();
// 给对象相关属性赋值
fields[j].setAccessible(true);
fields[j].set(object, row.getCell(j).getStringCellValue());

poi导出

导出与导入基本相同,将get方法改成set方法;

//创建工作簿  
HSSFWorkbook workBook = new HSSFWorkbook();  
// 创建工作表,工作表的名字叫helloWorld  
HSSFSheet sheet = workBook.createSheet("helloWorld");  
// 创建行,第3行  
HSSFRow row = sheet.createRow(2);  
// 创建单元格,操作第三行第三列  
HSSFCell cell = row.createCell(2, CellType.STRING); 
// 给单元格赋值
cell.setCellValue("helloWorld");

单元格使用函数,先设置单元格为函数格式,然后给单元格设置函数,但是不设置单元格格式也成功了。函数可以i直接使用excel表格中的函数,在""中添加函数即可。

// 单元格设置为函数格式
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
// 设置函数
cell.setCellFormula("SUM(C2:C3)" );

将文件进行输出,在类中先获取日志,否则后面的logger会报错:

private static final Logger logger = LoggerFactory.getLogger(TaskMessageInfoServiceImpl.class);

输出文件:

//使用输出流进行文件下载
String filename = "name.xls";
response.setContentType("application/vnd.ms-excel");
ServletOutputStream out = null;
//获取浏览器类型
String agent = request.getHeader("User-Agent");
    try {
         out = response.getOutputStream();
         filename =FileUtils.encodeDownloadFilename(filename, agent);
         response.setHeader("content-disposition", "attachment;filename=" + filename);
         wb.write(out);
         logger.info("任务报表导出");
        } catch (IOException e) {
             logger.error("outputStream exception");
        } finally {
          try {
             if (out != null) {
             out.close();}
       } catch (IOException e) {
             logger.error("close outStream faild");
            }
        }

Excel锁定不可修改

目前所了解的方法,如果需要锁定部分区域不可修改,需要先将整个sheet锁定,然后对不需要锁定的单元格进行解锁。

// 锁定整个sheet,并设置解锁密码
sheet.protectSheet("String");
// 设置不锁定的样式
HSSFCellStyle ss = workbook.createCellStyl();
ss.setLocked(false);
// 创建单元格
cell=row.createCell(cellNum);
// 将单元格设置为不锁定的样式
cell.setCellStyle(ss);

注意

函数公式可直接在EXCEL中COPY

在编程时,需要对""使用转义字符,从此文章复制会自动变成转义字符

// 实际编程如下,从此文章复制则需去掉多余的\\
c3.setCellFormula("SUMIF(C3:C10,\">=80\",E3:E10)");

函数的内部需要进行判断的,一些需要"",一些不能有"",需要看excel的公式里面是否有"";

IF(C3<60,"不及格","及格");     // 判断不能有""
SUMIF(C3:C10,">=80",E3:E10)    // 判断需要""
召唤蕾姆
琼ICP备18000156号

鄂公网安备 42011502000211号