JAVA poi 帮助类
pom.xml 添加引用:
<!--poi--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <!--ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
Java没有datatable,创建一个数据保存帮助类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.ArrayList; public class ExcelDO { public ExcelDO() { } public ExcelDO(String name) { this.name = name; } /* * sheet名 * */ private String name; public String getName() { return this.name; } public void setName(String name) { this.name = name; } /* * 二维集合,保存excel中的数据 * */ private ArrayList<ArrayList<String>> list; public ArrayList<ArrayList<String>> getList() { return this.list; } public void setList(ArrayList<ArrayList<String>> list) { this.list = list; } }
poi帮助类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package me.loveshare.springboot1.Common.Helper; import me.loveshare.springboot1.Entity.DO.ExcelDO; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.concurrent.atomic.AtomicReference; public class POIHelper { /** * 根据excel路径返回集合 */ public static ArrayList<ExcelDO> ReadExcel(String filePath) throws IOException { ArrayList<ExcelDO> list = new ArrayList<>(); Workbook workbook = GetWorkbook(filePath); // sheet总数 Integer sheetTotal = workbook.getNumberOfSheets(); for (Integer num = 0; num < sheetTotal; num++) { if (workbook.isSheetHidden(num)) continue; Sheet sheet = workbook.getSheetAt(num); ExcelDO excelDO = new ExcelDO(sheet.getSheetName()); ArrayList<ArrayList<String>> itemList = new ArrayList<ArrayList<String>>(); // 设置最大列,默认为1 Integer maxColumnNum = 0; // 不是有效列集合,连续超过三行不读取后续所有列 ArrayList<Integer> noValidColumnList = new ArrayList<>(); // 列:按照列把数据填充到datatable中,防止无限列出现 for (Integer columnIndex = 0; columnIndex <= maxColumnNum; columnIndex++) { noValidColumnList.add(columnIndex); // 列中所有数据都是null为true Boolean isAllEmpty = true; // 行 for (Integer rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { if (columnIndex == 0) itemList.add(new ArrayList<String>()); Row itemRow = sheet.getRow(rowIndex); if (itemRow == null) continue; maxColumnNum = maxColumnNum < itemRow.getLastCellNum() ? itemRow.getLastCellNum() : maxColumnNum; // 把格式转换为utf-8 String itemCellValue = StringHelper.FormatUtf8String(GetValue(itemRow, columnIndex)); if (!StringHelper.IsNullOrWhiteSpace(itemCellValue)) isAllEmpty = false; itemList.get(rowIndex).add(columnIndex, itemCellValue); } // 当前列有值 if (!isAllEmpty) noValidColumnList.clear(); // 连续空白列超过三行 或 有空白行且当前行为最后一行 else if (noValidColumnList.size() > 3 || (noValidColumnList.size() > 0 && columnIndex == maxColumnNum - 1)) { for (Integer i = noValidColumnList.size() - 1; i >= 0; i--) itemList.remove(i); break; } } // 得到一个sheet中有多少个合并单元格 Integer sheetMergeCount = sheet.getNumMergedRegions(); for (Integer i = 0; i < sheetMergeCount; i++) { // 获取合并后的单元格 CellRangeAddress range = sheet.getMergedRegion(i); String cellValue = itemList.get(range.getFirstRow()).get(range.getFirstColumn()); for (Integer mRowIndex = range.getFirstRow(); mRowIndex <= range.getLastRow(); mRowIndex++) { for (Integer mColumnIndex = range.getFirstColumn(); mColumnIndex <= range.getLastColumn(); mColumnIndex++) { itemList.get(mRowIndex).set(mColumnIndex, cellValue); } } } excelDO.setList(itemList); list.add(excelDO); } return list; } /* * 把集合中的数据保存为excel文件 * */ public static void SaveExcel(ArrayList<ExcelDO> doList, String fileDirectoryPath) { doList.forEach(item -> { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(item.getName()); ArrayList<ArrayList<String>> itemList = item.getList(); if (itemList != null || !itemList.isEmpty()) { for (Integer rowNum = 0; rowNum < itemList.size(); rowNum++) { ArrayList<String> rowList = itemList.get(rowNum); Row row = sheet.createRow(rowNum); for (Integer columnNum = 0; columnNum < rowList.size(); columnNum++) { Cell codeCell = row.createCell(columnNum); codeCell.setCellValue(rowList.get(columnNum)); } } } String filePath = fileDirectoryPath + item.getName() + ".xls"; try { OutputStream stream = new FileOutputStream(filePath);// 将workbook写到输出流中 workbook.write(stream); stream.flush(); stream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }); } // 根据文件路径,返回文档对象 public static Workbook GetWorkbook(String filePath) throws IOException { String extension = FileHelper.GetExtension(filePath); InputStream stream = new FileInputStream(filePath); //HSSF提供读写Microsoft Excel XLS格式档案的功能。(97-03) //XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。 //HWPF提供读写Microsoft Word DOC格式档案的功能。 //HSLF提供读写Microsoft PowerPoint格式档案的功能。 //HDGF提供读Microsoft Visio格式档案的功能。 //HPBF提供读Microsoft Publisher格式档案的功能。 //HSMF提供读Microsoft Outlook格式档案的功能。 switch (extension) { case "xls": return new HSSFWorkbook(stream); case "xlsx": case "xlsm": return new XSSFWorkbook(stream); } //抛出自定的业务异常 throw new Error("excel格式文件错误"); } /* * poi特殊日期格式:数字格式化成-yyyy年MM月dd日,格式 * */ private static ArrayList<String> PoiDateList = new ArrayList<String>() { { add("年"); add("月"); add("日"); } }; /// <summary> /// 获取XSSFRow的值(全部统一转成字符串) /// </summary> /// <param name="row"></param> /// <param name="index"></param> /// <returns></returns> public static String GetValue(Row row, int index) { Cell rowCell = row.getCell(index); return rowCell == null ? "" : GetValueByCellStyle(rowCell, rowCell.getCellType()); } /// <summary> /// 根据单元格的类型获取单元格的值 /// </summary> /// <param name="rowCell"></param> /// <param name="type"></param> /// <returns></returns> public static String GetValueByCellStyle(Cell rowCell, int rowCellType) { String value = ""; switch (rowCellType) { case Cell.CELL_TYPE_STRING: value = rowCell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: // 获取单元格值的格式化信息 String dataFormat = rowCell.getCellStyle().getDataFormatString(); // 判断格式化信息中是否存在:年月日 AtomicReference<Boolean> isDate = new AtomicReference<>(false); if (!StringHelper.IsNullOrWhiteSpace(dataFormat)) PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x))); if (DateUtil.isCellDateFormatted(rowCell)) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } else if (DateUtil.isCellInternalDateFormatted(rowCell)) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式 else if (isDate.get()) { value = new SimpleDateFormat("yyyy-MM-dd").format(rowCell.getDateCellValue()); } //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式 else if (dataFormat == null) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } else { if (StringHelper.IsNullOrWhiteSpace(dataFormat)) { value = String.valueOf(rowCell.getNumericCellValue()); } else { if (rowCell.getCellStyle().getDataFormatString().contains("$")) { value = "$" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) { value = "¥" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) { value = "¥" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("€")) { value = "€" + String.valueOf(rowCell.getNumericCellValue()); } else { value = String.valueOf(rowCell.getNumericCellValue()); } } } break; case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(rowCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: value = ErrorEval.getText(rowCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: // TODO: 是否存在 嵌套 公式类型 value = GetValueByCellStyle(rowCell, rowCell.getCachedFormulaResultType()); String cellvalue = String.valueOf(rowCell.getCellFormula()); break; default: System.out.println(rowCell); break; } return value; } }
StringHelper
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.io.UnsupportedEncodingException; public class StringHelper { /* 把特殊字符转换为utf-8格式 */ public static String FormatUtf8String(String str) throws UnsupportedEncodingException { if (IsNullOrWhiteSpace(str)) return ""; String newStr = changeCharset(str, "utf-8").trim(); return newStr; } /** * 字符串编码转换的实现方法 * * @param str 待转换编码的字符串 * @param newCharset 目标编码 * @return * @throws UnsupportedEncodingException */ public static String changeCharset(String str, String newCharset) throws UnsupportedEncodingException { if (IsNullOrWhiteSpace(str)) return ""; //用默认字符编码解码字符串。 byte[] bs = str.getBytes(); //用新的字符编码生成字符串 return new String(bs, newCharset); } /* 判断字符是否为空,为空返回true */ public static boolean IsNullOrWhiteSpace(String str) { return str == null || str.isEmpty() ? true : false; } }
FileHelper
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.io.File; public class FileHelper { // 返回指定的路径字符串的扩展名,不包含“。”,转小写 public static String GetExtension(String filePath) { File file = new File(filePath); String fileName = file.getName(); return fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()).toLowerCase(); } }