POI操作Excel(批量导出数据/下载excel)
1、第一个demo:创建工作簿,创建sheet页,创建单元格
2、创建一个时间格式的单元格
3、遍历工作簿的行和列并获取单元格内容
4、文本提取
5、单元格对齐方式
6、单元格边框处理
7、单元格填充色和颜色操作
8、单元格合并
9、字体处理
10、读取和重写工作簿
11、单元格中使用换行
12、创建用户自定义数据格式
13、批量导出数据
14、demo: 利用 poi 实现数据的批量导出
15、demo: 利用 poi 技术实现使用模版批量导出数据
1、第一个demo:创建工作簿,创建sheet页,创建单元格 <--返回目录
导包:poi-3.9-20121203.jar
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(0); // 创建一个行 Cell cell = row.createCell(0); // 创建一个单元格 第1列 cell.setCellValue(1); // 给单元格设置值 row.createCell(1).setCellValue(1.2); // 创建一个单元格 第2列 值是1.2 row.createCell(2).setCellValue("这是一个字符串类型"); // 创建一个单元格 第3列 值为一个字符串 row.createCell(3).setCellValue(false); // 创建一个单元格 第4列 值为布尔类型 FileOutputStream fileOut = new FileOutputStream("d:\\Poi生成的excel.xls"); wb.write(fileOut); fileOut.close(); }
2、创建一个时间格式的单元格 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(0); // 创建一个行 Cell cell = row.createCell(0); // 创建一个单元格 第1列 cell.setCellValue(new Date()); // 给单元格设置值 // 单元格样式类 CellStyle cellStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy-mm-dd hh:mm:ss")); cell = row.createCell(1); // 第二列 cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); cell = row.createCell(2); // 第三列 cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
3、遍历工作簿的行和列并获取单元格内容 <--返回目录
public static void main(String[] args) throws Exception { InputStream is = new FileInputStream("d:\\工作簿1.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页 if (hssfSheet == null) return; // 遍历行Row for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // 遍历列Cell for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) { HSSFCell hssfCell = hssfRow.getCell(cellNum); if (hssfCell == null) { continue; } System.out.print(" " + getValue(hssfCell)); } System.out.println(); } } private static String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(hssfCell.getNumericCellValue()); } else { return String.valueOf(hssfCell.getStringCellValue()); } }
4、文本提取 <--返回目录
public static void main(String[] args) throws Exception { InputStream is = new FileInputStream("d:\\工作簿1.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); ExcelExtractor excelExtractor = new ExcelExtractor(wb); // false:不打印Sheet页的名字 excelExtractor.setIncludeSheetNames(true); System.out.println(excelExtractor.getText()); }
5、单元格对齐方式 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(2); // 创建一个行 row.setHeightInPoints(30); // 设置行高 createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); } /** * 创建一个单元格并为其设定指定的对齐方式 * * @param wb 工作簿 * @param row 行 * @param column 列 * @param halign 水平方向对齐方式 * @param valign 垂直方向对齐方式 */ private static void createCell(Workbook wb, Row row, short column, short halign, short valign) { CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式 cellStyle.setAlignment(halign); // 设置单元格水平方向对齐方式 cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对齐方式 Cell cell = row.createCell(column); // 创建单元格 cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值 cell.setCellStyle(cellStyle); // 设置单元格样式 }
6、单元格边框处理 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(1); // 创建一个行 Cell cell = row.createCell(1); // 创建一个单元格 cell.setCellValue(4); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框 cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex()); // 底部边框颜色 cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框 cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色 cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框 cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); // 右边边框颜色 cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框 cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色 cell.setCellStyle(cellStyle); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
7、单元格填充色和颜色操作 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(1); // 创建一个行 Cell cell = row.createCell(1); cell.setCellValue("abc"); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 背景色 cellStyle.setFillPattern(CellStyle.BIG_SPOTS); // 填充方式 cell.setCellStyle(cellStyle); Cell cell2 = row.createCell(2); cell2.setCellValue("ABC"); CellStyle cellStyle2 = wb.createCellStyle(); cellStyle2.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色 cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充方式 cell2.setCellStyle(cellStyle2); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
8、单元格合并 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(1); // 创建一个行 Cell cell = row.createCell(1); cell.setCellValue("单元格合并测试"); sheet.addMergedRegion(new CellRangeAddress(1, // 起始行 2, // 结束行 1, // 其实列 2 // 结束列 )); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
結果:
9、字体处理 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(1); // 创建一个行 // 创建一个字体处理类 Font font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); CellStyle style = wb.createCellStyle(); style.setFont(font); Cell cell = row.createCell((short) 1); cell.setCellValue("This is test of fonts"); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
10、读取和重写工作簿 <--返回目录
public static void main(String[] args) throws Exception { InputStream inp = new FileInputStream("d:\\工作簿.xls"); POIFSFileSystem fs = new POIFSFileSystem(inp); Workbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); // 获取第一个Sheet页 Row row = sheet.getRow(0); // 获取第一行 Cell cell = row.getCell(0); // 获取第一个单元格 if (cell == null) { cell = row.createCell(3); } cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("测试单元格"); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
11、单元格中使用换行 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 Row row = sheet.createRow(2); // 创建一个行 Cell cell = row.createCell(2); cell.setCellValue("我要换行 \n 成功了吗?"); CellStyle cs = wb.createCellStyle(); // 设置可以换行 cs.setWrapText(true); cell.setCellStyle(cs); // 调整下行的高度 row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints()); // 调整单元格宽度 sheet.autoSizeColumn(2); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
12、创建用户自定义数据格式 <--返回目录
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿 Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页 CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(111111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); // 设置数据格式 cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(1111111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls"); wb.write(fileOut); fileOut.close(); }
13、批量导出数据 <--返回目录
public String export()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); Workbook wb=new HSSFWorkbook(); String headers[]={"编号","姓名","电话","Email","QQ"}; ResultSet rs=userDao.userList(con, null); ExcelUtil.fillExcelData(rs, wb, headers); ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; }
ExcelUtil
package com.oy; import java.sql.ResultSet; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelUtil { public static void fillExcelData(ResultSet rs, Workbook wb, String[] headers) throws Exception { int rowIndex = 0; Sheet sheet = wb.createSheet(); Row row = sheet.createRow(rowIndex++); for (int i = 0; i < headers.length; i++) { row.createCell(i).setCellValue(headers[i]); } while (rs.next()) { row = sheet.createRow(rowIndex++); for (int i = 0; i < headers.length; i++) { row.createCell(i).setCellValue(rs.getObject(i + 1).toString()); } } } }
ResponseUtil
package com.oy; import java.io.OutputStream; import java.io.PrintWriter; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.Workbook; public class ResponseUtil { public static void write(HttpServletResponse response, Object o) throws Exception { response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); out.print(o.toString()); out.flush(); out.close(); } public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
14、demo: 利用 poi 实现数据的批量导出 <--返回目录
依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>
PoiController
@Controller public class PoiController { @RequestMapping("/poi/export") public void get(HttpServletResponse response) throws Exception { Workbook wb = new HSSFWorkbook(); String headers[] = { "id", "name", "mobileNum", "email" }; List<User> userList = new ArrayList<>(); User user1 = new User(1, "张三", "13512345678", "123@163.com"); User user2 = new User(1, "张三", "13512345678", "123@163.com"); User user3 = new User(1, "张三", "13512345678", "123@163.com"); userList.add(user1); userList.add(user2); userList.add(user3); ExcelUtil.fillExcelData(userList, wb, headers); ResponseUtil.export(response, wb, "导出excel.xls"); } }
User
public class User { private Integer id; private String name; private String mobileNum; private String email; public User() { } public User(Integer id, String name, String mobileNum, String email) { this.id = id; this.name = name; this.mobileNum = mobileNum; this.email = email; } // getter和setter方法省略 public Integer getId() { return id; } }
ExcelUtil
public class ExcelUtil { public static void fillExcelData(List<User> userList, Workbook wb, String[] headers) throws Exception { int rowIndex = 0; Sheet sheet = wb.createSheet(); Row row = sheet.createRow(rowIndex++); for (int i = 0; i < headers.length; i++) { row.createCell(i).setCellValue(headers[i]); } if (userList != null && userList.size() > 0) { for (User user : userList) { row = sheet.createRow(rowIndex++); for (int i = 0; i < headers.length; i++) { String field = headers[i]; field = field.substring(0, 1).toUpperCase() + field.substring(1); Object val = user.getClass().getMethod("get" + field).invoke(user); row.createCell(i).setCellValue(val.toString()); } } } } }
ResponseUtil
public class ResponseUtil { public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception { response.setHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
15、demo: 利用 poi 技术实现使用模版批量导出数据 <--返回目录
demo结构:
userExporTemplate.xls
PoiController
@Controller public class PoiController { @RequestMapping("/poi/export2") public void get2(HttpServletResponse response) throws Exception { List<User> userList = new ArrayList<>(); User user1 = new User(1, "张三", "13512345678", "123@163.com"); User user2 = new User(1, "张三", "13512345678", "123@163.com"); User user3 = new User(1, "张三", "13512345678", "123@163.com"); userList.add(user1); userList.add(user2); userList.add(user3); String headers[] = { "id", "name", "mobileNum", "email" }; Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userList, headers, "userExporTemplate.xls"); ResponseUtil.export(response, wb, "利用模版导出excel.xls"); } }
ExcelUtil
public class ExcelUtil { public static Workbook fillExcelDataWithTemplate(List<User> userList, String[] headers, String templateFileName) throws Exception { InputStream in = ExcelUtil.class.getResourceAsStream("/static/template/" + templateFileName); POIFSFileSystem fs = new POIFSFileSystem(in); Workbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); // 获取列数 int cellNums = sheet.getRow(0).getLastCellNum(); int rowIndex = 1; Row row = null; if (userList != null && userList.size() > 0) { for (User user : userList) { row = sheet.createRow(rowIndex++); for (int i = 0; i < cellNums; i++) { String field = headers[i]; field = field.substring(0, 1).toUpperCase() + field.substring(1); Object val = user.getClass().getMethod("get" + field).invoke(user); row.createCell(i).setCellValue(val.toString()); } } } return wb; } }
User类和ResponseUtil类与上一个demo相同。
posted on 2019-08-04 00:06 wenbin_ouyang 阅读(660) 评论(0) 编辑 收藏 举报