JAVA导出数据到EXCEL表格（简单）
本示例是快速将查询的结果导出到excel 中，如果用我的代码只需要修改查询数据的sql、excel文件名、excel的sheet标题、内容的标题即可。
POI简介：Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。
官方主页http://poi.apache.org/index.html，
API文档http://poi.apache.org/apidocs/index.html
使用方式：
创建数据库（这个操作就不贴代码了）
导入工程下db目录的数据库
创建类model（实际开发中换成自己的类），加上注解@ExcelField
public class Model { /** * 用户昵称 */ @ExcelField(title = "用户昵称") private String nick; /** * 用户头像url */ @ExcelField(title = "用户头像url") private String headUrl; /** * 用户账号 */ @ExcelField(title = "用户账号") private String userAcc; /** * 用户手机号 */ @ExcelField(title = "用户手机号") private String mobile; /** * 名字 */ @ExcelField(title = "名字") private String cardName; /** * '状态，0待批，1不同意，2同意', */ @ExcelField(title = "状态") private String state; /** * 说明 */ @ExcelField(title = "说明") private String describe; get/set...... }
编写测试类
@SpringBootTest @RunWith(SpringRunner.class) public class ExportTest { @Autowired private ApiMapper apiMapper; /** * 导出测试 * @throws Exception */ @Test public void testExportLog() throws Exception { final String fileName = "提现审核统计表.xls"; List<Model> list = apiMapper.findAll(); ExcelUtil.writeExcel(response, fileName, list , Model.class); } }
最后附上Excel注解的代码
import java.lang.annotation.Inherited; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; @Inherited @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { String title(); }
ExcelUtil工具类
// // Source code recreated from a .class file by IntelliJ IDEA // (powered by Fernflower decompiler) // package cn.jss.common.util; import cn.jss.common.YCode; import cn.jss.common.YException; import java.beans.PropertyDescriptor; import java.io.IOException; import java.io.InputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataValidation; 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.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; public class ExcelUtil { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class); private ExcelUtil() { } public static <Q> void writeExcel(HttpServletResponse response, String fileName, List<Q> list, Class<Q> clazz) throws IOException, IllegalArgumentException, IllegalAccessException { HSSFWorkbook wb = new HSSFWorkbook(); Field[] fields = getAllFields(clazz); ArrayList<String> headList = new ArrayList(); Field[] var7 = fields; int var8 = fields.length; int i; for(i = 0; i < var8; ++i) { Field f = var7[i]; ExcelField field = (ExcelField)f.getAnnotation(ExcelField.class); if (field != null) { headList.add(field.title()); } } Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); for(i = 0; i < headList.size(); ++i) { Cell headCell = row.createCell(i); headCell.setCellValue(String.valueOf(headList.get(i))); } if (null != list && !list.isEmpty()) { for(i = 0; i < list.size(); ++i) { Row rowData = sheet.createRow(i + 1); Q q = list.get(i); Field[] ff = getAllFields(q.getClass()); int j = 0; Field[] var14 = ff; int var15 = ff.length; for(int var16 = 0; var16 < var15; ++var16) { Field f = var14[var16]; ExcelField field = (ExcelField)f.getAnnotation(ExcelField.class); if (field != null) { f.setAccessible(true); Object obj = f.get(q); Cell cell = rowData.createCell(j); cell.setCellType(1); cell.setCellValue(String.valueOf(obj)); ++j; } } } } response.setHeader("Content-Disposition", "attachment;filename=" + urlEncode(fileName)); response.setContentType("application/ms-excel"); ServletOutputStream ouPutStream = null; try { ouPutStream = response.getOutputStream(); wb.write(ouPutStream); } finally { if (ouPutStream != null) { ouPutStream.close(); } } } public static CellStyle getCellStyle(Workbook wb) { CellStyle style = wb.createCellStyle(); style.setAlignment((short)1); style.setAlignment((short)2); style.setVerticalAlignment((short)1); style.setWrapText(true); return style; } public static Field[] getAllFields(Class clazz) { ArrayList fieldList; for(fieldList = new ArrayList(); clazz != null; clazz = clazz.getSuperclass()) { fieldList.addAll(new ArrayList(Arrays.asList(clazz.getDeclaredFields()))); } Field[] fields = new Field[fieldList.size()]; fieldList.toArray(fields); return fields; } public static final String urlEncode(String s) throws UnsupportedEncodingException { return URLEncoder.encode(s, "UTF-8"); } public static HSSFWorkbook createExcelTemplate(String[] handers, List<String[]> downData, String[] downRows, String[] sendOrderList, String[] errorLogList, String[] expressFailList) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("批量发货"); HSSFSheet sheet2 = wb.createSheet("Sheet2"); sheet1.setDefaultColumnWidth(20); HSSFSheet translation = wb.createSheet("表格说明"); translation.addMergedRegion(new CellRangeAddress(2, 19, 1, 10)); HSSFRow sheet3 = translation.createRow(2); HSSFCell info = sheet3.createCell(1, 1); HSSFCellStyle style = wb.createCellStyle(); style.setWrapText(true); style.setVerticalAlignment((short)1); info.setCellValue("请严格按照表格说明的规范填写，填写不合法均会导入失败；\r\n 1、表格已预置待发货的订货号，请勿篡改；\r\n 2、物流公司名称，请按照提供的标准填写，必填，否则导入失败；\r\n 3、物流单号，请按照实际物流公司单号填写，必填，1-20字符以内"); info.setCellStyle(style); HSSFCell dealerOrderCell = null; int i = 0; int i; for(i = sendOrderList.length; i < i; ++i) { HSSFRow row = sheet1.createRow(i + 1); dealerOrderCell = row.createCell(0, 1); dealerOrderCell.setCellValue(sendOrderList[i]); } HSSFRow row; HSSFCell logCell; int index; if (expressFailList != null && expressFailList.length > 0) { logCell = null; i = 0; for(index = errorLogList.length; i < index; ++i) { row = sheet1.createRow(i + 1); logCell = row.createCell(1, 1); logCell.setCellValue(expressFailList[i]); } } if (errorLogList != null && errorLogList.length > 0) { logCell = null; i = 0; for(index = errorLogList.length; i < index; ++i) { row = sheet1.createRow(i + 1); logCell = row.createCell(3, 1); logCell.setCellValue(errorLogList[i]); } } HSSFRow rowFirst = sheet1.createRow(0); for(i = 0; i < handers.length; ++i) { HSSFCell cell = rowFirst.createCell(i); sheet1.setColumnWidth(i, 5000); cell.setCellStyle(style); cell.setCellValue(handers[i]); } wb.setSheetHidden(1, true); String[] arr = new String[]{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}; index = 0; row = null; for(int r = 0; r < downRows.length; ++r) { String[] dlData = (String[])downData.get(r); int rownum = Integer.parseInt(downRows[r]); if (dlData.length < 5) { sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 500, rownum, rownum)); } else { String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$500"; sheet2.setColumnWidth(r, 4000); sheet1.addValidationData(SetDataValidation(strFormula, 1, 500, rownum, rownum)); for(int j = 0; j < dlData.length; ++j) { if (index == 0) { row = sheet2.createRow(j); sheet2.setColumnWidth(j, 4000); row.createCell(0).setCellValue(dlData[j]); } else { int rowCount = sheet2.getLastRowNum(); if (j <= rowCount) { sheet2.getRow(j).createCell(index).setCellValue(dlData[j]); } else { sheet2.setColumnWidth(j, 4000); sheet2.createRow(j).createCell(index).setCellValue(dlData[j]); } } } ++index; } } return wb; } private static HSSFDataValidation SetDataValidation(String strFormula, int firstRow, int endRow, int firstCol, int endCol) { CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula); HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); dataValidation.createErrorBox("Error", "Error"); dataValidation.createPromptBox("", (String)null); return dataValidation; } private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) { DataValidationHelper helper = sheet.getDataValidationHelper(); DataValidationConstraint constraint = helper.createExplicitListConstraint(textList); constraint.setExplicitListValues(textList); CellRangeAddressList regions = new CellRangeAddressList((short)firstRow, (short)endRow, (short)firstCol, (short)endCol); DataValidation data_validation = helper.createValidation(constraint, regions); return data_validation; } public static List excelForList(MultipartFile file, Class<?> clazz, Boolean titleExist, String[] tableHeads, Integer maxLimit) throws YException { ArrayList list = new ArrayList(); try { Object wb = null; try { InputStream input = file.getInputStream(); wb = new HSSFWorkbook(input); } catch (Exception var23) { InputStream input = file.getInputStream(); wb = new XSSFWorkbook(input); } Sheet sheet = ((Workbook)wb).getSheetAt(0); Row row; int i; if (titleExist) { i = 1; if (null != tableHeads && tableHeads.length > 0) { row = sheet.getRow(0); int num = row.getPhysicalNumberOfCells(); if (num != tableHeads.length) { throw new YException(YCode.C_1.code(), "请使用系统模板导入"); } for(int k = 0; k < tableHeads.length; ++k) { Cell cell = row.getCell(k); if (null == cell || null == cell.getStringCellValue()) { throw new YException(YCode.C_1.code(), "请使用系统模板导入"); } if (!tableHeads[k].equals(cell.getStringCellValue())) { throw new YException(YCode.C_1.code(), "请使用系统模板导入"); } } } } else { i = 0; } if (sheet.getLastRowNum() > maxLimit) { throw new YException(YCode.C_1.code(), "单次导入不多于" + maxLimit + "个数据"); } else { while(i <= sheet.getLastRowNum()) { row = sheet.getRow(i); Object object = clazz.newInstance(); Field[] fields = clazz.getDeclaredFields(); int j = 0; Field[] var13 = fields; int var14 = fields.length; for(int var15 = 0; var15 < var14; ++var15) { Field field = var13[var15]; String fieldName = field.getName(); PropertyDescriptor pd = new PropertyDescriptor(fieldName, clazz); Method getMethod = pd.getWriteMethod(); Cell cell = row.getCell(j++); int type = cell.getCellType(); if (type == 4) { boolean value = cell.getBooleanCellValue(); getMethod.invoke(object, String.valueOf(value)); } else if (type == 0) { Double d = cell.getNumericCellValue(); getMethod.invoke(object, NumberToTextConverter.toText(d)); } else if (type == 1) { String value = cell.getStringCellValue(); getMethod.invoke(object, new String(value)); } } list.add(object); ++i; } return list; } } catch (YException var24) { throw new YException(var24.getStatus(), var24.getMessage()); } catch (Exception var25) { LOGGER.error("excelForList e=>", var25); throw new YException(YCode.C_400.code(), "解析Excel数据异常"); } } }
