poi 导出Excel 2003 和 2007 版本

package utils;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiExportExcel {



public static void PoiWriteExcel_To2007(String title, String[][] head,String[][]body, OutputStream os){

XSSFWorkbook xssf_w_book=new XSSFWorkbook();
XSSFSheet xssf_w_sheet=xssf_w_book.createSheet(title);
XSSFRow xssf_w_row=null;//创建一行
XSSFCell xssf_w_cell=null;//创建每个单元格
int col_count = 0;
int row_count = 0;
row_count = body.length;
col_count = head.length;


XSSFCellStyle head_cellStyle=xssf_w_book.createCellStyle();//创建一个单元格样式
XSSFFont head_font=xssf_w_book.createFont();
head_font.setFontName("宋体");//设置头部字体为宋体
head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
head_font.setFontHeightInPoints((short) 12);
head_cellStyle.setFont(head_font);//单元格样式使用字体





int titleRows = 0; //标题占据的行数
xssf_w_row=xssf_w_sheet.createRow(0+titleRows);//第一行写入标题行
for(int i = 0; i < col_count; i++){
xssf_w_cell = xssf_w_row.createCell((short)i);

String type=head[i][2];

if(type==null){
type="";
}
else if(type.equals("DATE")){
System.out.println("走格式化日期这步了");
XSSFDataFormat head_format=xssf_w_book.createDataFormat();
head_cellStyle.setDataFormat(head_format.getFormat("m/d/yy"));
xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
XSSFRichTextString xssfString =new XSSFRichTextString(head[i][0]);
xssf_w_cell.setCellValue(xssfString);
xssf_w_cell.setCellStyle(head_cellStyle);
xssf_w_sheet.autoSizeColumn((short)i);
}else{
XSSFDataFormat head_format=xssf_w_book.createDataFormat();
head_cellStyle.setDataFormat(head_format.getFormat("@"));
xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
XSSFRichTextString xssfString =new XSSFRichTextString(head[i][0]);
xssf_w_cell.setCellValue(xssfString);
xssf_w_cell.setCellStyle(head_cellStyle);
xssf_w_sheet.autoSizeColumn((short)i);
}
}




XSSFCellStyle cellStyle_CN=xssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式)

cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN);



for(int i = 1; i <= row_count; i++){
xssf_w_row = xssf_w_sheet.createRow(i+titleRows);

for(int j = 0; j < col_count; j++){
String val=head[j][1];
xssf_w_cell = xssf_w_row.createCell((short)j);
if(val==null || val.trim().equals(""))
val="0";

String type=head[j][2];

if(type==null)
type="";
//判断类型为数字类型
else if(type.equals("NUM")){
xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String value=body[i-1][Integer.parseInt(val)];
if(value==null || value.trim().equals(""))
value="0";
xssf_w_cell.setCellValue(Double.parseDouble(value));
}
//判断类型为日期类型
else if(type.equals("DATE")){
xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);//设置单元格为字符串类型
XSSFDataFormat format=xssf_w_book.createDataFormat();
cellStyle_CN.setDataFormat(format.getFormat("yyyy/MM/dd"));
String value=body[i-1][Integer.parseInt(val)];
XSSFRichTextString xssfString =new XSSFRichTextString(value);
xssf_w_cell.setCellStyle(cellStyle_CN);
xssf_w_cell.setCellValue(xssfString);

}else{
xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);//设置单元格为字符串类型

String value=body[i-1][Integer.parseInt(val)];
if(value==null )
value="";
XSSFDataFormat format=xssf_w_book.createDataFormat();
cellStyle_CN.setDataFormat(format.getFormat("@"));
xssf_w_cell.setCellStyle(cellStyle_CN);
XSSFRichTextString xssfString =new XSSFRichTextString(value);
xssf_w_cell.setCellValue(xssfString);
}


}

}



try{
xssf_w_book.write(os);
os.flush();
os.close();

}
catch(Exception e){
e.printStackTrace();
}

}


public static void PoiWriteExcel_To2003(String title, String[][] head, String[][] body, OutputStream os){
HSSFWorkbook hssf_w_wk = new HSSFWorkbook();//创建excel
HSSFSheet hssf_w_s = hssf_w_wk.createSheet(title);//创建一个工作空间
HSSFRow hssf_w_r = null;//创建一行
HSSFCell hssf_w_c = null;//每个单元格
//填写内容
int col_count = 0;
int row_count = 0;
row_count = body.length;
col_count = head.length;

HSSFCellStyle cellStyle = hssf_w_wk.createCellStyle();
HSSFFont font = hssf_w_wk.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 10);
font.setFontName("宋体");//设置标题字体
cellStyle.setFont(font);

//标题
int titleRows = 0; //标题占据的行数
hssf_w_r = hssf_w_s.createRow(0);
//第一行写入标题行
hssf_w_r = hssf_w_s.createRow(0+titleRows);

cellStyle = hssf_w_wk.createCellStyle();
cellStyle.setFont(font);//设置列标题样式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

for(int i = 0; i < col_count; i++){
hssf_w_c = hssf_w_r.createCell((short)i);
String type=head[i][2];
System.out.println("类型为"+type);
if(type==null){
type="";
}
else if(type.equals("DATE")){
HSSFDataFormat format1= hssf_w_wk.createDataFormat();
cellStyle.setDataFormat(format1.getFormat("m/d/yy"));
hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString hssfString = new HSSFRichTextString(head[i][0]);
hssf_w_c.setCellValue(hssfString);
hssf_w_c.setCellStyle(cellStyle);
hssf_w_s.autoSizeColumn((short)i);
}else{
HSSFDataFormat format = hssf_w_wk.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString hssfString = new HSSFRichTextString(head[i][0]);
hssf_w_c.setCellValue(hssfString);
hssf_w_c.setCellStyle(cellStyle);
hssf_w_s.autoSizeColumn((short)i);
}
}
//第二行开始写入数据

cellStyle = hssf_w_wk.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

for(int i = 1; i <= row_count; i++){
hssf_w_r = hssf_w_s.createRow(i+titleRows);

for(int j = 0; j < col_count; j++){
String val=head[j][1];
hssf_w_c = hssf_w_r.createCell((short)j);
if(val==null || val.trim().equals(""))
val="0";

String type=head[j][2];

if(type==null)
type="";
//判断类型为数字类型
else if(type.equals("NUM")){
hssf_w_c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
String value=body[i-1][Integer.parseInt(val)];
if(value==null || value.trim().equals(""))
value="0";
hssf_w_c.setCellValue(Double.parseDouble(value));
}
//判断类型为日期类型
else if(type.equals("DATE")){
HSSFDataFormat format1= hssf_w_wk.createDataFormat();
cellStyle.setDataFormat(format1.getFormat("yyyy/m/d/"));
String value=body[i-1][Integer.parseInt(val)];
HSSFRichTextString hssfString = new HSSFRichTextString(value);
hssf_w_c.setCellStyle(cellStyle);
hssf_w_c.setCellValue(hssfString);
}else{
hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
String value=body[i-1][Integer.parseInt(val)];
if(value==null )
value="";
HSSFDataFormat format2 = hssf_w_wk.createDataFormat();
cellStyle.setDataFormat(format2.getFormat("@"));
hssf_w_c.setCellStyle(cellStyle);
HSSFRichTextString hssfString = new HSSFRichTextString(value);
hssf_w_c.setCellValue(hssfString);
}
}
}
try{
hssf_w_wk.write(os);
os.flush();
os.close();

}
catch(Exception e){
e.printStackTrace();
}

}

}