这个工具类基于POI,POM文件中加入
<!-- poi file upload --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.3.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-examples</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.2</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.2</version> </dependency>
接下来是工具类,注释应该能看懂,不懂在下方留言:
import java.io.*; import java.util.*; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import com.datadriver.core.util.Utils; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * POI工具类 功能点: * 1、实现excel的sheet复制,复制的内容包括单元的内容、样式、注释 * 2、setMForeColor修改HSSFColor.YELLOW的色值,setMBorderColor修改PINK的色值 * @Acthor:fyd * @ClassName: PoiUtil * @Date 2017-7-14 上午9:24:30 */ public final class PoiUtil { /** * * @Acthor:fyd * @Title: readExcel * @Date 2017-7-14 上午9:32:16 * @param targetWb 新的工作空间 * @param filePathAndName 文件路径/名称 * @param excelName 新的文件名称 * @param sheetName sheet 名称 * @throws Exception * @return void 返回类型 */ public static void readExcel(HSSFWorkbook targetWb, String filePathAndName, String excelName, String sheetName) throws Exception { // 打开已有的excel InputStream in = new FileInputStream(filePathAndName); HSSFWorkbook sourceWb = new HSSFWorkbook(in); for (int i = 0; i < sourceWb.getNumberOfSheets(); i++) { HSSFSheet sourceSheet = sourceWb.getSheetAt(i); copySheet(sourceSheet, targetWb, sourceWb, sheetName + "_" + sourceSheet.getSheetName()); } } /** * * @Acthor:fyd * @Title: readExcelT * @Date 2017-8-31 上午10:54:27 * @param targetWb * @param filePathAndName * @param excelName * @param sheetName * @throws Exception * @return void 返回类型 */ public static void readExcelT(HSSFWorkbook targetWb, String filePathAndName, String excelName, String sheetName) throws Exception { // 打开已有的excel InputStream in = new FileInputStream(filePathAndName); HSSFWorkbook sourceWb = new HSSFWorkbook(in); for (int i = 0; i < sourceWb.getNumberOfSheets(); i++) { HSSFSheet sourceSheet = sourceWb.getSheetAt(i); copySheet(sourceSheet, targetWb, sourceWb, sheetName); } } /** * 功能:拷贝sheet * 实际调用 copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true) * @param targetSheet * @param sourceSheet * @param targetWork * @param sourceWork */ public static void copySheet(HSSFSheet sourceSheet, HSSFWorkbook targetWork, HSSFWorkbook sourceWork, String sheetName) throws Exception{ if(Utils.isEmpty(sourceSheet) || Utils.isEmpty(targetWork) || Utils.isEmpty(sourceWork) || Utils.isEmpty(sheetName)){ throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!"); } HSSFSheet targetSheet = targetWork.createSheet(sheetName); copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true); } /** * 功能:拷贝sheet * @param targetSheet * @param sourceSheet * @param targetWork * @param sourceWork * @param copyStyle boolean 是否拷贝样式 */ public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet, HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception { if(Utils.isEmpty(targetSheet) || Utils.isEmpty(sourceSheet) || Utils.isEmpty(targetWork) || Utils.isEmpty(sourceWork)){ throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!"); } //复制源表中的合并单元格 mergerRegion(targetSheet, sourceSheet); //复制源表中的行 int maxColumnNum = 0; Map styleMap = (copyStyle) ? new HashMap() : null; //设置目标sheet的列宽 for (int i = 0; i <= maxColumnNum; i++) { targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i)); } HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释 for (Iterator rowIt = sourceSheet.rowIterator(); rowIt.hasNext();) { HSSFRow sourceRow = (HSSFRow) rowIt.next(); HSSFRow targetRow = targetSheet.createRow(sourceRow.getRowNum()); if (!Utils.isEmpty(sourceRow)) { //行复制 copyRow(targetRow, sourceRow, targetWork, sourceWork,patriarch, styleMap); } if (sourceRow.getLastCellNum() > maxColumnNum) { maxColumnNum = sourceRow.getLastCellNum(); } } } /** * 功能:拷贝row * @param targetRow * @param sourceRow * @param styleMap * @param targetWork * @param sourceWork * @param targetPatriarch */ public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception { if(Utils.isEmpty(targetRow) || Utils.isEmpty(sourceRow) || Utils.isEmpty(targetWork) || Utils.isEmpty(sourceWork)){ throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时,targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空,故抛出该异常!"); } //设置行高 targetRow.setHeight(sourceRow.getHeight()); for (Iterator cellIt = sourceRow.cellIterator(); cellIt.hasNext();) { HSSFCell sourceCell = (HSSFCell) cellIt.next(); if (sourceCell != null) { HSSFCell targetCell = targetRow.createCell(sourceCell.getColumnIndex()); //拷贝单元格,包括内容和样式 copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap); //拷贝单元格注释 copyComment(targetCell,sourceCell,targetPatriarch); } } } /** * 功能:拷贝cell,依据styleMap是否为空判断是否拷贝单元格样式 * @param targetCell 不能为空 * @param sourceCell 不能为空 * @param targetWork 不能为空 * @param sourceWork 不能为空 * @param styleMap 可以为空 */ public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) { if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){ throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时,targetCell、sourceCell、targetWork、sourceWork都不能为空,故抛出该异常!"); } //处理单元格样式 if(styleMap != null){ if (targetWork == sourceWork) { targetCell.setCellStyle(sourceCell.getCellStyle()); } else { String stHashCode = "" + sourceCell.getCellStyle().hashCode(); HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap .get(stHashCode); if (targetCellStyle == null) { targetCellStyle = targetWork.createCellStyle(); targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle()); styleMap.put(stHashCode, targetCellStyle); } targetCell.setCellStyle(targetCellStyle); } } //处理单元格内容 switch (sourceCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(sourceCell.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: targetCell.setCellErrorValue(sourceCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: targetCell.setCellFormula(sourceCell.getCellFormula()); break; default: break; } } /** * 功能:拷贝comment * @param targetCell * @param sourceCell * @param targetPatriarch */ public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{ if(targetCell == null || sourceCell == null || targetPatriarch == null){ throw new IllegalArgumentException("调用PoiUtil.copyCommentr()方法时,targetCell、sourceCell、targetPatriarch都不能为空,故抛出该异常!"); } //处理单元格注释 HSSFComment comment = sourceCell.getCellComment(); if(comment != null){ HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor()); newComment.setAuthor(comment.getAuthor()); newComment.setColumn(comment.getColumn()); newComment.setFillColor(comment.getFillColor()); newComment.setHorizontalAlignment(comment.getHorizontalAlignment()); newComment.setLineStyle(comment.getLineStyle()); newComment.setLineStyleColor(comment.getLineStyleColor()); newComment.setLineWidth(comment.getLineWidth()); newComment.setMarginBottom(comment.getMarginBottom()); newComment.setMarginLeft(comment.getMarginLeft()); newComment.setMarginTop(comment.getMarginTop()); newComment.setMarginRight(comment.getMarginRight()); newComment.setNoFill(comment.isNoFill()); newComment.setRow(comment.getRow()); newComment.setShapeType(comment.getShapeType()); newComment.setString(comment.getString()); newComment.setVerticalAlignment(comment.getVerticalAlignment()); newComment.setVisible(comment.isVisible()); targetCell.setCellComment(newComment); } } /** * 功能:复制原有sheet的合并单元格到新创建的sheet * * @param sheetCreat * @param sourceSheet */ public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception { if(targetSheet == null || sourceSheet == null){ throw new IllegalArgumentException("调用PoiUtil.mergerRegion()方法时,targetSheet或者sourceSheet不能为空,故抛出该异常!"); } for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) { CellRangeAddress oldRange = sourceSheet.getMergedRegion(i); CellRangeAddress newRange = new CellRangeAddress( oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(), oldRange.getLastColumn()); targetSheet.addMergedRegion(newRange); } } /** * 功能:重新定义HSSFColor.YELLOW的色值 * * @param workbook * @return */ public static HSSFColor setMForeColor(HSSFWorkbook workbook) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; byte[] rgb = { (byte) 221, (byte) 241, (byte) 255 }; try { hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]); if (hssfColor == null) { palette.setColorAtIndex(HSSFColor.YELLOW.index, rgb[0], rgb[1], rgb[2]); hssfColor = palette.getColor(HSSFColor.YELLOW.index); } } catch (Exception e) { e.printStackTrace(); } return hssfColor; } /** * 功能:重新定义HSSFColor.PINK的色值 * * @param workbook * @return */ public static HSSFColor setMBorderColor(HSSFWorkbook workbook) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; byte[] rgb = { (byte) 0, (byte) 128, (byte) 192 }; try { hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]); if (hssfColor == null) { palette.setColorAtIndex(HSSFColor.PINK.index, rgb[0], rgb[1], rgb[2]); hssfColor = palette.getColor(HSSFColor.PINK.index); } } catch (Exception e) { e.printStackTrace(); } return hssfColor; } /*** * 把Excel文件返回List集合 * @param f * @param filePath * @return */ public static List excelToList(File f, String filePath){ List list=null;//返回对象 if(null==f && (null==filePath || "".equals(filePath))){ return null; } File file = f; if(null!=filePath && !"".equals(filePath)){//已指定文件路径 file = new File(filePath); } String filename = file.getName(); if (filename == null || "".equals(filename)){ } try { InputStream input = new FileInputStream(file); Workbook workBook = null; //以下做法是为了区分不同版本,然后使用XSSF或HSSF String flag="";//XSSF或HSSF的标记 try { workBook = new XSSFWorkbook(input); flag="XSSF"; } catch (Exception ex) { workBook = new HSSFWorkbook(new FileInputStream(file)); flag="HSSF"; } //根据标记使用不同的方法来解析excel if("XSSF".equals(flag)){ XSSFSheet sheet = (XSSFSheet) workBook.getSheetAt(0); if (sheet != null) { //list list = new ArrayList(); for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { //object XSSFRow row = sheet.getRow(i); List obj = new ArrayList(); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { //column XSSFCell cell = row.getCell(j); String cellStr = cell.toString(); obj.add(cellStr); } System.out.println(); list.add(obj); } } }else if ("HSSF".equals(flag)){ HSSFSheet sheet = (HSSFSheet) workBook.getSheetAt(0); if (sheet != null) { //list list = new ArrayList(); for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { //object HSSFRow row = sheet.getRow(i); List obj = new ArrayList(); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { //column HSSFCell cell = row.getCell(j); String cellStr = cell.toString(); obj.add(cellStr); } System.out.println(); list.add(obj); } } }else{ } } catch (Exception e) { e.printStackTrace(); } return list; } }