<!-- poi dependency-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
package com.bc.mcode.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
public class POIUtil {
public static void exportExcel(HttpServletResponse response, String sheetName, String excelName,
Object[][] dataSource) {
@SuppressWarnings("resource")
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setColumnWidth(0, 21 * 256);
sheet.setColumnWidth(1, 21 * 256);
sheet.setColumnWidth(2, 21 * 256);
sheet.setColumnWidth(3, 21 * 256);
sheet.setColumnWidth(4, 21 * 256);
sheet.setColumnWidth(5, 21 * 256);
sheet.setColumnWidth(6, 21 * 256);
sheet.setColumnWidth(7, 21 * 256);
sheet.setColumnWidth(8, 21 * 256);
sheet.setColumnWidth(9, 21 * 256);
sheet.setColumnWidth(10, 21 * 256);
sheet.setColumnWidth(11, 21 * 256);
sheet.setColumnWidth(12, 21 * 256);
sheet.setColumnWidth(13, 21 * 256);
sheet.setColumnWidth(14, 21 * 256);
sheet.setColumnWidth(15, 21 * 256);
sheet.setColumnWidth(16, 21 * 256);
sheet.setColumnWidth(17, 21 * 256);
sheet.setColumnWidth(18, 21 * 256);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1, true);
HSSFRow row;
HSSFCell cell;
int len = dataSource.length;
for (int i = 0; i < len; i++) {
row = sheet.createRow(i);
Object[] dataSourceTemp = dataSource[i];
int temp = dataSourceTemp.length;
for (int j = 0; j < temp; j++) {
cell = row.createCell(j);
Object value = dataSourceTemp[j];
if (null == value || "".equals(value)) {
continue;
}
cell.setCellValue(String.valueOf(value));
}
}
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.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=" + java.net.URLEncoder.encode(new String((excelName + ".xls").getBytes(), "UTF-8"), "UTF-8"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
if (bos != null)
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void exportExcel2(HttpServletResponse response, String sheetName1,String sheetName2, String excelName,
Object[][] dataSource1,Object[][] dataSource2) {
@SuppressWarnings("resource")
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet(sheetName1);
HSSFSheet sheet2 = workbook.createSheet(sheetName2);
sheet1.setColumnWidth(0, 21 * 256);
sheet1.setColumnWidth(1, 21 * 256);
sheet1.setColumnWidth(2, 21 * 256);
sheet1.setColumnWidth(3, 21 * 256);
sheet1.setColumnWidth(4, 21 * 256);
sheet1.setColumnWidth(5, 21 * 256);
sheet1.setColumnWidth(6, 21 * 256);
sheet1.setColumnWidth(7, 21 * 256);
sheet1.setColumnWidth(8, 21 * 256);
sheet1.setColumnWidth(9, 21 * 256);
sheet1.setColumnWidth(10, 21 * 256);
sheet1.setColumnWidth(11, 21 * 256);
sheet1.setColumnWidth(12, 21 * 256);
sheet1.setColumnWidth(13, 21 * 256);
sheet1.setColumnWidth(14, 21 * 256);
sheet1.setColumnWidth(15, 21 * 256);
sheet1.setColumnWidth(16, 21 * 256);
sheet1.setColumnWidth(17, 21 * 256);
sheet1.setColumnWidth(18, 21 * 256);
sheet1.autoSizeColumn(1);
sheet1.autoSizeColumn(1, true);
sheet2.setColumnWidth(0, 21 * 256);
sheet2.setColumnWidth(1, 21 * 256);
sheet2.setColumnWidth(2, 21 * 256);
sheet2.setColumnWidth(3, 21 * 256);
sheet2.setColumnWidth(4, 21 * 256);
sheet2.setColumnWidth(5, 21 * 256);
sheet2.setColumnWidth(6, 21 * 256);
sheet2.setColumnWidth(7, 21 * 256);
sheet2.setColumnWidth(8, 21 * 256);
sheet2.setColumnWidth(9, 21 * 256);
sheet2.setColumnWidth(10, 21 * 256);
sheet2.setColumnWidth(11, 21 * 256);
sheet2.setColumnWidth(12, 21 * 256);
sheet2.setColumnWidth(13, 21 * 256);
sheet2.setColumnWidth(14, 21 * 256);
sheet2.setColumnWidth(15, 21 * 256);
sheet2.setColumnWidth(16, 21 * 256);
sheet2.setColumnWidth(17, 21 * 256);
sheet2.setColumnWidth(18, 21 * 256);
sheet2.autoSizeColumn(1);
sheet2.autoSizeColumn(1, true);
HSSFRow row;
HSSFCell cell;
int len1 = dataSource1.length;
int len2 = dataSource2.length;
for (int i = 0; i < len1; i++) {
row = sheet1.createRow(i);
Object[] dataSourceTemp = dataSource1[i];
int temp = dataSourceTemp.length;
for (int j = 0; j < temp; j++) {
cell = row.createCell(j);
Object value = dataSourceTemp[j];
if (null == value || "".equals(value)) {
continue;
}
cell.setCellValue(String.valueOf(value));
}
}
for (int i = 0; i < len2; i++) {
row = sheet2.createRow(i);
Object[] dataSourceTemp = dataSource2[i];
int temp = dataSourceTemp.length;
for (int j = 0; j < temp; j++) {
cell = row.createCell(j);
Object value = dataSourceTemp[j];
if (null == value || "".equals(value)) {
continue;
}
cell.setCellValue(String.valueOf(value));
}
}
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.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=" + java.net.URLEncoder.encode(new String((excelName + ".xls").getBytes(), "UTF-8"), "UTF-8"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
if (bos != null)
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
网友评论