Poi 导出excel单个sheet的内容
目录
Poi 导出excel单个sheet的内容
代码：
Contrller
Service:
导出引用：ExcelExportUtil
导出类：PoiSingleExcelExporter
测试用postman:
结果：
总结：
学习了带复杂表头的处理，如何导出简单表头单个sheet内容呢？ 这边就少了表头的合并的内容，大致内容是差不多的
直接上代码：
代码：
Contrller
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
@Resource
private ExcelService excelService;
@GetMapping("/exportSingleData")
@ApiOperation(value="导出数据接口")
public void exportSingleData(HttpServletResponse response) {
excelService.exportSingleData(response);
}
}
Service:
@Service
public class ExcelService {
public void exportSingleData(HttpServletResponse response) {
String tableName = "order single " + LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue() + "-"
+ LocalDate.now().getDayOfMonth();
// 获取需要导出的数据
List<Map<String, Object>> dataList = getResultData();
// 需要展示的列
List<String> fieldList = Lists.newArrayList("orderTime","total","except","overTime","successRate");
// 匹配数据
List<List<String>> results = matchFieldData(dataList, fieldList);
// 设置表题
List<String> titleList = Lists.newArrayList("日期","订单总量","异常量","超时量","成功率");
try {
ExcelExportUtil. exportSingleExcel(tableName, titleList, results,response);
} catch (Exception e) {
e.printStackTrace();
}
}
private List<Map<String, Object>> getResultData(){
String data = "[{\"orderTime\":\"2021-03-03 10:10:10\",\"total\":55,\"except\":12,\"overTime\":8,\"timelyRate\":\"88%\",\"successRate\":\"77%\"},{\"orderTime\":\"2021-03-03 10:15:10\",\"total\":155,\"except\":44,\"overTime\":20,\"timelyRate\":\"78%\",\"successRate\":\"65%\"},{\"orderTime\":\"2021-03-03 10:20:10\",\"total\":85,\"except\":6,\"overTime\":5,\"timelyRate\":\"98%\",\"successRate\":\"97%\"}]";
return GsonUtils.changeJsonToList(data);
}
private List<List<String>> matchFieldData(List<Map<String, Object>> dataList, List<String> fieldList) {
return ListUtils
.emptyIfNull(dataList).stream().filter(Objects::nonNull).map(e -> ListUtils.emptyIfNull(fieldList)
.stream().map(f -> MapUtils.getString(e, f)).collect(Collectors.toList()))
.collect(Collectors.toList());
}
}
GsonUtils 引用 看字符串转化为list
导出引用：ExcelExportUtil
@Component
public class ExcelExportUtil {
private static Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtil.class);
/**
* 单页导出
* @param fileName 文件名
* @param titles 标题
* @param result 内容，每个List<String>表示一行数据，List中数据的顺序要与标题一致,
* @param response
*/
public static void exportSingleExcel(String fileName, List<String> titles, List<List<String>> result,
HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
new PoiSingleExcelExporter().export( fileName, titles, result,response);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
}
}
导出类：PoiSingleExcelExporter
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
public class PoiSingleExcelExporter {
PoiSingleExcelExporter() {}
/*单个sheet页*/
public void export( String fileName, List<String> titleList,
List<List<String>> contentList,HttpServletResponse response) {
HSSFSheet sheet;
try (HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {
setResponse(response, fileName);
sheet = workbook.createSheet("Sheet1");
int rowNum = 0; // 行号，要一行一行设置内容
createHeader(workbook, sheet, titleList, rowNum);
rowNum++;
createContent(sheet, contentList, rowNum);
workbook.write(output);
output.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
private void createHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<String> titles, int rowNum) {
setHeaderStyle(workbook);
setSheetContent(sheet, titles, rowNum);
}
private static void setSheetContent(HSSFSheet sheet, List<String> contentList, int rownum) {
HSSFRow row = sheet.createRow(rownum);
AtomicInteger i = new AtomicInteger();
for (String title : ListUtils.emptyIfNull(contentList)) {
if (!StringUtils.isEmpty(title)) {
HSSFCell cell = row.createCell(i.getAndIncrement());
cell.setCellValue(title);
}
}
}
private void createContent(HSSFSheet sheet, List<List<String>> content, int rownum) throws Exception {
for (List<String> lineData : content) {
setSheetContent(sheet,lineData,rownum);
rownum++;
}
}
private void setResponse(HttpServletResponse response, String fileName) {
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", getResponseHeader(fileName));
}
private String getResponseHeader(String fileName) {
return "attachment; filename=" + gbToUtf8(fileName) + ".xls";
}
private static String gbToUtf8(String src) {
byte[] b = src.getBytes();
char[] c = new char[b.length];
for (int x = 0; x < b.length; x++) {
c[x] = (char) (b[x] & 0x00FF);
}
return new String(c);
}
private static void setHeaderStyle(HSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();//设置样式
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 13);//设置字体大小
font.setBold(true);//字体加粗
style.setFont(font);//设置的字体
style.setBorderTop(BorderStyle.DASHED);//上边框
style.setBorderBottom(BorderStyle.DASHED); //下边框
style.setBorderBottom(BorderStyle.DASHED);//左边框
style.setRightBorderColor(IndexedColors.BLACK.getIndex());//右边框颜色
style.setTopBorderColor(IndexedColors.BLACK.getIndex());//上边框颜色
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //下边框颜色
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); //左边框颜色
style.setBorderBottom(BorderStyle.DASHED);//右边框
style.setAlignment(HorizontalAlignment.LEFT);//设置水平对齐的样式为居中对齐
style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐
}
}
测试用postman:
结果：
总结：
导出内容，还是一行一行处理，先处理表题的内容，然后处理内容。多个sheet的时候怎么处理呢？