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)    // 判断需要""

本文由 SAn 创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2018/04/11 18:18

excel函数调研

poi包excel函数使用

常用Excel函数

poi导入

poi导出

Excel锁定不可修改

注意