1、首先需要POI的jar包,POI是Apache下属的开源项目,到官网上http://poi.apache.org/下载jar包即可,注意如果使用的office2007之后的xlsx格式的文档,需要导入poi-ooxml的jar包
2、下面是我实现的一个具体Excel导出
/**
**
*/
package com.inspur.poi;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author liuwp
* @date 2015年9月10日
*/
public class ExcelOutput {
//设计单元格样式
private static CellStyle headStyle1(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setBorderTop(CellStyle.BORDER_MEDIUM);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
private static CellStyle headStyle2(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
private static CellStyle leftStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.LIME.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
private static CellStyle leftTotal(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short)11);
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
private static CellStyle dateCell(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
return style;
}
private static CellStyle dateBottomCell(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_MEDIUM);
return style;
}
private static CellStyle dateRightCell(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_MEDIUM);
return style;
}
public static void main(String[] args){
//输出的Excel表格是2007版的,格式为xlsx
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("领域组");
//冻结窗口
sheet.createFreezePane(1, 0, 1, 0);
//列宽设定
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(13, 3000);
sheet.setColumnWidth(14, 2500);
sheet.setColumnWidth(15, 3000);
sheet.setColumnWidth(16, 3000);
sheet.setColumnWidth(17, 2500);
//标题的单元格合并
CellRangeAddress region1 = CellRangeAddress.valueOf("A4:A5");
CellRangeAddress region2 = CellRangeAddress.valueOf("B4:B6");
CellRangeAddress region3 = CellRangeAddress.valueOf("C4:D5");
CellRangeAddress region4 = CellRangeAddress.valueOf("E4:F5");
CellRangeAddress region5 = CellRangeAddress.valueOf("G4:H5");
CellRangeAddress region6 = CellRangeAddress.valueOf("I4:J5");
CellRangeAddress region7 = CellRangeAddress.valueOf("K4:M5");
CellRangeAddress region8 = CellRangeAddress.valueOf("N4:N6");
CellRangeAddress region9 = CellRangeAddress.valueOf("O4:O6");
CellRangeAddress region10 = CellRangeAddress.valueOf("P4:P6");
CellRangeAddress region11 = CellRangeAddress.valueOf("Q4:Q6");
CellRangeAddress region12 = CellRangeAddress.valueOf("R4:R6");
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
sheet.addMergedRegion(region6);
sheet.addMergedRegion(region7);
sheet.addMergedRegion(region8);
sheet.addMergedRegion(region9);
sheet.addMergedRegion(region10);
sheet.addMergedRegion(region11);
sheet.addMergedRegion(region12);
//获取行属性
Row row4 = sheet.createRow(3);
Row row5 = sheet.createRow(4);
Row row6 = sheet.createRow(5);
//标题第一行内容
String date = "7.1-7.31";
String[] head1 = {date,"平均每包缺陷数(提包审查)","平均每包缺陷数(测试)","","缺陷修复占比(测试)","","平均每包严重缺陷数(测试)","","平均每包缺陷数(项目)","","包数","","","缺陷数(提包审查)","缺陷数(测试)","缺陷修改数(测试)","严重缺陷数(测试)","缺陷数(项目)"};
String[] head2 = {"领域组","","季度目标","实际达成","季度目标","实际达成","季度目标","实际达成","本期","年累","免测包数","测试包数","合计","","","","",""};
String[] left = {"3M","GIS配送","仓储管控","订单中心","分销","管控产品","管控项目","技术架构部","零售终端","网络营销","移动应用","专卖内管","合计"};
//表头样式、内容
for(int i=0;i<18;i++){
Cell cell4 = row4.createCell((short) i);
cell4.setCellValue(head1[i]);
cell4.setCellStyle(headStyle1(wb));
Cell cell5 = row5.createCell(i);
cell5.setCellStyle(headStyle2(wb));
Cell cell6 = row6.createCell(i);
cell6.setCellValue(head2[i]);
cell6.setCellStyle(headStyle2(wb));
}
//左侧栏目
Row row7 = sheet.createRow(6);
Row row8 = sheet.createRow(7);
Row row9 = sheet.createRow(8);
Row row10 = sheet.createRow(9);
Row row11 = sheet.createRow(10);
Row row12 = sheet.createRow(11);
Row row13 = sheet.createRow(12);
Row row14 = sheet.createRow(13);
Row row15 = sheet.createRow(14);
Row row16 = sheet.createRow(15);
Row row17 = sheet.createRow(16);
Row row18 = sheet.createRow(17);
Row row19 = sheet.createRow(18);
//左边栏
Cell cell70 = row7.createCell(0);
Cell cell80 = row8.createCell(0);
Cell cell90 = row9.createCell(0);
Cell cell100 = row10.createCell(0);
Cell cell110 = row11.createCell(0);
Cell cell120 = row12.createCell(0);
Cell cell130 = row13.createCell(0);
Cell cell140 = row14.createCell(0);
Cell cell150 = row15.createCell(0);
Cell cell160 = row16.createCell(0);
Cell cell170 = row17.createCell(0);
Cell cell180 = row18.createCell(0);
Cell cell190 = row19.createCell(0);
cell70.setCellValue(left[0]);
cell70.setCellStyle(leftStyle(wb));
cell80.setCellValue(left[1]);
cell80.setCellStyle(leftStyle(wb));
cell90.setCellValue(left[2]);
cell90.setCellStyle(leftStyle(wb));
cell100.setCellValue(left[3]);
cell100.setCellStyle(leftStyle(wb));
cell110.setCellValue(left[4]);
cell110.setCellStyle(leftStyle(wb));
cell120.setCellValue(left[5]);
cell120.setCellStyle(leftStyle(wb));
cell130.setCellValue(left[6]);
cell130.setCellStyle(leftStyle(wb));
cell140.setCellValue(left[7]);
cell140.setCellStyle(leftStyle(wb));
cell150.setCellValue(left[8]);
cell150.setCellStyle(leftStyle(wb));
cell160.setCellValue(left[9]);
cell160.setCellStyle(leftStyle(wb));
cell170.setCellValue(left[10]);
cell170.setCellStyle(leftStyle(wb));
cell180.setCellValue(left[11]);
cell180.setCellStyle(leftStyle(wb));
cell190.setCellValue(left[12]);
cell190.setCellStyle(leftTotal(wb));
// row19.createCell(0).setCellValue(left[12]);
// row19.createCell(0).setCellStyle(leftStyle(wb));
for(int j=1;j<18;j++){
Cell cell0 = row7.createCell(j);
cell0.setCellValue(1);
//cell.setCellStyle(leftStyle(wb));
Cell cell1 = row8.createCell(j);
cell1.setCellValue(2);
Cell cell2 = row9.createCell(j);
cell2.setCellValue(3);
}
//单元格样式
// for(int i=0;i<13;i++){
// for(int j=0;j<17;j++){
// Cell cell = sheet.createRow(i+6).createCell(j+1);
// cell.setCellStyle(dateCell(wb));
// }
// }
// for(int i=0;i<17;i++){
// Cell cell = sheet.createRow(18).createCell(i+1);
// cell.setCellStyle(dateBottomCell(wb));
// }
// for(int i=0;i<13;i++){
// Cell cell = sheet.createRow(i+6).createCell(17);
// cell.setCellStyle(dateRightCell(wb));
// }
//左侧合计
//
//
try {
//输出文件
FileOutputStream fout = new FileOutputStream(new File("d:\\exceloutput.xlsx"));
wb.write(fout);
fout.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
}
}