JAVA公共导出Excel_公共POI导出Excel方法–java

packagecom.tl.util;importjava.io.IOException;importjava.io.OutputStream;importjava.net.URLEncoder;importjava.util.ArrayList;importjava.util.List;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFRichTextString;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.BorderStyle;importorg.apache.poi.ss.usermodel.CellType;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.VerticalAlignment;importorg.apache.poi.ss.util.CellRangeAddress;public classExportExcel {//显示的导出表的标题

privateString title;//导出表的列名

privateString[] rowName ;private List dataList = new ArrayList();

HttpServletResponse response;//构造方法，传入要导出的数据

public ExportExcel(String title,String[] rowName,ListdataList){this.dataList =dataList;this.rowName =rowName;this.title =title;

}/** 导出数据

**/

public void export(HttpServletResponse response) throwsException{try{

HSSFWorkbook workbook= new HSSFWorkbook(); //创建工作簿对象

HSSFSheet sheet = workbook.createSheet(title); //创建工作表//产生表格标题行

HSSFRow rowm = sheet.createRow(0);

HSSFCell cellTiltle= rowm.createCell(0);//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】

HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象

HSSFCellStyle style = this.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

HSSFCell cellRowName= rowRowName.createCell(n); //创建列头对应个数的单元格

cellRowName.setCellValue(CellType.STRING.getCode()); //设置列头单元格的数据类型

HSSFRichTextString text = newHSSFRichTextString(rowName[n]);

cellRowName.setCellValue(text);//设置列头单元格的值

cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式

}//将查询出的数据设置到sheet对应的单元格中

for(int i=0;i

Object[] obj= dataList.get(i);//遍历每个对象

HSSFRow row = sheet.createRow(i+3);//创建所需的行数

for(int j=0; j

HSSFCell cell= null; //设置单元格的数据类型

if(j == 0){

cell=row.createCell(j,CellType.NUMERIC);

cell.setCellValue(i+1);

}else{

cell=row.createCell(j,CellType.STRING);//if(!"".equals(obj[j]) && obj[j] != null){

cell.setCellValue(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);if (currentCell.getCellTypeEnum() ==CellType.STRING) {int length =currentCell.getStringCellValue().getBytes().length;if (columnWidth

columnWidth=length;

}

}

}

}if(colNum == 0){

sheet.setColumnWidth(colNum, (columnWidth-2) * 256);

}else{

sheet.setColumnWidth(colNum, (columnWidth+4) * 256);

}

}if(workbook !=null){try{

String fileName= title+".xls";

fileName= URLEncoder.encode(fileName, "UTF-8");//中文名称

String headStr = "attachment; filename=\"" + fileName + "\"";

response.setContentType("APPLICATION/OCTET-STREAM");

response.setHeader("Content-Disposition", headStr);

OutputStream out=response.getOutputStream();

workbook.write(out);

}catch(IOException e)

{

e.printStackTrace();

}

}

}catch(Exception e){

e.printStackTrace();

}

}/** 列头单元格样式*/

publicHSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {//设置字体

HSSFFont font =workbook.createFont();//设置字体大小

font.setFontHeightInPoints((short)12);//字体加粗

font.setBold(true);//设置字体名字

font.setFontName("Courier New");//设置样式;

HSSFCellStyle style =workbook.createCellStyle();//设置底边框;

style.setBorderBottom(BorderStyle.THIN);//设置底边框颜色;

style.setBottomBorderColor((short)8);//HSSFColor.BLACK.index(过时，替换成(short)8)下面都一样//设置左边框;

style.setBorderLeft(BorderStyle.THIN);//设置左边框颜色;

style.setLeftBorderColor((short)8);//设置右边框;

style.setBorderRight(BorderStyle.THIN);//设置右边框颜色;

style.setRightBorderColor((short)8);//设置顶边框;

style.setBorderTop(BorderStyle.THIN);//设置顶边框颜色;

style.setTopBorderColor((short)8);//在样式用应用设置的字体;

style.setFont(font);//设置自动换行;

style.setWrapText(false);//设置水平对齐的样式为居中对齐;

style.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;

style.setVerticalAlignment(VerticalAlignment.CENTER);returnstyle;

}/** 列数据信息单元格样式*/

publicHSSFCellStyle getStyle(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(BorderStyle.THIN);//设置底边框颜色;

style.setBottomBorderColor((short)8);//HSSFColor.BLACK.index(过时，替换成(short)8)//设置左边框;

style.setBorderLeft(BorderStyle.THIN);//设置左边框颜色;

style.setLeftBorderColor((short)8);//HSSFColor.BLACK.index(过时，替换成(short)8)//设置右边框;

style.setBorderRight(BorderStyle.THIN);//设置右边框颜色;

style.setRightBorderColor((short)8);//设置顶边框;

style.setBorderTop(BorderStyle.THIN);//设置顶边框颜色;

style.setTopBorderColor((short)8);//在样式用应用设置的字体;

style.setFont(font);//设置自动换行;

style.setWrapText(false);//设置水平对齐的样式为居中对齐;

style.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;

style.setVerticalAlignment(VerticalAlignment.CENTER);returnstyle;

}

}