<span style="font-size:14px;"> /***
* 给出 模板和数据 自动生成Excel
*
* @param type 默认为0
* @param path 模版路径
* @param title 要显示的标题--未用到
* @param exportName 导出名称
* @param tableHead 列表的表头 --未用到
* @param dataList 数据项
* @param dataTypeList 数据项对应的数据类型
* @param mapCount
* @param insertHeadInfo 插入到表头的数据
* @throws Wrong
*/
public static void outputMoudleExcel(int type, String path, String title,
String exportName, List<String> tableHead,
List<List<String>> dataList, String[] dataTypeList,
Map<String, String> mapCount, Map<String,String> insertHeadInfo,HttpServletResponse response)
throws Wrong {
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
HSSFSheet sheet = workbook.getSheetAt(0);
workbook.getFontAt((short) 0).setFontName("宋体");
// 数据字体
HSSFFont dataf = workbook.createFont();
dataf.setFontHeightInPoints((short) 10);
dataf.setFontName("宋体");
// 标题样式 (字体、边框)
HSSFCellStyle titlestyle = workbook.createCellStyle();
titlestyle.setBorderBottom(Border.THIN);
titlestyle.setBorderLeft(Border.THIN);
titlestyle.setBorderRight(Border.THIN);
titlestyle.setBorderTop(Border.THIN);
// 标题字体
HSSFFont titlef = workbook.createFont();
// 字号
titlef.setFontHeightInPoints((short) 12);
// 加粗
titlef.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titlef.setFontName("宋体");
titlestyle.setFont(titlef);
titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 数据样式 (字体、边框)
HSSFCellStyle datastyle = workbook.createCellStyle();
// 下边框
datastyle.setBorderBottom(Border.THIN);
// 左边框
datastyle.setBorderLeft(Border.THIN);
// 右边框
datastyle.setBorderRight(Border.THIN);
// 上边框
datastyle.setBorderTop(Border.THIN);
// 数据样式(数字类型)(字体、边框)
HSSFCellStyle datastyle1 = workbook.createCellStyle();
// 下边框
datastyle1.setBorderBottom(Border.THIN);
// 左边框
datastyle1.setBorderLeft(Border.THIN);
// 右边框
datastyle1.setBorderRight(Border.THIN);
// 上边框
datastyle1.setBorderTop(Border.THIN);
// 数据字体
datastyle1.setFont(dataf);
// 合计样式 (字体、边框)
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setBorderBottom(Border.THIN);
headstyle.setBorderLeft(Border.THIN);
headstyle.setBorderRight(Border.THIN);
headstyle.setBorderTop(Border.THIN);
HSSFFont headf = workbook.createFont();
headf.setFontHeightInPoints((short) 15);
headf.setFontName("宋体");
headf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headstyle.setFont(headf);
// 左右居中
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
String maintitle = "";
String productType = "";
String projectName = "";
if(insertHeadInfo!=null && insertHeadInfo.size()>0){
maintitle = insertHeadInfo.get("maintitle");
productType= insertHeadInfo.get("productType");
projectName =insertHeadInfo.get("projectName");
}
// 循环数据
for (int i = 0; i < dataList.size(); i++) {
HSSFRow rowdata = sheet.createRow((short) 3 + i);
// 设置表格高度
rowdata.setHeightInPoints(20);
for (int j = 0; j < dataList.get(i).size(); j++) {
HSSFCell celldata = rowdata.createCell((short) j);
if (dataTypeList[j].equals("double")) {
datastyle1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
celldata.setCellStyle(datastyle1);
celldata.setCellValue(Double.parseDouble(dataList
.get(i).get(j).equals("") ? "0.00" : dataList
.get(i).get(j)));
} else if (dataTypeList[j].equals("int")) {
datastyle1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
celldata.setCellStyle(datastyle1);
celldata.setCellValue(Integer.parseInt(dataList.get(i)
.get(j).equals("") ? "0" : dataList.get(i).get(
j)));
} else {
datastyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
celldata.setCellStyle(datastyle);
celldata.setCellValue(dataList.get(i).get(j));
}
}
}
sheet.getRow(0).getCell(0).setCellValue(maintitle);//设置表头
sheet.getRow(1).getCell(2).setCellValue(projectName);
sheet.getRow(1).getCell(7).setCellValue(productType);
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader(
"content-disposition",
"attachment; filename="
+ URLEncoder.encode(
exportName
+ (exportName.endsWith(".xls") ? ""
: ".xls"), "UTF-8"));
ServletOutputStream output = response.getOutputStream();
workbook.write(output);
output.flush();
output.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}</span>
获取数据
String path= request.getRealPath("excel/出库信息.xls");
path=path.replace('\\', '/');
Map<String,String> insertHeadInfo = new HashMap<String,String>();//插入到表头的数据
insertHeadInfo.put("maintitle",maintitle );
insertHeadInfo.put("productType", productType);
insertHeadInfo.put("projectName", projectName);
String[] dataTypeList = {"int","String","String","String","String","","","","",""};
return new ModelAndView (new ExcelExport(0, path, "", exportName, tableHead, dataList, dataTypeList, null,insertHeadInfo, response));
<span style="font-size:14px;"></span>