SpringMVC 导出Excel案例

来源：互联网发布：办公软件的运用编辑：程序博客网时间：2024/06/05 21:07

//excel 导出涉及单元格合并样式设置等

@RequestMapping()

public void packReview(HttpServletRequest request,HttpServletResponse response) throws IOException {

//获取参数

String deliverIds = ReqUtils.getString(request, "deliverIds");
String[] adorderids = deliverIds.split(",");

HSSFWorkbook wb = new HSSFWorkbook ();
//3.分批次生成excel
if(adorderids.length>0){
try{
wb = exportDataToExcelXLSX(wb, adorderids);
}catch(RuntimeException runMsg){
// throw new Exception("查询数据信息异常 ");
logger.debug("查询数据信息异常");
}
}
//1.设置相应头
String filename = "拣货单";
String fileSuff = "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
fileSuff = sdf.format(new Date());
String filenames =filename +fileSuff +".xls";
try{
filenames =encodeChineseDownloadFileName(request,filenames);
}catch(Exception e){
e.printStackTrace();
}
response.reset();
response.setHeader("Content-disposition", filenames);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filenames);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();

}

/**
* 设置excel样式和数值
* @param wb
* @param adorderids
* @param companyatr
* @return
*/
private HSSFWorkbook exportDataToExcelXLSX(HSSFWorkbook wb, String[] adorderids){
for(int i=0;i<adorderids.length;i++) {
StringBuffer sql=new StringBuffer();
//根据参数查找数据略

List<Map<String, Object>> list = this.jdbcDao.queryForList(sql.toString(), new Object[] {adorderids[i]});
String[] assetHeadTemp = {"商品","条码","尺码","颜色","数量"};
String[] assetNameTemp = {"mingcheng","barcode","chima","yanse","jianshu"};
HSSFSheet sheet = null;
CellStyle columnHeadStyle = wb.createCellStyle();
columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
columnHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
columnHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
columnHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
columnHeadStyle.setWrapText(true);

CellStyle columnStyle = wb.createCellStyle();
columnStyle.cloneStyleFrom(columnHeadStyle);
columnStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
columnStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
Font f = wb.createFont();// 字体
f.setFontHeightInPoints((short) 10);// 字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//BOLDWEIGHT_BOLD);// 加粗
columnStyle.setFont(f);

CellStyle colStyle = wb.createCellStyle();
colStyle.cloneStyleFrom(columnHeadStyle);
colStyle.setFont(f);

Row row;
Cell cell;
sheet = wb.createSheet(adorderids[i]);
row = sheet.createRow(0);
row.setHeight((short) 500);

cell = row.createCell(0);

//合并单元格前需要先遍历要合并的单元格并添加样式

//若合并前没有遍历设样式合并后的单元格没有边框

for(int j=0;j<assetHeadTemp.length;j++){
cell = row.createCell(j);
cell.setCellStyle(columnStyle);
cell.setCellValue("订单号： "+adorderids[i]);

}

//合并单元格（起始行，截止行，起始列，截止列）

sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));

row = sheet.createRow(3);
sheet.createFreezePane(0, 4, 0, 4); //固定行列滚动时不动（冻结的列，冻结的行，右边可见的第一列，下边可见的第一行数）
row.setHeight((short) 450);
for(int j=0;j<assetHeadTemp.length;j++){
cell = row.createCell(j);
cell.setCellStyle(columnHeadStyle);
cell.setCellValue(assetHeadTemp[j]);
if(j==0) {
sheet.setColumnWidth(j, (int)6000);
}else if(j==1){
sheet.setColumnWidth(j, (int)5500);
}else {
sheet.setColumnWidth(j, (int)3000);
}

}

row = sheet.createRow(1);
row.setHeight((short) 500);
cell = row.createCell(0);
for(int j=0;j<assetHeadTemp.length;j++){
cell = row.createCell(j);
cell.setCellStyle(columnStyle);
cell.setCellValue("订单序号:"+list.get(0).get("orderxuhao"));
}
sheet.addMergedRegion(new CellRangeAddress(1,1,0,4));
cell.setCellStyle(columnStyle);

row = sheet.createRow(2);
row.setHeight((short) 500);
cell = row.createCell(0);
for(int j=0;j<assetHeadTemp.length;j++){
cell = row.createCell(j);
cell.setCellStyle(columnStyle);
cell.setCellValue("品牌方备注： "+(list.get(0).get("brandRemark")!=null?list.get(0).get("brandRemark"):""));
}
sheet.addMergedRegion(new CellRangeAddress(2,2,0,4));
cell.setCellStyle(columnStyle);

int rowIndex = 4;
Double num=0d;
for(Map<String,Object> map : list){
row = sheet.createRow(rowIndex++);
row.setHeight((short) 450);
int index = 0;
for(int n=0;n<assetNameTemp.length;n++ ){
cell = row.createCell(index++);
cell.setCellStyle(columnHeadStyle);
if(assetNameTemp[n].equals("mingcheng")) {
cell.setCellValue(map.get(assetNameTemp[n])!=null ?map.get(assetNameTemp[n]).toString().substring(0, map.get(assetNameTemp[n]).toString().indexOf("【")):"");
}else if(assetNameTemp[n].equals("jianshu")) {
cell.setCellValue(Integer.valueOf(map.get(assetNameTemp[n]).toString().substring(0, map.get(assetNameTemp[n]).toString().indexOf("."))));
}else {
cell.setCellValue(map.get(assetNameTemp[n])!=null ?map.get(assetNameTemp[n]).toString():"");
}
}
}
}
return wb;
}
/**
* 对文件流输出下载的中文文件名进行编码屏蔽各种浏览器版本的差异性
* @throws UnsupportedEncodingException
*/
public static String encodeChineseDownloadFileName(
HttpServletRequest request, String pFileName) throws Exception {

String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent){
if (-1 != agent.indexOf("Firefox")) {//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";
}else if (-1 != agent.indexOf("Chrome")) {//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}

阅读全文

0 0