Java poiを使用したExcelソースの完全な例のエクスポート
28231 ワード
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
/**
*
*
* @param itemsetId
*/
@GetMapping("/export.json")
@OvsExceptionHandler
public void export(@RequestParam("itemsetId") Long itemsetId, HttpServletResponse response) throws Exception {
List<ItemsetItemDTO> list = new ArrayList<>();
ItemsetItemQuery q = new ItemsetItemQuery();
q.setItemsetId(itemsetId);
q.setCurrentPage(1);
q.setPageSize(500);
PageResult<ItemsetItemDTO> pageResult = itemsetService.getItemList(q);
while (!CollectionUtils.isEmpty(pageResult.getList())) {
// 10w
if (list.size() > 100000) {
break;
}
list.addAll(pageResult.getList());
// next page
q.setCurrentPage(q.getCurrentPage() + 1);
pageResult = itemsetService.getItemList(q);
}
OutputStream outputStream = null;
try {
HSSFWorkbook workbook = exportExcel(list);
outputStream = response.getOutputStream();
response.reset();
response.setContentType("application/msexcel");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
// Content-disposition
String filename = " _" + System.currentTimeMillis();
filename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xls");
workbook.write(outputStream);
} catch (Exception e) {
log.error("", e);
} finally {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
// ignore
}
}
}
public HSSFWorkbook exportExcel(List<ItemsetItemDTO> list) {
HSSFWorkbook workbook = new HSSFWorkbook();
try {
HSSFSheet sheet = workbook.createSheet(" ");
Row row = sheet.createRow(0);
// excle
String[] header = new String[]{" ID", " ", " ", " ID", " ", " "};
for (int i = 0; i < header.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(header[i]);
sheet.setColumnWidth(i, header[i].getBytes().length * 350);
}
//excel
for (int s = 0; s < list.size(); s++) {
ItemsetItemDTO info = list.get(s);
row = sheet.createRow(s + 1);
row.createCell(0).setCellValue(info.getItemId());
row.createCell(1).setCellValue(info.getItemName());
row.createCell(2).setCellValue(info.getPcateLeafName());
row.createCell(3).setCellValue(info.getSrcSellerId());
row.createCell(4).setCellValue(info.getBizType());
row.createCell(5).setCellValue(info.getTwMinPrice().toString());
}
} catch (Exception e) {
log.error("exportExcel:", e);
}
return workbook;
}
ここでpom.xml依存は次のとおりです.
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-scratchpadartifactId>
<version>3.15version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>3.15version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poiartifactId>
<version>3.15version>
dependency>