使用写入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的Excel
workbook = new XSSFWorkbook();
}
else if("xls".equals(type)) {
// 如需生成xls的Excel,请使用下面的工作簿对象,注意后续输出时文件后缀名也需更改为xls
workbook = 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的Excel
workbook = new XSSFWorkbook(fis);
}
else if(templateName.endsWith("xls")) {
// 如需生成xls的Excel,请使用下面的工作簿对象,注意后续输出时文件后缀名也需更改为xls
workbook = 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 2020
*/
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) {
this.comments = 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();
}
@Override
public 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> {
@Override
public 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 block
e.printStackTrace();
}
}