Excel导出多sheet单sheet通用型（poi）

导出背景：

1、Activity 活动 → Coupon 优惠券    1： N

2、List<Activity>、List<ActivityDto> List<Coupon> List<CouponDto>

3、一个活动作为一个sheet导出，导出内容是相对应活动的优惠券

serverImpl：

public Object list(String name, Byte status, Byte getType, HttpServletResponse response) throws Exception {

XSSFWorkbook workbook = new XSSFWorkbook();//创建workbook

//在jpa中的快捷操作

ExampleMatcher matcher = ExampleMatcher.matching(); // 构建对象
Activity activitySearch = new Activity();
if (!StringUtils.isEmpty(name)) {
matcher = matcher.withMatcher("activityName", GenericPropertyMatchers.contains()); // 姓名采用“开始匹配”的方式查询
activitySearch.setActivityName(name);
}
if (status != null) {
activitySearch.setStatus(status);
}
if (getType != null) {
activitySearch.setGetType(getType);
}
// 创建实例

Example<Activity> ex = Example.of(activitySearch, matcher);

//在mybatis中查询数据库时传入参数即可

List<Activity> activitys = activityRepository.findAll(ex);

//利用jpa 实体与dto的转换

List<ActivityDto> dtos = ActivityMapper.INSTANCE.activityToActivityDto(activitys);

String sheetName=“”;//sheet名字

int sheetNum=0;//sheet个数

//文件头

String[] rowsName = new String[] { "优惠码", "会员名", "订单编号", "使用状态", "领取日期", "使用日期" };  

List<Object[]> dataList = null;//存放优惠券的集合

List<List<Object[]>>dateAll=new ArrayList<List<Object[]>>(); //存放活动的集合

Object[] objs = null;

for (int i = 0; i < dtos.size(); i++) {//巡查到数据库的ActivityDto集合 

     ActivityDto dto = dtos.get(i);

     Coupon cou = new Coupon();  

     dataList=new ArrayList<Object[]>();//一个活动中的优惠券查询要另起一个不然会重复  

     List<Coupon> coupons = couponRepository.findByActivityIdAll(dto.getId());

     for (int j = 0; j < coupons.size(); j++) {

          cou=coupons.get(j);

          objs = new Object[rowsName.length];

          objs[0] = cou.getCouponCode();

          objs[1] = cou.getMemberName();

          objs[2] = cou.getOrderId();  

          if(cou.getStatus().equals("1")){ //数字转换（在mybatis中直接用case...when...查询即可，只有两中判断时可以用 cou.getStatus().equals("1”) ？”有效" ：”无效" ）

          objs[3] ="有效";

          }else if(cou.getStatus().equals("2")){

               objs[3] ="使用中";

          }else if(cou.getStatus().equals("3")){

               objs[3] ="无效";

          }else {

               objs[3] =" ";

}

objs[4] = cou.getReceiveDate();

          objs[5] = cou.getUseDate();

          dataList.add(objs);

}

dateAll.add(dataList); //多个sheet集合  

         sheetName = dto.getActivityName();  

          sheetName+=sheetName+",”; //如果有多个sheet，sheet名字进行之间用“，”隔开  

          sheetNum=i;//sheet的个数等于活动的个数

}

sheetName.substring(0, sheetName.lastIndexOf(","));

     ExcelUtil<ActivityDto> excel = new ExcelUtil<>();

     excel.exportExcel(workbook, sheetNum, sheetName, rowsName, dateAll, response);  

     return null;

}

 

Excel：

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.common.tools.string.StringUtil;

@SuppressWarnings("all")
public class ExcelUtil<T> {
public ArrayList<ArrayList<String>> readExcel(String fileName, String path) {
ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();

try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(path + File.separator + fileName);
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(path + File.separator + fileName));
}

for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}

// 循环列Cell
ArrayList<String> arrCell = new ArrayList<String>();
for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
arrCell.add(null);
continue;
}
arrCell.add(getValue(cell));}Row.add(arrCell);}}} catch (IOException e) {System.out.println("e:" + e);}return Row;}public ArrayList<ArrayList<String>> readExcel(InputStream fis) {ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();try {Workbook workBook = null;try {workBook = new XSSFWorkbook(fis);} catch (Exception ex) {workBook = new HSSFWorkbook(fis);}for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {Sheet sheet = workBook.getSheetAt(numSheet);if (sheet == null) {continue;}// 循环行Rowfor (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {Row row = sheet.getRow(rowNum);if (row == null) {continue;}// 循环列CellArrayList<String> arrCell = new ArrayList<String>();for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {Cell cell = row.getCell(cellNum);if (cell == null) {arrCell.add(null);continue;}arrCell.add(getValue(cell));}Row.add(arrCell);}}} catch (IOException e) {System.out.println("e:" + e);}return Row;}public static String getValue(Cell cell) {DecimalFormat df = new DecimalFormat("0");if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {return String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {return df.format(cell.getNumericCellValue());} else if (cell.getCellType() == cell.CELL_TYPE_ERROR) {return String.valueOf(cell.getErrorCellValue());} else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {return String.valueOf(cell.getCellFormula());} else {return String.valueOf(cell.getStringCellValue());}}/** * 导出集合数据到Excel，单Sheet */public ByteArrayOutputStream exportToExcel(String sheetName, List<String[]> head, List<T> data) throws Exception {ByteArrayOutputStream output = new ByteArrayOutputStream();XSSFWorkbook wb = new XSSFWorkbook();XSSFSheet st = wb.createSheet(sheetName);if (data.size() <= 0) {wb.write(output);return output;}// 标题XSSFRow header = st.createRow(0);for (int j = 0; j < head.size(); j++) {String name = head.get(j)[0];header.createCell(j).setCellValue(name);}// 填充数据int rowIndex = 1;for (T d : data) {XSSFRow row = st.createRow(rowIndex);for (int j = 0; j < head.size(); j++) {String key = head.get(j)[1];Object value = null;String className = d.getClass().getName();if ("java.util.HashMap".equals(className)) {value = ((Map<Object, Object>) d).get(key);}if (!"java.util.HashMap".equals(className)) {Method m = d.getClass().getMethod("get" + key);value = m.invoke(d);}row.createCell(j).setCellValue(StringUtil.isNullOrEmpty(value) ? "" : value.toString());}rowIndex++;}wb.write(output);return output;}/** * 导出集合到excel，双sheet */

public void exportExcel(XSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers,List<List<Object[]>> dataList, HttpServletResponse response) throws Exception {  

String tital = sheetTitle.substring(0, sheetTitle.lastIndexOf(","));//去掉sheet名字最后的“,”

String[] name = tital.split(","); //把名字放在集合中方便后面取

for (int k = 0; k < name.length; k++) {

     int sheetN = k;

     // 第一步，创建一个webbook

  XSSFSheet sheet = workbook.createSheet();

     workbook.setSheetName(sheetN, name[k]);//相应的sheet相对用的名字

     // 设置列宽度大小

     sheet.setDefaultColumnWidth((short) 20);

     // 第二步

     // 产生表格标题行

     XSSFRow row = sheet.createRow(0);

     for (int i = 0; i < headers.length; i++) {

          XSSFCell cell = row.createCell((short) i);

          XSSFRichTextString text = new XSSFRichTextString(headers[i]);

          cell.setCellValue(text.toString());

     }

     // 第三步：遍历集合数据，产生数据行，开始插入数据  

     if (dataList != null) { //活动的集合  

          int index = 1;//起始行数   

          List<Object[]> dateAll = dataList.get(k); //活动下优惠券的集合

          for (int j = 0; j < dateAll.size(); j++) {  

               row = sheet.createRow(index); //定义行  

               int cellIndex = 0; //定义列  

               for (Object str : dateAll.get(j)) { //取出优惠券的集合中的具体实体对应的参数

                    if (str == null) {

                         str = "";

}

XSSFCell cell = row.createCell((int) cellIndex);

              cell.setCellValue(str.toString());//将参数写入到列中

              cellIndex++;

}

               index++;

}

}
}
if (workbook != null) {
try {
String fileName;
fileName = "活动优惠券" + ".xls";
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
OutputStream out = response.getOutputStream();

workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
}

}

导出结果：

 

 

转载于:https://www.cnblogs.com/lsz1349yw/p/7479040.html