贴一下自己写的关于excel导出商品的类

先是导出的几个方法

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


import com.sljr.slmall.data.model.product.Product;
import com.sljr.slmall.data.vo.product.ImportProductVo;
import com.sljr.slmall.data.vo.product.ProductVo;
import com.sljr.slmall.product.srv.service.ProductService;


import groovy.lang.TracingInterceptor;
import lombok.extern.slf4j.Slf4j;


import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Created by dell、 on 2017/9/12.
 */
@RestController
@Slf4j
public class ExportExcel {


@Autowired
ProductService productService;


/***
* 创建表头
* 
* @param workbook
* @param sheet
*/
private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
HSSFRow row = sheet.createRow(0);
// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(0, 16 * 256);
sheet.setColumnWidth(1, 95 * 256);
sheet.setColumnWidth(2, 30 * 256);
sheet.setColumnWidth(3, 12 * 256);
sheet.setColumnWidth(4, 12 * 256);
sheet.setColumnWidth(5, 12 * 256);
sheet.setColumnWidth(6, 12 * 256);
sheet.setColumnWidth(7, 20 * 256);
sheet.setColumnWidth(8, 20 * 256);
sheet.setColumnWidth(9, 20 * 256);
sheet.setColumnWidth(10, 20 * 256);


// 设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);


HSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("商品编号(sku)");
cell.setCellStyle(style);


cell = row.createCell(1);
cell.setCellValue("商品名称");
cell.setCellStyle(style);


cell = row.createCell(2);
cell.setCellValue("规格");
cell.setCellStyle(style);


cell = row.createCell(3);
cell.setCellValue("销售价");
cell.setCellStyle(style);


cell = row.createCell(4);
cell.setCellValue("京东价");
cell.setCellStyle(style);


cell = row.createCell(5);
cell.setCellValue("实时价");
cell.setCellStyle(style);


cell = row.createCell(6);
cell.setCellValue("协议价");
cell.setCellStyle(style);


cell = row.createCell(7);
cell.setCellValue("是否上架(京东)");
cell.setCellStyle(style);


cell = row.createCell(8);
cell.setCellValue("是否上架(善林)");
cell.setCellStyle(style);


cell = row.createCell(9);
cell.setCellValue("所属分类");
cell.setCellStyle(style);


cell = row.createCell(10);
cell.setCellValue("所属平台");
cell.setCellStyle(style);


}


@RequestMapping("getExcel")
public void getExcel(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("商品表");
createTitle(workbook, sheet);
CellStyle center = workbook.createCellStyle();
center.setAlignment(HSSFCellStyle.ALIGN_CENTER);//字体居中
CellStyle left = workbook.createCellStyle();
left.setAlignment(HSSFCellStyle.ALIGN_LEFT);//字体左对齐

List<ImportProductVo> productList = productService.getAllProductIsMarkable();
if (productList == null) {


}
// 新增数据行，并且设置单元格数据
int rowNum = 1;
for (ImportProductVo product : productList) {


HSSFRow row = sheet.createRow(rowNum);

addCell(row, 0, product.getSku(), workbook,center);
addCell(row, 1, product.getName(), workbook,center);
addCell(row, 2, product.getSpec(), workbook,center);
addCell(row, 3, product.getPrice(), workbook,left);
addCell(row, 4, product.getJdPrice(), workbook,left);
addCell(row, 5, product.getRealPrice(), workbook,left);
addCell(row, 6, product.getProtocolPrice(), workbook,left);
addCell(row, 7, product.getIsMarketable(), workbook,center);
addCell(row, 8, product.getIsSlMarketable(), workbook,center);
addCell(row, 9, product.getCategory(), workbook,center);
addCell(row, 10, product.getPlatform(), workbook,center);
rowNum++;
}
OutputStream outputStream = null;
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=product.xls");
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();


if (outputStream != null) {
outputStream.close();
}
}


public void addCell(HSSFRow row, int column, Object val, HSSFWorkbook wb,CellStyle style) {
Cell cell = row.createCell(column);
try {
if (val == null) {
cell.setCellValue("");
} else if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
if ((Integer) val == 1 ) {
String a = "是";
cell.setCellValue(a);
}else if ((Integer) val == 0) {
String a = "否";
cell.setCellValue(a);
}else {
cell.setCellValue((Integer) val);
}
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Date) {
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("yyyy/MM/dd"));
cell.setCellValue((Date) val);
} else if (val instanceof BigDecimal) {
double doubleVal = ((BigDecimal) val).doubleValue();
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("￥#,##0.00"));
cell.setCellValue(doubleVal);
}
cell.setCellStyle(style);
} catch (Exception ex) {
log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
cell.setCellValue(val.toString());
}


}


}