導出背景： 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

總結

以上是生活随笔為你收集整理的Excel导出多sheet单sheet通用型（poi）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。