//單sheet調用模板public void exportInfo(String ids,HttpServletResponse response) { try {//單元格列頭String[] rowName = {"所屬單位","隱患描述", "隱患分類", "隱患級別", "隱患狀態", "隱患來源"};//創建list存數據List<Object[]> dataList = new ArrayList<>();//從數據庫中查詢出符合條件 要導出的數據List<Hiddendanger> list = new ArrayList<>();list = hiddendangerMapper.selectListByParams();//開始填充數據Object[] data = null;for (Hiddendanger eduExerciseProblem : list) {data = new Object[17];// 所屬單位if (org.apache.commons.lang3.StringUtils.isNotBlank(eduExerciseProblem.getQyname())) {data[0] = eduExerciseProblem.getQyname();} else {data[0] = "";}//將數據放入存數據的list中dataList.add(data);}ExportExcelUtil export = new ExportExcelUtil("隱患信息", rowName, dataList, response);String fileName = "隱患信息" + DateFormatUtils.format(new Date(), "yyyy-MM-dd") + ".xls";export.exportExcel(fileName);} //多sheet調用模板 @Overridepublic void peopleAndPostExport(HttpServletResponse response) throws Exception {List<Workplace> workplaceList = workplaceMapper.getWorkplaceByQyidAndName(UserUtil.getCurrQyId(), null);List<SeUser> userList = userFeign.getUsersByQyId(UserUtil.getCurrQyId()).getData();List<List<String>> data = new ArrayList<>();List<List<String>> data1 = new ArrayList<>();List<List<String>> data2 = new ArrayList<>();String[] headers = { "部門", "崗位", "人員姓名", "身份證號"};String[] headers1 = { "人員姓名", "身份證號"};String[] headers2 = { "部門", "崗位" };for(SeUser eduExerciseProblem:userList){List<String> list = new ArrayList<>();list.add(eduExerciseProblem.getRealname());list.add(eduExerciseProblem.getIdcard());data1.add(list);}for(Workplace eduExerciseProblem:workplaceList){List<String> list = new ArrayList<>();list.add(eduExerciseProblem.getDeptname());list.add(eduExerciseProblem.getWorkplace());data2.add(list);}ExportExcelUtil export = new ExportExcelUtil(response);HSSFWorkbook workbook = new HSSFWorkbook();String fileName = "人員-崗位信息導入模板" + DateFormatUtils.format(new Date(), "yyyy-MM-dd") + ".xls";export.exportExcels(workbook, 0, "數據導入頁", headers, data);export.exportExcels(workbook, 1, "人員信息頁", headers1, data1);export.exportExcels(workbook, 2, "崗位信息頁", headers2, data2);OutputStream sos = response.getOutputStream();response.setContentType("application/octet-stream");// 設置下載文件名response.addHeader("Content-Disposition","attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");// 向客戶端輸出文件workbook.write(sos);sos.flush();sos.close();} package com.zhjt.utils;import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress;import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.LinkedList; import java.util.List; import java.util.Map;public class ExportExcelUtil {// 顯示的導出表的標題private String title;// 導出表的列名private String[] firstrowName;private String[] rowName;private List<Object[]> dataList = new ArrayList<Object[]>();HttpServletResponse response;// 構造方法，傳入要導出的數據public ExportExcelUtil(String title, String[] rowName, List<Object[]> dataList, HttpServletResponse res) {this.dataList = dataList;this.rowName = rowName;this.title = title;this.response = res;}public ExportExcelUtil(String title,String[] firstrowName, String[] rowName, List<Object[]> dataList, HttpServletResponse res) {this.dataList = dataList;this.firstrowName=firstrowName;this.rowName = rowName;this.title = title;this.response = res;}/**** @Title: fhexport* @Description: TODO(任務下達匯總復合表導出)* @throws Exception* @retrun void 返回值* @date 2017年8月2日 下午4:48:47* @throws*/public void fhexport() throws Exception {HSSFWorkbook workbook = new HSSFWorkbook(); // 創建工作簿對象HSSFSheet sheet = workbook.createSheet(title); // 創建工作表// 產生表格標題行HSSFRow rowm = sheet.createRow(0);HSSFCell cellTiltle = rowm.createCell(0);// sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(workbook); // 單元格樣式對象sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(title);// 定義所需列數int columnNumFirst=firstrowName.length;int columnNum = rowName.length;HSSFRow rowRowName=sheet.createRow(2); // 在索引2的位置創建行(最頂端的行開始的第二行)// 將列頭設置到sheet的單元格中for (int n = 0; n <=columnNumFirst; n++) {HSSFCell cellRowName=null;HSSFRichTextString text=null;if(n>2){cellRowName = rowRowName.createCell((n-1)*2-1); // 創建列頭對應個數的單元格text = new HSSFRichTextString(firstrowName[n-1]);cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式cellRowName = rowRowName.createCell((n-1)*2); // 創建列頭對應個數的單元格text = new HSSFRichTextString(firstrowName[n-1]);cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式sheet.addMergedRegion(new CellRangeAddress(2,2,(n-1)*2-1,(n-1)*2));}else{cellRowName = rowRowName.createCell(n); // 創建列頭對應個數的單元格if(n<1){text = new HSSFRichTextString("序號");}else{text = new HSSFRichTextString(firstrowName[n-1]);}cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式}}HSSFRow rowRowName2= sheet.createRow(3); // 在索引3的位置創建行(最頂端的行開始的第三行)// 將列頭設置到sheet的單元格中for (int n = 0; n < columnNum; n++) {HSSFCell cellRowName = rowRowName2.createCell(n); // 創建列頭對應個數的單元格cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型HSSFRichTextString text = new HSSFRichTextString(rowName[n]);cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式if(n<3){sheet.addMergedRegion(new CellRangeAddress(2,3,n,n));}}// 將查詢出的數據設置到sheet對應的單元格中int r=1,p=0;for (int i = 0; i < dataList.size(); i++) {Object[] obj = dataList.get(i);// 遍歷每個對象HSSFRow row = sheet.createRow(i + 4);// 創建所需的行數int l=0;for (int j = 0; j < obj.length; j++) {HSSFCell cell = null; // 設置單元格的數據類型if (j == 0) {cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);if (i%5 != 0||i==0){cell.setCellValue(r);}else{r++;cell.setCellValue(r);}cell.setCellStyle(style); // 設置單元格樣式} else {cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);cell.setCellValue(obj[j].toString()); // 設置單元格的值cell.setCellStyle(style); // 設置單元格樣式if (i%5 == 0&&i!=0){if(j>2){sheet.addMergedRegion(new CellRangeAddress(p*5 + 8,p*5 + 8,j+l,(j-1)*2));l++;}}if(i==dataList.size()-1){if(j>2){sheet.addMergedRegion(new CellRangeAddress(p*5 + 8,p*5 + 8,j+l,(j-1)*2));l++;}}}}if (i%5 == 0&&i!=0){ p++;} // if(i==dataList.size()-1){p++;}//合并序號和行業sheet.addMergedRegion(new CellRangeAddress(i*5+4,i*5 + 8,0,0));sheet.addMergedRegion(new CellRangeAddress(i*5+4,i*5 + 8,1,1));}// 讓列寬隨著導出的列長自動適應for (int colNum = 0; colNum < columnNum; colNum++) {int columnWidth = sheet.getColumnWidth(colNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {HSSFRow currentRow;// 當前行未被使用過if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(colNum) != null) {HSSFCell currentCell = currentRow.getCell(colNum);currentCell.setCellType(HSSFCell.CELL_TYPE_STRING);String temp = currentCell.getStringCellValue();if(temp!=null&&!"".equals(temp)){int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}if (colNum == 0) {sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);} else {sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);}} // sheet.addMergedRegion(new CellRangeAddress(2,3,0,0)); // sheet.addMergedRegion(new CellRangeAddress(2,3,1,1)); // sheet.addMergedRegion(new CellRangeAddress(2,3,2,2));OutputStream sos = response.getOutputStream();response.setContentType("application/octet-stream");// 獲取原文件名String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";// 設置下載文件名response.addHeader("Content-Disposition","attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");// 向客戶端輸出文件workbook.write(sos);sos.flush();sos.close();}/** 導出數據，數據中的第一列數據將會作為序號,該方法有問題*/public void export() throws Exception {HSSFWorkbook workbook = new HSSFWorkbook(); // 創建工作簿對象HSSFSheet sheet = workbook.createSheet(title); // 創建工作表// 產生表格標題行HSSFRow rowm = sheet.createRow(0);HSSFCell cellTiltle = rowm.createCell(0);// sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(workbook); // 單元格樣式對象sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(title);// 定義所需列數int columnNum = rowName.length;HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置創建行(最頂端的行開始的第二行)// 將列頭設置到sheet的單元格中for (int n = 0; n < columnNum; n++) {HSSFCell cellRowName = rowRowName.createCell(n); // 創建列頭對應個數的單元格cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型HSSFRichTextString text = new HSSFRichTextString(rowName[n]);cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式}// 將查詢出的數據設置到sheet對應的單元格中for (int i = 0; i < dataList.size(); i++) {Object[] obj = dataList.get(i);// 遍歷每個對象HSSFRow row = sheet.createRow(i + 3);// 創建所需的行數for (int j = 0; j < obj.length; j++) {HSSFCell cell = null; // 設置單元格的數據類型if (j == 0) {cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);cell.setCellValue(i + 1);} else {cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);cell.setCellValue((null!=obj[j])?obj[j].toString():""); // 設置單元格的值}cell.setCellStyle(style); // 設置單元格樣式}}// 讓列寬隨著導出的列長自動適應for (int colNum = 0; colNum < columnNum; colNum++) {int columnWidth = sheet.getColumnWidth(colNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {HSSFRow currentRow;// 當前行未被使用過if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(colNum) != null) {HSSFCell currentCell = currentRow.getCell(colNum);currentCell.setCellType(HSSFCell.CELL_TYPE_STRING);String temp = currentCell.getStringCellValue();if(temp!=null&&!"".equals(temp)){int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}if (colNum == 0) {sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);} else {sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);}}OutputStream sos = response.getOutputStream();response.setContentType("application/octet-stream");// 獲取原文件名String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";// 設置下載文件名response.addHeader("Content-Disposition","attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");// 向客戶端輸出文件workbook.write(sos);sos.flush();sos.close();}/**** @param sheetName* @param titles 表頭* @param datas 數據，每行對應map中一條記錄，統計項為key，表頭對應數據放在value的String[]中。* @return HSSFWorkbook* @throws IOException*/public static HSSFWorkbook exportExcel(String sheetName,String[] titles, Map<String,String[]> datas) throws IOException{int[] columWidth = new int[titles.length+1];HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet(sheetName);// 產生表格標題行HSSFRow rowm = sheet.createRow(0);HSSFCell cellTiltle = rowm.createCell(0);// sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】HSSFCellStyle columnTopStyle = getColumnTopStyle(wb);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(wb); // 單元格樣式對象//設置合并前兩行和所有列sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (titles.length)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(sheetName);//設置表頭行第一列為空HSSFRow row = sheet.createRow(2);HSSFCell cell = row.createCell(0);cell.setCellValue("");cell.setCellStyle(style);// 定義所需列數int columnNum = titles.length;for (int i = 0; i < columnNum; i++) {HSSFCell cellRowName = row.createCell(i+1); // 創建列頭對應個數的單元格cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型HSSFRichTextString text = new HSSFRichTextString(titles[i]);cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式int length = titles[i].getBytes().length*256;sheet.setColumnWidth(i+1, length);columWidth[i+1] = length;}List<String> keys = new LinkedList<String>(datas.keySet());for (int i = 0; i < keys.size(); i++) {//map每條記錄對應一行String key = keys.get(i);//map的key為首列查詢內容row = sheet.createRow(i + 3);//前兩行為標題。第三行為表頭row.createCell(0).setCellValue(key);int length = key.getBytes().length*256;if(length>columWidth[0]){columWidth[0] = length;sheet.setColumnWidth(0, columWidth[0]);}String[] data = datas.get(key);for (int j = 0; j < data.length; j++) {//循環數據并填充單元格int length2 = data[j].getBytes().length*300;if(length2>columWidth[j+1]){columWidth[j+1] = length2;sheet.setColumnWidth(j+1, columWidth[j+1]);}cell = row.createCell(j+1);cell.setCellValue(data[j]);cell.setCellStyle(style); // 設置單元格樣式}}return wb;}/*** @Description 生成復數sheet的excel* @Param [sheetNames, titless, datas] 各sheet名稱集合，各sheet中標題名稱集合，各sheet中數據集合* @return org.apache.poi.hssf.usermodel.HSSFWorkbook**/public static HSSFWorkbook exportPluralSheetExcel(List<String> sheetNames,List<String[]> titless, List<List<String[]>> datas) throws IOException{HSSFWorkbook wb = new HSSFWorkbook();for (int s = 0; s < sheetNames.size(); s++) {String sheetName = sheetNames.get(s);HSSFSheet sheet = wb.createSheet(sheetName);String[] titles = titless.get(s);// 產生表格標題行int[] columWidth = new int[titles.length];HSSFRow rowm = sheet.createRow(0);HSSFCell cellTiltle = rowm.createCell(0);// sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】HSSFCellStyle columnTopStyle = getColumnTopStyle(wb);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(wb); // 單元格樣式對象//設置合并前兩行和所有列sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (titles.length-1)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(sheetName);//設置表頭行第一列為空HSSFRow row = sheet.createRow(2);HSSFCell cell = row.createCell(0);cell.setCellValue("");cell.setCellStyle(style);// 定義所需列數int columnNum = titles.length;for (int i = 0; i < columnNum; i++) {HSSFCell cellRowName = row.createCell(i); // 創建列頭對應個數的單元格cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型HSSFRichTextString text = new HSSFRichTextString(titles[i]);cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式int length = titles[i].getBytes().length*256;sheet.setColumnWidth(i, length);columWidth[i] = length;}List<String[]> sdata = datas.get(s);for (int i = 0; i < sdata.size(); i++) {row = sheet.createRow(i + 3);//前兩行為標題。第三行為表頭String[] data = sdata.get(i);for (int j = 0; j < data.length; j++) {//循環數據并填充單元格int length2 = data[j].getBytes().length * 300;if (length2 > columWidth[j]) {columWidth[j] = length2;sheet.setColumnWidth(j, columWidth[j]);}cell = row.createCell(j);cell.setCellValue(data[j]);cell.setCellStyle(style); // 設置單元格樣式}}// List<String> keys = new LinkedList<String>(datas.keySet()); // for (int i = 0; i < keys.size(); i++) {//map每條記錄對應一行 // String key = keys.get(i);//map的key為首列查詢內容 // row = sheet.createRow(i + 3);//前兩行為標題。第三行為表頭 // row.createCell(0).setCellValue(key); // int length = key.getBytes().length*256; // if(length>columWidth[0]){ // columWidth[0] = length; // sheet.setColumnWidth(0, columWidth[0]); // } // // String[] data = datas.get(key); // for (int j = 0; j < data.length; j++) {//循環數據并填充單元格 // int length2 = data[j].getBytes().length*300; // if(length2>columWidth[j+1]){ // columWidth[j+1] = length2; // sheet.setColumnWidth(j+1, columWidth[j+1]); // } // cell = row.createCell(j+1); // cell.setCellValue(data[j]); // cell.setCellStyle(style); // 設置單元格樣式 // } // }}return wb;}/** 列頭單元格樣式*/public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {// 設置字體HSSFFont font = workbook.createFont();// 設置字體大小font.setFontHeightInPoints((short) 11);// 字體加粗font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 設置字體名字font.setFontName("Courier New");// 設置樣式;HSSFCellStyle style = workbook.createCellStyle();// 設置底邊框;style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 設置底邊框顏色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 設置左邊框;style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 設置左邊框顏色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 設置右邊框;style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 設置右邊框顏色;style.setRightBorderColor(HSSFColor.BLACK.index);// 設置頂邊框;style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 設置頂邊框顏色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在樣式用應用設置的字體;style.setFont(font);// 設置自動換行;style.setWrapText(false);// 設置水平對齊的樣式為居中對齊;style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 設置垂直對齊的樣式為居中對齊;style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);return style;}/** 列數據信息單元格樣式*/public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {// 設置字體HSSFFont font = workbook.createFont();// 設置字體大小// font.setFontHeightInPoints((short)10);// 字體加粗// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 設置字體名字font.setFontName("Courier New");// 設置樣式;HSSFCellStyle style = workbook.createCellStyle();// 設置底邊框;style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 設置底邊框顏色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 設置左邊框;style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 設置左邊框顏色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 設置右邊框;style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 設置右邊框顏色;style.setRightBorderColor(HSSFColor.BLACK.index);// 設置頂邊框;style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 設置頂邊框顏色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在樣式用應用設置的字體;style.setFont(font);// 設置自動換行;style.setWrapText(false);// 設置水平對齊的樣式為居中對齊;style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 設置垂直對齊的樣式為居中對齊;style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);return style;}public static HSSFWorkbook exportExcel(String titleName,String[] columnName, List<Object[]> datas){HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet();// 產生表格標題行HSSFRow title = sheet.createRow(0);HSSFCell cellTitle = title.createCell(0);HSSFCellStyle columnTopStyle = getColumnTopStyle(wb);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(wb); // 單元格樣式對象//設置合并前兩行和所有列sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length));cellTitle.setCellStyle(columnTopStyle);cellTitle.setCellValue(titleName);//設置表頭行HSSFRow row1 = sheet.createRow(1);for(int i=0;i<=columnName.length;++i) {HSSFCell cell = row1.createCell(i);if(i==0) {cell.setCellValue("序號");}else {cell.setCellValue(columnName[i-1]);} cell.setCellStyle(style);}//數據填充部分for(int i=0;i<datas.size();++i) {HSSFRow row = sheet.createRow(i+2);for(int j=0;j<=columnName.length;++j) {HSSFCell cell = row.createCell(j);if(j==0) {cell.setCellValue(i+1);}else {cell.setCellValue(datas.get(i)[j-1]==null?"":datas.get(i)[j-1].toString());}cell.setCellStyle(style);}}return wb;}public void exportExcel(String fileName) throws Exception {HSSFWorkbook workbook = new HSSFWorkbook(); // 創建工作簿對象HSSFSheet sheet = workbook.createSheet(title); // 創建工作表// 產生表格標題行HSSFRow rowm = sheet.createRow(0);HSSFCell cellTiltle = rowm.createCell(0);// sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 獲取列頭樣式對象HSSFCellStyle style = getStyle(workbook); // 單元格樣式對象sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(title);// 定義所需列數int columnNum = rowName.length;HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置創建行(最頂端的行開始的第二行)// 將列頭設置到sheet的單元格中for (int n = 0; n < columnNum; n++) {HSSFCell cellRowName = rowRowName.createCell(n); // 創建列頭對應個數的單元格cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型HSSFRichTextString text = new HSSFRichTextString(rowName[n]);cellRowName.setCellValue(text); // 設置列頭單元格的值cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式}// 將查詢出的數據設置到sheet對應的單元格中for (int i = 0; i < dataList.size(); i++) {Object[] obj = dataList.get(i);// 遍歷每個對象HSSFRow row = sheet.createRow(i + 3);// 創建所需的行數for (int j = 0; j < obj.length; j++) {HSSFCell cell = null; // 設置單元格的數據類型cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);if(obj[j] != null)cell.setCellValue(obj[j].toString()); // 設置單元格的值elsecell.setCellValue("");cell.setCellStyle(style); // 設置單元格樣式}}// 讓列寬隨著導出的列長自動適應for (int colNum = 0; colNum < columnNum; colNum++) {int columnWidth = sheet.getColumnWidth(colNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {HSSFRow currentRow;// 當前行未被使用過if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(colNum) != null) {HSSFCell currentCell = currentRow.getCell(colNum);currentCell.setCellType(HSSFCell.CELL_TYPE_STRING);String temp = currentCell.getStringCellValue();if(temp!=null&&!"".equals(temp)){int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}try {if (colNum == 0) {sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);} else {sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);}} catch (IllegalArgumentException e) {sheet.setColumnWidth(colNum, 65280);}}OutputStream sos = response.getOutputStream();response.setContentType("application/octet-stream");// 獲取原文件名//String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";// 設置下載文件名response.addHeader("Content-Disposition","attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");// 向客戶端輸出文件workbook.write(sos);sos.flush();sos.close();}/*** @Description: 導出Excel 包含多張sheet* @author wang* @param workbook* @param sheetNum (sheet的位置，0表示第一個表格中的第一個sheet)* @param sheetTitle （sheet的名稱）* @param headers （表格的列標題）* @param result （表格的數據）* @throws Exception*/public void exportExcels(HSSFWorkbook workbook, int sheetNum,String sheetTitle, String[] headers, List<List<String>> result) throws Exception {// 生成一個表格HSSFSheet sheet = workbook.createSheet();workbook.setSheetName(sheetNum, sheetTitle);// 設置表格默認列寬度為20個字節sheet.setDefaultColumnWidth((short) 20);// 生成一個樣式HSSFCellStyle style = workbook.createCellStyle();// 設置這些樣式style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一個字體HSSFFont font = workbook.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字體應用到當前的樣式style.setFont(font);// 指定當單元格內容顯示不下時自動換行style.setWrapText(true);// 產生表格標題行HSSFRow row = sheet.createRow(0);for (int i = 0; i < headers.length; i++) {HSSFCell cell = row.createCell((short) i);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(headers[i]);cell.setCellValue(text.toString());}// 遍歷集合數據，產生數據行if (result != null) {Integer index = 1;for (List<String> m : result) {row = sheet.createRow(index);int cellIndex = 0;for (String str : m) {HSSFCell cell = row.createCell((short) cellIndex);if(org.apache.commons.lang3.StringUtils.isNotBlank(str)){cell.setCellValue(str.toString());}else {cell.setCellValue("");}cellIndex++;}index++;}}} }

總結

以上是生活随笔為你收集整理的后台excel导出（单sheet 多sheet）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。