maven依赖：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.1.0</version> </dependency>

工具类：

1.代码都是活的可以根据自己的业务修改，比如读取返还的数据格式不爽可以修改。

2.catch部分建议使用工程的日志系统 把异常记录。

package hoperun.pagoda.dxlAnalyse.utils; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * Use poi Operate the Excel. * @author zy */ public final class PoiUtils { /** * writeWorkbook. */ private static Workbook writeWorkbook; /** * readworkbook. */ private static Workbook readworkbook; /** * outFilepath. */ private static String writeFilePath; /** * readFilepath. */ private static String readFilePath; /** * excel xls. */ public static final String OFFICE_EXCEL_XLS = "xls"; /** * excel xlsx. */ public static final String OFFICE_EXCEL_XLSX = "xlsx"; /** * font size 12. */ public static final int FONT_SIZE_12 = 12; /** * cell width 15. */ public static final int CELL_DEFAULT_WIDTH = 20; /** * font size 16. */ public static final int FONT_SIZE_16 = 16; /** * construction. */ private PoiUtils() { } /** * Gets the Workbook object based on the file path. * @param filepath1 filepath */ public static void getWorkbook(final String filepath1) { readFilePath = filepath1; InputStream is = null; if (readFilePath.isEmpty()) { throw new IllegalArgumentException("The file path cannot be empty"); } else { String suffiex = getSuffiex(readFilePath); if (suffiex.isEmpty()) { throw new IllegalArgumentException("The file suffix cannot be empty"); } if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) { try { is = new FileInputStream(readFilePath); readworkbook = WorkbookFactory.create(is); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (EncryptedDocumentException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { try { if (is != null) { is.close(); } if (readworkbook != null) { readworkbook.close(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } else { throw new IllegalArgumentException("This file is not an Excel"); } } } /** * Also read the contents of the specified Sheet. * @param sheetNo The sheet number, starting at 0, is set to null if sheetNo reads the full text. * @return excel content. */ public static String readExcel(final Integer sheetNo) { StringBuilder sb = new StringBuilder(); try { getWorkbook(readFilePath); if (readworkbook != null) { if (sheetNo == null) { int numberOfSheets = readworkbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = readworkbook.getSheetAt(i); if (sheet == null) { continue; } sb.append(readExcelSheet(sheet)); } } else { Sheet sheet = readworkbook.getSheetAt(sheetNo); if (sheet != null) { sb.append(readExcelSheet(sheet)); } } } } catch (EncryptedDocumentException e) { // TODO Auto-generated catch block e.printStackTrace(); } return sb.toString(); } /** * read Excel Sheet. * @param sheet sheet * @return String */ private static String readExcelSheet(final Sheet sheet) { StringBuilder sb = new StringBuilder(); if (sheet != null) { int rowNos = sheet.getLastRowNum();// Get the total number of records in excel for (int i = 0; i <= rowNos; i++) { Row row = sheet.getRow(i); if (row != null) { int columNos = row.getLastCellNum(); for (int j = 0; j < columNos; j++) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellType(CellType.STRING); sb.append(cell.getStringCellValue() + " "); } } } } } return sb.toString(); } /** * Create Workbook. * @param filepath1 filepath. */ public static void createWorkbook(final String filepath1) { writeFilePath = filepath1; if (writeFilePath.isEmpty()) { throw new IllegalArgumentException("文件路径不能为空"); } else { String suffiex = getSuffiex(writeFilePath); if (suffiex.isEmpty()) { throw new IllegalArgumentException("文件后缀不能为空"); } if (OFFICE_EXCEL_XLS.equals(suffiex.toLowerCase())) { writeWorkbook = new HSSFWorkbook(); } else { writeWorkbook = new XSSFWorkbook(); } } } /** * creat Excel file. * @param sheetName new sheet name. * @param allData all data. */ public static void creatSheet(final String sheetName, final List<List<Object>> allData) { //creat sheet. Sheet sheet; if (sheetName.isEmpty()) { // use default sheet name. sheet = writeWorkbook.createSheet(); } else { sheet = writeWorkbook.createSheet(sheetName); } //set column width to default. sheet.setDefaultColumnWidth((short) CELL_DEFAULT_WIDTH); //creat style. Map<String, CellStyle> styles = createStyles(writeWorkbook); for(int rowIndex = 0; rowIndex<allData.size();rowIndex++) { Row row = sheet.createRow(rowIndex); List<Object> cellsData = allData.get(rowIndex); for(int cellIndex = 0;cellIndex<cellsData.size();cellIndex++) { Cell cell = row.createCell(cellIndex); if(rowIndex == 0) { cell.setCellStyle(styles.get("header")); }else if ((rowIndex & 1)==1) { //w 设置cell的样式 cell.setCellStyle(styles.get("cellA")); } else { cell.setCellStyle(styles.get("cellB")); } Object cellData = cellsData.get(cellIndex); //Determine the type of object. SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if (cellData instanceof Double) { cell.setCellValue((Double) cellData); } else if (cellData instanceof Date) { String time = simpleDateFormat.format(cellData); cell.setCellValue(time); } else if (cellData instanceof Calendar) { Calendar calendar = (Calendar) cellData; String time = simpleDateFormat.format(calendar.getTime()); cell.setCellValue(time); } else if (cellData instanceof Boolean) { cell.setCellValue((Boolean) cellData); } else { if (cellData != null) { cell.setCellValue(cellData.toString()); } } } } } /** * write Execute. * @return result */ public static boolean writeExcel() { boolean result = false; OutputStream outputStream = null; try { outputStream = new FileOutputStream(writeFilePath); writeWorkbook.write(outputStream); result = true; } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if (outputStream != null) { outputStream.close(); } if (writeWorkbook != null) { writeWorkbook.close(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return result; } /** * set style. * @param wb Workbook. * @return style Map. */ private static Map<String, CellStyle> createStyles(final Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); //title style. CellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐 titleStyle.setLocked(true); // 样式锁定 titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) FONT_SIZE_16); titleFont.setBold(true); titleFont.setFontName("微软雅黑"); titleStyle.setFont(titleFont); styles.put("title", titleStyle); //header style. CellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); // 前景色 headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜色填充方式 headerStyle.setWrapText(true); headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界 headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) FONT_SIZE_12); headerFont.setColor(IndexedColors.WHITE.getIndex()); titleFont.setFontName("微软雅黑"); headerStyle.setFont(headerFont); styles.put("header", headerStyle); Font cellStyleFont = wb.createFont(); cellStyleFont.setFontHeightInPoints((short) FONT_SIZE_12); cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex()); cellStyleFont.setFontName("微软雅黑"); //content style A. CellStyle cellStyleA = wb.createCellStyle(); cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置 cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleA.setWrapText(true); cellStyleA.setBorderRight(BorderStyle.THIN); cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderLeft(BorderStyle.THIN); cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderTop(BorderStyle.THIN); cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderBottom(BorderStyle.THIN); cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setFont(cellStyleFont); styles.put("cellA", cellStyleA); //content style A:The foreground color is light yellow. CellStyle cellStyleB = wb.createCellStyle(); cellStyleB.setAlignment(HorizontalAlignment.CENTER); cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyleB.setWrapText(true); cellStyleB.setBorderRight(BorderStyle.THIN); cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderLeft(BorderStyle.THIN); cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderTop(BorderStyle.THIN); cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderBottom(BorderStyle.THIN); cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setFont(cellStyleFont); styles.put("cellB", cellStyleB); return styles; } /** * get suffix. * @param path path. * @return suffix */ private static String getSuffiex(final String path) { if ( path.isEmpty()) { return ""; } int index = path.lastIndexOf("."); if (index == -1) { return ""; } return path.substring(index + 1, path.length()); } }

写入调用：

// 创建workbook String outFilePath = "c://test.xls"; PoiUtils.createWorkbook(outFilePath); //创建第一个sheet并写入数据 List<List<Object>> formDate = new ArrayList<List<Object>>(); List<Object> formTitle = Arrays.asList("Form一览","form名","Filed数","Button数","Code量"); formDate.add(....);//添加行的内容 disposalFormData(formCountMap, formDate); String sheetName1 = "sheet名字"; PoiUtils.creatSheet(sheetName1, formDate); //创建第二个sheet并写入数据 //...... //把workboot写入文件 PoiUtils.writeExcel()

读取调用：

String filePath = "c://test.xls"; PoiUtils.getWorkbook(filePath); //读第一个sheet内容 String content = PoiUtils.readExcel(0); //读所有sheet内容 String content1 = PoiUtils.readExcel(null);

poi 设置超链接

public void testSetHyperLink() { XSSFWorkbook workbook = new XSSFWorkbook(); try { FileOutputStream out = new FileOutputStream("d:\\testSetHyperLink.xlsx"); XSSFSheet sheet = workbook.createSheet("1"); XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setUnderline(XSSFFont.U_DOUBLE); font.setColor(IndexedColors.RED.getIndex()); style.setFont(font); CreationHelper createHelper = workbook.getCreationHelper(); XSSFCellStyle cellStyle = workbook.createCellStyle(); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue("test.xlsx"); XSSFFont font1 = workbook.createFont(); font1.setColor(HSSFColor.GREEN.index); cellStyle.setFont(font1); cell.setCellStyle(cellStyle); Hyperlink hyperlink = createHelper.createHyperlink(Hyperlink.LINK_FILE); hyperlink.setAddress("C://Users//Administrator//Desktop//test.xlsx"); cell.setHyperlink(hyperlink); workbook.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }