Springboot+poiエクスポート指定フォーマットExcelテンプレート詳細+Demo
プロジェクトはspringbootフレームワークを使用しており、非常に簡単で、プロジェクトをインポートするだけで実行できます.まず効果を見てみましょう.
リソースのダウンロード先:
http://download.csdn.net/download/tjcyjd/9998721
具体的な手順は次のとおりです.
1、依存jarパッケージを導入する.
pom.xmlに依存する2つのパッケージを導入すればいいです.
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
2、エクスポートするExcelテンプレートをwebappの下のexcelフォルダに入れます.
次のディレクトリでは、Excelを2つ、2003を1つ、2007を作成します.user_model.xls、user_model.xlsx.
Excelでエクスポートするコンテンツのフォーマットは次のとおりです.
3、エクスポートコード実装.
くだらないことは言わないで、直接コードをつけて、持ってきて使います.
Excel 2003のエクスポート:
@RequestMapping(value = "/export2003.do", method = RequestMethod.GET)
public void export2003(HttpServletRequest request, HttpServletResponse response) {
List list = new ArrayList();
User user1 = new User();
user1.setId(100000000L);
user1.setUsername(" ");
user1.setHead("http://qzapp.qlogo.cn/qzapp/101357640/3C94155CAB4E28517D8435BF404B52F1/100");
user1.setSex(0);
user1.setPhone("18800000000");
User user2 = new User();
user2.setId(100000001L);
user2.setUsername(" ");
user2.setHead("http://q.qlogo.cn/qqapp/1105676675/9DA6D356F4FE1DF0E63BD07334680BF2/100");
user2.setSex(0);
user2.setPhone("18800000001");
list.add(user1);
list.add(user2);
HSSFWorkbook wb = null;
try {
// excel
String basePath = request.getSession().getServletContext().getRealPath("/");
String excel = basePath + "/excel/user_model.xls";
File fi = new File(excel);
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
// excel
wb = new HSSFWorkbook(fs);
// sheet
HSSFSheet sheet = wb.getSheetAt(0);
//
int rowIndex = 1;
for (User user : list) {
HSSFRow row = sheet.getRow(rowIndex);
if (null == row) {
row = sheet.createRow(rowIndex);
}
HSSFCell cell0 = row.getCell(0);
if (null == cell0) {
cell0 = row.createCell(0);
}
cell0.setCellValue(user.getId());//
HSSFCell cell1 = row.getCell(1);
if (null == cell1) {
cell1 = row.createCell(1);
}
cell1.setCellValue(user.getUsername());//
HSSFCell cell2 = row.getCell(2);
if (null == cell2) {
cell2 = row.createCell(2);
}
cell2.setCellValue(user.getHead());//
HSSFCell cell3 = row.getCell(3);
if (null == cell3) {
cell3 = row.createCell(3);
}
cell3.setCellValue(user.getSex() == 0 ? " " : " ");//
HSSFCell cell4 = row.getCell(4);
if (null == cell4) {
cell4 = row.createCell(4);
}
cell4.setCellValue(user.getPhone());//
rowIndex++;
}
String fileName = " ";
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// response ，
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream sout = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(sout);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
logger.error(" excel :", e);
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e) {
logger.error(" excel :", e);
}
} Excel 2007のエクスポート:
@RequestMapping(value = "/export2007.do", method = RequestMethod.GET)
public void export2007(HttpServletRequest request, HttpServletResponse response) {
List list = new ArrayList();
User user1 = new User();
user1.setId(100000000L);
user1.setUsername(" ");
user1.setHead("http://qzapp.qlogo.cn/qzapp/101357640/3C94155CAB4E28517D8435BF404B52F1/100");
user1.setSex(0);
user1.setPhone("18800000000");
User user2 = new User();
user2.setId(100000001L);
user2.setUsername(" ");
user2.setHead("http://q.qlogo.cn/qqapp/1105676675/9DA6D356F4FE1DF0E63BD07334680BF2/100");
user2.setSex(0);
user2.setPhone("18800000001");
list.add(user1);
list.add(user2);
XSSFWorkbook wb = null;
try {
// excel
String basePath = request.getSession().getServletContext().getRealPath("/");
String excel = basePath + "/excel/user_model.xlsx";
File fi = new File(excel);
// excel
wb = new XSSFWorkbook(new FileInputStream(fi));
// sheet
XSSFSheet sheet = wb.getSheetAt(0);
//
int rowIndex = 1;
int j = 1;
for (User user : list) {
XSSFRow row = sheet.getRow(rowIndex);
if (null == row) {
row = sheet.createRow(rowIndex);
}
XSSFCell cell0 = row.getCell(0);
if (null == cell0) {
cell0 = row.createCell(0);
}
cell0.setCellValue(user.getId());//
XSSFCell cell1 = row.getCell(1);
if (null == cell1) {
cell1 = row.createCell(1);
}
cell1.setCellValue(user.getUsername());//
XSSFCell cell2 = row.getCell(2);
if (null == cell2) {
cell2 = row.createCell(2);
}
cell2.setCellValue(user.getHead());//
XSSFCell cell3 = row.getCell(3);
if (null == cell3) {
cell3 = row.createCell(3);
}
cell3.setCellValue(user.getSex() == 0 ? " " : " ");//
XSSFCell cell4 = row.getCell(4);
if (null == cell4) {
cell4 = row.createCell(4);
}
cell4.setCellValue(user.getPhone());//
rowIndex++;
}
String fileName = " ";
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// response ，
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
ServletOutputStream sout = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(sout);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
logger.error(" excel :", e);
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e) {
logger.error(" excel :", e);
}
} エクスポートの結果は次のとおりです.
エクスポート成功!リソースのダウンロード先:
http://download.csdn.net/download/tjcyjd/9998721