通用Excel导出Demo

每天进步一点点！

分享一个Excel导出Demo

import java.io.File;import java.io.FileOutputStream;import java.util.ArrayList;import javax.servlet.http.HttpServletRequest;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.util.CellRangeAddress;import net.sf.json.JSONArray;import net.sf.json.JSONObject;/** * * @Description: 该类用于各种表格的导出Excel* @author 张林强 */public class ExcelCommon {/** * 将传入的数据转为excel<br/> * @param column 数据列名  含有columnViewData：XX和columnViewField：XX的集合 * @param data   数据内容  前台显示的表格data数据 * @param excelTitleexcel标题名称 *  */public File dataToExcel(JSONArray column,JSONArray data,String excelTitle,HttpServletRequest request) throws Exception{ArrayList<ArrayList<String>> allData = getExcelData(column, data);HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();////创建标题//HSSFRow rowTitle = sheet.createRow(0);//HSSFCell cellTitle = rowTitle.createCell(0);////标题栏合并//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,  allData.get(0).size()-1));////标题居中//cellTitle.setCellStyle(getTitleCellStyle(workbook));//cellTitle.setCellValue(excelTitle);HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);ArrayList<String> list = allData.get(0);for(int i = 0 ; i < list.size() ; i++){sheet.setColumnWidth(i, list.get(i).getBytes().length*256);}//用户数据for(int i=0;i<allData.size();i++){HSSFRow row = sheet.createRow(i);ArrayList<String> rowData = allData.get(i);for(int j=0;j<rowData.size();j++){HSSFCell cell = row.createCell(j);cell.setCellValue(rowData.get(j));cell.setCellStyle(style);}}String excelTempDir = request.getSession().getServletContext().getRealPath("/")+"temp/";File tempFile = new File(excelTempDir);if(!tempFile.exists()){tempFile.mkdir();}excelTempDir += System.currentTimeMillis()+".xls";File excelFile = new File(excelTempDir);FileOutputStream fos = new FileOutputStream(excelFile);workbook.write(fos);fos.close();return excelFile;}/** * 将传入的档案数据转为excel<br/> * @param column 数据列名 * @param data   数据内容 * @param excelTitleexcel标题名称 *  */public HSSFWorkbook archiveDataToExcel(JSONArray column,JSONArray data,String excelTitle,HttpServletRequest request) throws Exception{ArrayList<ArrayList<String>> allData = getExcelData(column, data);HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();//创建标题HSSFRow rowTitle = sheet.createRow(0);HSSFCell cellTitle = rowTitle.createCell(0);//标题栏合并sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,  allData.get(0).size()-1));//标题居中cellTitle.setCellStyle(getTitleCellStyle(workbook));cellTitle.setCellValue(excelTitle);for(int i=0;i<allData.size();i++){HSSFRow row = sheet.createRow(i+1);ArrayList<String> rowData = allData.get(i);for(int j=0;j<rowData.size();j++){HSSFCell cell = row.createCell(j);if(i != 0){if(j == 0){//cell.setCellStyle(getTitleCellStyle(workbook));cell.setCellValue(i);}else{cell.setCellValue(rowData.get(j));}}else{cell.setCellValue(rowData.get(j));}}}return workbook;}/** * 通过 查询到的结果转化导出EXCEL 需要的数据集合 * @param column 通过getXXXColumnView方法得到的集合 * @param data通过getXXXData方法得到的集合     */private ArrayList<ArrayList<String>> getExcelData(JSONArray column,JSONArray data){//字段名称ArrayList<String> columnName = new ArrayList<String>();//数据库字段名ArrayList<String> columnDbName = new ArrayList<String>();//需要导出的数据集合ArrayList<ArrayList<String>> allData = new ArrayList<ArrayList<String>>();JSONArray columnJsonArray = column;if(null != columnJsonArray){for(int i=0;i<columnJsonArray.size();i++){JSONObject obj = columnJsonArray.getJSONObject(i);if("false".equals(obj.getString("hidden"))){columnDbName.add(obj.getString("dataIndex"));columnName.add(obj.getString("text"));}}}allData.add(columnName);for(int j=0;j<data.size();j++){JSONObject dataJsonObject = data.getJSONObject(j);//每行数据内容ArrayList<String> rowData = new ArrayList<String>();for(int k=0;k<columnDbName.size();k++){if(null!=columnDbName.get(k)&&!"null".equals(dataJsonObject.getString(columnDbName.get(k)))){rowData.add(dataJsonObject.getString(columnDbName.get(k)));}else{rowData.add("");}}allData.add(rowData);}return allData;}private HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook){HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);return cellStyle;}}