每天进步一点点!
分享一个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 excelTitle excel标题名称
*
*/
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 excelTitle excel标题名称
*
*/
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;
}
}