使用写入excel工具类的例子
说明:改工具类是网上公开的，来源现在找不着了，我只是在原基础上修改了下，抽离了泛型。如有侵权，请通知博主
工具类代码:
package com.system.util;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Iterator;import java.util.List;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.HorizontalAlignment;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.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** * Author: laijieguan*/public abstract class ExcelWriter<T> {public List<String> CELL_HEADS = new ArrayList<String>(); // 列头//static {//// 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头//CELL_HEADS = new ArrayList<>();//CELL_HEADS.add("姓名");//CELL_HEADS.add("年龄");//CELL_HEADS.add("居住城市");//CELL_HEADS.add("职业");//}/*** 生成Excel并写入数据信息,无模板的读取方式* * @param dataList 数据列表* @return 写入数据后的工作簿对象* @throws IOException */public Workbook exportData(List<T> dataList,String type) {Workbook workbook =null;if("xlsx".equals(type)) {// 生成xlsx的Excelworkbook = new XSSFWorkbook();}else if("xls".equals(type)) {// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xlsworkbook = new HSSFWorkbook();}Sheet sheet = null;//判断是否需要生成第一行的数据头信息if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头sheet = buildDataSheet(workbook);}else {//不要生成sheet = workbook.getSheetAt(0);}// 构建每行的数据内容int rowNum = 1;for (Iterator<T> it = dataList.iterator(); it.hasNext();) {T data = it.next();if (data == null) {continue;}// 输出行数据Row row = sheet.createRow(rowNum++);convertDataToRow(data, row);}return workbook;}/*** 生成Excel并写入数据信息,有模板的读取方式* * @param dataList 数据列表* @return 写入数据后的工作簿对象* @throws IOException */public Workbook exportDataTem(List<T> dataList,String templateName) throws Exception {Workbook workbook =null;FileInputStream fis = new FileInputStream(templateName);if(templateName.endsWith("xlsx")) {// 生成xlsx的Excelworkbook = new XSSFWorkbook(fis);}else if(templateName.endsWith("xls")) {// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xlsworkbook = new HSSFWorkbook(fis);}Sheet sheet = null;//判断是否需要生成第一行的数据头信息if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头sheet = buildDataSheet(workbook);}else {//不要生成sheet = workbook.getSheetAt(0);}// 构建每行的数据内容int rowNum = 1;for (Iterator<T> it = dataList.iterator(); it.hasNext();) {T data = it.next();if (data == null) {continue;}// 输出行数据Row row = sheet.createRow(rowNum++);convertDataToRow(data, row);}return workbook;}/*** 生成sheet表，并写入第一行数据（列头）* * @param workbook 工作簿对象* @return 已经写入列头的Sheet*/public Sheet buildDataSheet(Workbook workbook) {Sheet sheet = workbook.createSheet();// 设置列头宽度for (int i = 0; i < CELL_HEADS.size(); i++) {sheet.setColumnWidth(i, 4000);}// 设置默认行高sheet.setDefaultRowHeight((short) 400);// 构建头单元格样式//CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());// 写入第一行各列的数据Row head = sheet.createRow(0);for (int i = 0; i < CELL_HEADS.size(); i++) {Cell cell = head.createCell(i);cell.setCellValue(CELL_HEADS.get(i));//cell.setCellStyle(cellStyle);}return sheet;}/*** 设置第一行列头的样式* * @param workbook 工作簿对象* @return 单元格样式对象*//** private static CellStyle buildHeadCellStyle(Workbook workbook) { CellStyle* style = workbook.createCellStyle(); // 对齐方式设置* style.setAlignment(HorizontalAlignment.CENTER); // 边框颜色和宽度设置* style.setBorderBottom(BorderStyle.THIN);* style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框* style.setBorderLeft(BorderStyle.THIN);* style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框* style.setBorderRight(BorderStyle.THIN);* style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框* style.setBorderTop(BorderStyle.THIN);* style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框 // 设置背景颜色* style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());* style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 粗体字设置 Font font =* workbook.createFont(); font.setBold(true); style.setFont(font); return style;* }*//*** 将数据转换成行 抽离泛型* * @param data 源数据* @param row 行对象* @return*/public abstract void convertDataToRow(T data, Row row);}
现在有一个实体类是这样的
public class Country {/*** 国别代码*/private String countrycode;/*** 国别中文简称*/private String countryname;/*** 国别英文简称*/private String countryenname;/*** 国别英文缩写*/private String countryenshort;/*** 优普税率*/private String applytaxtype;/*** 国别描述*/private String countrydesc;/*** 备注*/private String comments;/*** 状态*/private String states;/*** 最终目的国别代码*/private String lastcountrycode;public String getCountrycode() {return countrycode;}public void setCountrycode(String countrycode) {this.countrycode = countrycode == null ? null : countrycode.trim();}public String getCountryname() {return countryname;}public void setCountryname(String countryname) {this.countryname = countryname == null ? null : countryname.trim();}public String getCountryenname() {return countryenname;}public void setCountryenname(String countryenname) {this.countryenname = countryenname == null ? null : countryenname.trim();}public String getCountryenshort() {return countryenshort;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column WL_COUNTRY.COUNTRYENSHORT** @param countryenshort the value for WL_COUNTRY.COUNTRYENSHORT** @mbg.generated Fri Jul 10 13:39:12 CST */public void setCountryenshort(String countryenshort) {this.countryenshort = countryenshort == null ? null : countryenshort.trim();}public String getApplytaxtype() {return applytaxtype;}public void setApplytaxtype(String applytaxtype) {this.applytaxtype = applytaxtype == null ? null : applytaxtype.trim();}public String getCountrydesc() {return countrydesc;}public void setCountrydesc(String countrydesc) {this.countrydesc = countrydesc == null ? null : countrydesc.trim();}public String getComments() {return comments;}public void setComments(String comments) {ments = comments == null ? null : comments.trim();}public String getStates() {return states;}public void setStates(String states) {this.states = states == null ? null : states.trim();}public String getLastcountrycode() {return lastcountrycode;}public void setLastcountrycode(String lastcountrycode) {this.lastcountrycode = lastcountrycode == null ? null : lastcountrycode.trim();}@Overridepublic String toString() {return "Country [countrycode=" + countrycode + ", countryname=" + countryname + ", countryenname="+ countryenname + ", countryenshort=" + countryenshort + ", applytaxtype=" + applytaxtype+ ", countrydesc=" + countrydesc + ", comments=" + comments + ", states=" + states+ ", lastcountrycode=" + lastcountrycode + "]";}}
继承工具类
import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import com.system.util.ExcelWriter;import com.wl.differentCountries.entity.Country;public class CountryExcelWriter extends ExcelWriter<Country> {@Overridepublic void convertDataToRow(Country data, Row row) {int cellNum = 0;Cell cell;// 国别代码cell = row.createCell(cellNum++);cell.setCellValue(null == data.getCountrycode() ? "" : data.getCountrycode());// 国别中文简称cell = row.createCell(cellNum++);cell.setCellValue(null == data.getCountryname() ? "" : data.getCountryname());// 国别英文简称cell = row.createCell(cellNum++);cell.setCellValue(null == data.getCountryenname() ? "" : data.getCountryenname());// 国别英文缩写cell = row.createCell(cellNum++);cell.setCellValue(null == data.getCountryenshort() ? "" : data.getCountryenshort());// 优普税率cell = row.createCell(cellNum++);cell.setCellValue(null == data.getApplytaxtype() ? "" : data.getApplytaxtype());// 国别描述cell = row.createCell(cellNum++);cell.setCellValue(null == data.getCountrydesc() ? "" : data.getCountrydesc());// 备注cell = row.createCell(cellNum++);cell.setCellValue(null == data.getComments() ? "" : data.getComments());// 状态cell = row.createCell(cellNum++);cell.setCellValue(null == data.getStates() ? "" : data.getStates());// 最终目的国别代码cell = row.createCell(cellNum++);cell.setCellValue(null == data.getLastcountrycode() ? "" : data.getLastcountrycode());}}
实际使用场景
public void exportCountryData(Map<String, Object> queryMap, OutputStream os) {try {//new一个工具类，喜欢做成静态的也行，你们自己修改修改CountryExcelWriter countryExcelWriter = new CountryExcelWriter();//查询出符合业务的数据List<Country> dataList = wlCountryMapper.getCountrys(queryMap);PathUtils pathUtils = new PathUtils();//获取模板的全路径String tempalte = pathUtils.getWEBINFPath() + "resource" + File.separator+ "countryList.xlsx";//把查询出来的数据传入，把模板全路径传入Workbook wb = countryExcelWriter.exportDataTem(dataList, tempalte);//写到对应的输出流，一般来说都是response的流，用于下载。看各自的业务了wb.write(os);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}
