Java写一个excel工具类_(Java) Excel工具类

packagecom.business.platform.service.common;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;/*** @description: Exl工具类

*@author: zyu

* @time: 2020-07-18 14:38*/

public classExLUtils {/*** 读取Excel文件内容

*@return

*/

public static List>readExcel() {/*Excel文件地址*/String filePath= "F:\\商户发展情况日报表.xlsx";

String strField= "no,proxy_name_2,increase_day,increase_month,increase_year,total_num,no_trade_num";

String[] fieldArr= strField.split(",");

List> retList = new ArrayList<>();try{/*** HSSFworkbook,XSSFworkbook,SXSSFworkbook区别总结

*

* HSSFWorkbook:是操作Excel2003以前(包括2003)的版本，扩展名是.xls； 【导出的行数至多为65535行，超出65536条后系统就会报错】

* XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx； 【其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行】

* SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx； 【不会内存溢出】*/

/*创建Excel工作本的引用*/Workbook workbook= new XSSFWorkbook(newFileInputStream(filePath));/*读取第一张工作表 Sheet1*/Sheet sheet= workbook.getSheet("Sheet1");

System.out.println(sheet.getLastRowNum());for (int i = 0; i <= sheet.getLastRowNum(); i++) {/*获取每一行*/Row row=sheet.getRow(i);

Map itemMap = new HashMap<>();for (int cellNum = 0; cellNum < fieldArr.length; cellNum++) {

String key=fieldArr[cellNum];

String item=row.getCell(cellNum).toString();

itemMap.put(key, item);

}

retList.add(itemMap);

}

System.out.println(retList);

}catch(Exception e) {

System.out.println(e);

}returnretList;

}/*** 写出数据到Excel工作簿

*

*@paramworkbook 工作簿

*@paramheaderCount 表头高度

*@paramoffsetRow 偏移行

*@paramdataList 数据

*@parammapper 映射关系

*@return下次开始行

*

*

我该如何使用?

*@seeExportUtils#textExport*/

public static int writeExcel(Workbook workbook, int headerCount, int offsetRow, List> dataList, Mapmapper) {for (int i = 0; i < mapper.size(); i++) {

String value=mapper.get(i);if (value == null) {throw new RuntimeException("映射关系有错误");

}

}

Sheet sheet= workbook.getSheet("Sheet1");for (Mapdata : dataList) {

Row row= sheet.createRow(headerCount + offsetRow++);for (int i = 0; i < mapper.size(); i++) {

String columnName=mapper.get(i);if(columnName.isEmpty()) {continue;

}

Cell cell=row.createCell(i);if ("$AUTO".equals(columnName)) {

cell.setCellValue(offsetRow);continue;

}

Object value=data.get(columnName);if (value == null) {

cell.setCellValue("");continue;

}if (value instanceofString) {

cell.setCellValue((String) value);

}else if (value instanceofInteger) {

cell.setCellValue((Integer) value);

}else if (value instanceofDouble) {

cell.setCellValue((Double) value);

}else{

cell.setCellValue(value.toString());

}

}

}returnoffsetRow;

}/*** 测试写入*/

public static voidtextExport() {try{

String inputFilePath= "F:\\inputExcel.xlsx";

String outputFilePath= "F:\\outputExcel.xlsx";/*关系映射表*/Map mapper = new HashMap<>();

mapper.put(0, "$AUTO"); //序号定义

mapper.put(1, "proxy_name_2");

mapper.put(2, "increase_day");

mapper.put(3, "increase_month");

mapper.put(4, "increase_year");

mapper.put(5, "total_num");

mapper.put(6, "no_trade_num");/*查询数据*/List> dataList = new ArrayList<>();

FileInputStream inputStream= newFileInputStream(inputFilePath);

FileOutputStream outputStream= newFileOutputStream(outputFilePath);

Workbook workbook=WorkbookFactory.create(inputStream);int headerCount = 2; //设置表头高度

int offsetRow = 0; //初始偏移量0//写行数据

writeExcel(workbook, headerCount, offsetRow, dataList, mapper);

workbook.write(outputStream);

inputStream.close();

outputStream.close();

}catch(Exception e) {

e.printStackTrace();

}

}public static voidmain(String[] args) {

textExport();

}

}