Excel导入导出
POI方式
一、POI概述
Jakarta POI 是一套用于访问微软格式文档的Java API。POI提供API给Java程序对Microsoft Office格式档案读和写的功能。在许多企业办公系统中，经常会有用户要求，需要对数据进行统计并且可以直接下载Excel文件。
结构：
HSSF － 提供读写Microsoft Excel格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
HWPF － 提供读写Microsoft Word格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读写Microsoft Visio格式档案的功能
二、官方下载
https://poi.apache.org/download.html
三、POI HSSF API组件:
HSSF（用于操作Excel的组件，适用于excel2003）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中，主要部分包括Excel对象，样式和格式，有以下几种常用的对象：
常用组件：
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的单元格
HSSFFont excel字体
样式：
HSSFCellStyle cell样式
四、基本操作步骤：
首先，我们应该要知道的是，一个Excel文件对应一个workbook，一个workbook中有多个sheet组成，一个sheet是由多个行(row)和列(cell)组成。那么我们用poi要导出一个Excel表格的正确顺序应该是：
1、用HSSFWorkbook打开或者创建“Excel文件对象”；
HSSFWorkbook wb = new HSSFWorkbook();
2、用HSSFWorkbook对象返回或者创建Sheet对象；
HSSFSheet sheet = wb.createSheet("地区信息");
3、用Sheet对象创建行对象；
HSSFRow row = sheet.createRow(0); // 第1行
4、用行对象创建Cell对象；
HSSFCell cell = row.createCell(0); // 第1个单元格
5、对Cell对象读写。
cell.setCellStyle(cellStyle); // 设置创建好的单元格样式（HSSFCellStyle类型） cell.setCellValue("地区信息列表");
6、将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面
String fileName = "areaInfos.xls"; response.setContentType("application/vnd.ms-excel"); response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); // 获取响应字节输出流 OutputStream out = response.getOutputStream(); wb.write(out); // 将生成的HSSFWorkbook写入HttpServletResponse响应中 out.close(); // 关闭响应字节输出流
五、应用案例：导出地区信息表
1、在JSP页面中，查询区域部分增加一个“导出”按钮，并指定请求的路径和方法。
<input name="query" type="submit" formaction="UserServlet?method=areaExport" class="button" value="导出" />
2、在Servlet中编写areaExport方法
public String areaExport(HttpServletRequest request, HttpServletResponse response) throws Exception { String fileName = "areaInfos.xls"; // 指定导出Excel文件名 response.setContentType("application/vnd.ms-excel"); response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); Area area = (Area) request.getSession().getAttribute("area"); List areaList = userService.findArea(null, area); // 调用查询方法，但是不传入分页信息// 获取响应字节输出流 OutputStream out = response.getOutputStream(); HSSFWorkbook workbook = userService.exportExcel(areaList); workbook.write(out); // 将生成的HSSFWorkbook写入HttpServletResponse响应中 out.close(); // 关闭响应字节输出流 return null; }
3、编写Service层的exportExcel方法
接口UserService.java：
import java.sql.SQLException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.sdbi.pojo.Area; import com.sdbi.pojo.Page; import com.sdbi.pojo.User; public interface UserService { List findArea(Page page, Area area) throws SQLException; // 根据检索条件，分页查询地区信息 HSSFWorkbook exportExcel(List list); }
实现类UserServiceImpl.java：
import java.sql.SQLException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.sdbi.dao.UserDao; import com.sdbi.dao.impl.UserDaoImpl; import com.sdbi.pojo.Area; import com.sdbi.pojo.Page; import com.sdbi.pojo.User; import com.sdbi.service.UserService; import com.sdbi.utils.ExcelUtil; public class UserServiceImpl implements UserService { private UserDao userDao = new UserDaoImpl(); ...... @Override public List findArea(Page page, Area area) throws SQLException { UserDao userDao = new UserDaoImpl(); return userDao.findArea(page, area); } @Override public HSSFWorkbook exportExcel(List list) { return ExcelUtil.exportExcel(list); } }
4、编写ExcelUtil类
定义一个导出Excel的方法，参数是需要导出的数据集合List
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; 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.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import com.sdbi.pojo.Area; public class ExcelUtil { public static HSSFWorkbook exportExcel(List list) { String title = "地区信息列表"; String[] head = { "ID", "父级ID", "名称", "简称", "经度", "纬度", "级别", "排序" }; // 创建⼀个HSSFWorkbook，对应⼀个excel应用文件 // 创建一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 根据Excel创建一个sheet HSSFSheet sheet = wb.createSheet("地区信息"); // 创建字体样式 HSSFFont fontTitle = wb.createFont(); fontTitle.setFontName("微软雅黑"); fontTitle.setBold(true); // 粗体 fontTitle.setColor(IndexedColors.WHITE.getIndex()); // 设置字体颜色，白色 fontTitle.setFontHeightInPoints((short) 16); // 设置字体大小 HSSFFont fontHead = wb.createFont(); fontHead.setFontName("黑体"); fontHead.setBold(true); // 粗体 fontHead.setFontHeightInPoints((short) 14); // 设置字体大小 HSSFFont fontBody = wb.createFont(); fontBody.setFontName("仿宋_GB2312"); fontBody.setFontHeightInPoints((short) 12); // 创建标题样式 HSSFCellStyle cellStyleTitle = wb.createCellStyle(); // 设置边框 cellStyleTitle.setBorderTop(BorderStyle.THIN); // 上边框 cellStyleTitle.setBorderBottom(BorderStyle.THIN); // 下边框 cellStyleTitle.setBorderLeft(BorderStyle.THIN); // 左边框 cellStyleTitle.setBorderRight(BorderStyle.THIN); // 右边框 // 设置居中 cellStyleTitle.setAlignment(HorizontalAlignment.CENTER); // ⽔平居中 cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中 // 设置单元格前景颜色 cellStyleTitle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); // 设置单元格填充样式，使用纯色前景颜色填充 cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置字体 cellStyleTitle.setFont(fontTitle);// 选择需要用到的字体格式 // 设置标题信息 HSSFRow row = sheet.createRow(0); // 第0行：标题 HSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyleTitle); cell.setCellValue(title); CellRangeAddress region = new CellRangeAddress(0, 0, 0, head.length - 1); // 合并第0行的前8个单元格 sheet.addMergedRegion(region); // 创建表头样式 HSSFCellStyle cellStyleHead = wb.createCellStyle(); // 设置边框 cellStyleHead.setBorderTop(BorderStyle.THIN); // 上边框 cellStyleHead.setBorderBottom(BorderStyle.THIN); // 下边框 cellStyleHead.setBorderLeft(BorderStyle.THIN); // 左边框 cellStyleHead.setBorderRight(BorderStyle.THIN); // 右边框 // 设置居中 cellStyleHead.setAlignment(HorizontalAlignment.CENTER); // ⽔平居中 cellStyleHead.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中 // 设置单元格前景颜色，25%灰度颜色 cellStyleHead.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 设置单元格填充样式，使用纯色前景颜色填充 cellStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置字体 cellStyleHead.setFont(fontHead);// 选择需要用到的字体格式 // 设置表头信息 row = sheet.createRow(1); // 第1行：表头 for (int i = 0; i < head.length; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyleHead); cell.setCellValue(head[i]); } // 表中数据的起始行数 int startRegionRowNum = 2; // 创建数据记录样式 HSSFCellStyle cellStyleBody = wb.createCellStyle(); // 设置边框 cellStyleBody.setBorderTop(BorderStyle.THIN); // 上边框 cellStyleBody.setBorderBottom(BorderStyle.THIN); // 下边框 cellStyleBody.setBorderLeft(BorderStyle.THIN); // 左边框 cellStyleBody.setBorderRight(BorderStyle.THIN); // 右边框 // 设置居中 cellStyleBody.setAlignment(HorizontalAlignment.CENTER); // ⽔平居中 cellStyleBody.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中 cellStyleBody.setFont(fontBody);// 选择需要用到的字体格式 // 构建表体数据 for (int i = 0; i < list.size(); i++) { Area area = (Area) list.get(i); // 创建行 row = sheet.createRow(i + startRegionRowNum); // 从第2行开始：数据记录 row.setHeight((short) 400); HSSFCell cell_0 = row.createCell(0); cell_0.setCellStyle(cellStyleBody); cell_0.setCellValue(area.getId()); // ID HSSFCell cell_1 = row.createCell(1); cell_1.setCellStyle(cellStyleBody); cell_1.setCellValue(area.getPid()); // 父级ID HSSFCell cell_2 = row.createCell(2); cell_2.setCellStyle(cellStyleBody); cell_2.setCellValue(area.getName()); // 名称 HSSFCell cell_3 = row.createCell(3); cell_3.setCellStyle(cellStyleBody); cell_3.setCellValue(area.getShortname()); // 简称 HSSFCell cell_4 = row.createCell(4); cell_4.setCellStyle(cellStyleBody); cell_4.setCellValue(area.getLongitude()); // 经度 HSSFCell cell_5 = row.createCell(5); cell_5.setCellStyle(cellStyleBody); cell_5.setCellValue(area.getLongitude()); // 纬度 HSSFCell cell_6 = row.createCell(6); cell_6.setCellStyle(cellStyleBody); cell_6.setCellValue(area.getLevel()); // 级别 HSSFCell cell_7 = row.createCell(7); cell_7.setCellStyle(cellStyleBody); cell_7.setCellValue(area.getSort()); // 排序 } sheet.autoSizeColumn(0); // 设置自适应列宽度 sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); return wb; } }
六、颜色IndexedColors枚举
IndexedColors是org.apache.poi.ss.usermodel包中的一个枚举类，里面预置了48种颜色，可以用于字体颜色设置、单元格颜色设置。
上面的单元格颜色对应下面的英语颜色表示，从X1-X49 按顺序对应；
IndexedColors.AQUA.getIndex() //1 IndexedColors.AUTOMATIC.getIndex() //2 IndexedColors.BLUE.getIndex() //3 IndexedColors.BLUE_GREY.getIndex() //4 IndexedColors.BRIGHT_GREEN.getIndex() //5 IndexedColors.BROWN.getIndex() //6 IndexedColors.CORAL.getIndex() //7 IndexedColors.CORNFLOWER_BLUE.getIndex() //8 IndexedColors.DARK_BLUE.getIndex() //9 IndexedColors.DARK_GREEN.getIndex() //10 IndexedColors.DARK_RED.getIndex() //11 IndexedColors.DARK_TEAL.getIndex() //12 IndexedColors.DARK_YELLOW.getIndex() //13 IndexedColors.GOLD.getIndex() //14 IndexedColors.GREEN.getIndex() //15 IndexedColors.GREY_25_PERCENT.getIndex() //16 IndexedColors.GREY_40_PERCENT.getIndex() //17 IndexedColors.GREY_50_PERCENT.getIndex() //18 IndexedColors.GREY_80_PERCENT.getIndex() //19 IndexedColors.INDIGO.getIndex() //20 IndexedColors.LAVENDER.getIndex() //21 IndexedColors.LEMON_CHIFFON.getIndex() //22 IndexedColors.LIGHT_BLUE.getIndex() //23 IndexedColors.LEMON_CHIFFON.getIndex() //24 IndexedColors.LIGHT_BLUE.getIndex() //25 IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex() //26 IndexedColors.LIGHT_GREEN.getIndex() //27 IndexedColors.LIGHT_ORANGE.getIndex() //28 IndexedColors.LIGHT_TURQUOISE.getIndex() //29 IndexedColors.LIGHT_YELLOW.getIndex() //30 IndexedColors.LIME.getIndex() //31 IndexedColors.MAROON.getIndex() //32 IndexedColors.OLIVE_GREEN.getIndex() //33 IndexedColors.ORANGE.getIndex() //34 IndexedColors.ORCHID.getIndex() //35 IndexedColors.PALE_BLUE.getIndex() //36 IndexedColors.PINK.getIndex() //37 IndexedColors.PLUM.getIndex() //38 IndexedColors.RED.getIndex() //39 IndexedColors.ROSE.getIndex() //40 IndexedColors.ROYAL_BLUE.getIndex() //41 IndexedColors.SEA_GREEN.getIndex() //42 IndexedColors.SKY_BLUE.getIndex() //43 IndexedColors.TAN.getIndex() //44 IndexedColors.TEAL.getIndex() //45 IndexedColors.TURQUOISE.getIndex() //46 IndexedColors.VIOLET.getIndex() //47 IndexedColors.WHITE.getIndex() //48 IndexedColors.YELLOW.getIndex() //49
