ExcelUtil.java
package com.mingwen.common.utils;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
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.HSSFDataFormat;
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 com.mingwen.product.entity.taobao.ShopGoodsBean;
public class ExcelUtil {
public static void createExcel(HttpServletRequest request, HttpServletResponse response, List<ShopGoodsBean> list,
String fileName, List<String> title) {
try {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
// 生成一个字体
HSSFFont font = workbook.createFont();
// 字体增粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = workbook.createSheet("sheet1");
// 创建Excel的sheet的一行 (表头)
HSSFRow row = sheet.createRow(0);
// 表头内容填充
for (int i = 0; i < title.size(); i++) {
// 设置excel每列宽度
sheet.setColumnWidth(i, 5000);
HSSFCell cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
// 创建内容行
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);// 自动换行
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
for (int j = 0; j < list.size(); j++) {
try {
HSSFRow contentRow = sheet.createRow(j + 1);
for (int k = 0; k < title.size(); k++) {
HSSFCell cell = contentRow.createCell(k);
switch (k) {
case 0:
cell.setCellValue(list.get(j).getShopName());
break;
case 1:
cell.setCellValue(list.get(j).getNumberId());
break;
case 2:
cell.setCellValue(list.get(j).getTitle());
break;
case 3:
cell.setCellValue(list.get(j).getIsbn());
break;
case 4:
cell.setCellValue(list.get(j).getAuthor());
break;
case 5:
cell.setCellValue(list.get(j).getPublishDate());
break;
case 6:
cell.setCellValue(list.get(j).getDataFrom());
break;
case 7:
cell.setCellValue(list.get(j).getGoodState());
break;
case 8:
cell.setCellValue(list.get(j).getOuterId());
break;
case 9:
cell.setCellValue(list.get(j).getSkuId());
break;
case 10:
cell.setCellValue(list.get(j).getTemplate());
break;
case 11:
cell.setCellValue(list.get(j).getTemplateName());
break;
case 12:
cell.setCellValue(list.get(j).getStock());
break;
case 13:
cell.setCellValue(list.get(j).getCid());
break;
case 14:
cell.setCellValue(list.get(j).getPress());
break;
case 15:
cell.setCellValue(list.get(j).getSellingPrice());
break;
default:
break;
}
cell.setCellStyle(cellStyle);
}
} catch (Exception e) {
continue;
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
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("gb2312"), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
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 (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
java导出excel工具类
最新推荐文章于 2025-03-05 10:37:15 发布