当前位置:首页 > 开发 > 编程语言 > 编程 > 正文

ExcelUtil

发表于: 2011-06-09   作者:cpf1985   来源:转载   浏览:
摘要: import java.io.IOException; import java.net.URLEncoder; import java.util.Calendar; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apa
 
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Calendar;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;

public class ExcelUtil {

    private static String DATE_FORMAT = "yyyy-MM-dd"; // "YYYY-MM-DD" 定制日期格式
    private static String NUMBER_FORMAT = "#,##0.00";// 定制浮点数格式
    private  String xlsFilePath;// 文件名
    private String fileName = "export";
    private HSSFWorkbook workbook;// 工作薄
    private HSSFSheet sheet1;// 工作表
    private HSSFSheet sheet2;// 工作表
   // private HSSFRow row;// 行
   
    private String[] name1;
    private String[][] data1;
    private String[] name2;
    private String[][] data2;
    private HttpServletResponse response;

    public ExcelUtil(String[] name,String[][] data,HttpServletResponse response) {
        this.name1 = name;
        this.data1 = data;
        this.response = response;
        init();
    }
    public ExcelUtil(String[] name1,String[] name2,String[][] data1,String[][] data2,HttpServletResponse response) {
        this.name1 = name1;
        this.data1 = data1;
        this.name2 = name2;
        this.data2 = data2;
        this.response = response;
        init("订单","订单详情");
    }
    private void init(String sheetName1,String sheetName2){
        this.workbook = new HSSFWorkbook();
        this.sheet1 = workbook.createSheet(sheetName1);
        int rowNum1 = this.data1.length;
        int colNum1 = this.name1.length;
        
        this.sheet2 = workbook.createSheet(sheetName2);
        int rowNum2 = this.data2.length;
        int colNum2 = this.name2.length;
//        输出表头
        HSSFRow row1= createRow(0,sheet1);
        for (int i = 0; i < colNum1; i++) {
            setCell(i, this.name1[i],row1);
        }
//        输出数据
        for (int n = 0; n < rowNum1; n++) {
        	HSSFRow row2 =  createRow(n + 1,sheet1);
            for (int m = 0; m < colNum1; m++) {
                setCell(m, this.data1[n][m],row2);
            }
        }
        
        
        HSSFRow row3= createRow(0,sheet2);
        for (int i = 0; i < colNum2; i++) {
            setCell(i, this.name2[i],row3);
        }
//        输出数据
        for (int n = 0; n < rowNum2; n++) {
        	HSSFRow row4= createRow(n + 1,sheet2);
            for (int m = 0; m < colNum2; m++) {
            	if(data2[n]!=null && data2[n][m]!=null){
            		 setCell(m, this.data2[n][m],row4);
            	}
               
            }
        }
    }
    private void init(){
        this.workbook = new HSSFWorkbook();
        this.sheet1 = workbook.createSheet();
        int rowNum = this.data1.length;
        int colNum = this.name1.length;
//        输出表头
        HSSFRow row1=  createRow(0);
        for (int i = 0; i < colNum; i++) {
        	 setCell(i, this.name1[i],row1);
        }
//        输出数据
        for (int n = 0; n < rowNum; n++) {
        	 HSSFRow row2=   createRow(n + 1);
            for (int m = 0; m < colNum; m++) {
                setCell(m, this.data1[n][m],row2);
            }
        }
    }
    
//    输出流
    public void write() throws IOException{
        this.response.setContentType("application/vnd.ms-excel; charset=UTF-8");   
        this.response.setHeader("Content-Disposition", "filename="+URLEncoder.encode(this.fileName,"utf-8")+".xls");
        this.response.setHeader("Cache-Control", "no-cache");
        ServletOutputStream os = this.response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    }
    
    
    
    //增加一行
    public HSSFRow createRow(int index) {
    	HSSFRow row = this.sheet1.createRow(index);
    	return row;
    }
    public HSSFRow createRow(int index,HSSFSheet sheet) {
    	HSSFRow  row = sheet.createRow(index);
        return row;
     }

    //设置单元格,传入值为字符串的
    public void setCell(int index, String value,HSSFRow row) {
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(value);
     }
    

//    设置单元格,传入值为日期的
    public void setCell(int index, Calendar value,HSSFRow row) {
       HSSFCell cell = row.createCell(index);
       cell.setCellValue(value.getTime());
       HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
       cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
       cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
    }
 
   //设置单元格,传入数据为int型
    public void setCell(int index, int value,HSSFRow row) {
       HSSFCell cell = row.createCell(index);
       cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
       cell.setCellValue(value);
    }

    //设置单元格,传入值为double型
    public void setCell(int index, double value,HSSFRow row) {
       HSSFCell cell =  row.createCell(index);
       cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
       cell.setCellValue(value);
       HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
       HSSFDataFormat format = workbook.createDataFormat();
       cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
       cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
    }
    
    public String getXlsFilePath() {
        return xlsFilePath;
    }
    public void setXlsFilePath(String xlsFilePath) {
        this.xlsFilePath = xlsFilePath;
    }
    public String getFileName() {
        return fileName;
    }
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    
}
使用:
 String[] name1 = {"订单编号","买家会员名","收货人姓名","详细地址","买家留言"};
 String[] name2 = {"订单编号","标题","颜色","尺码","价格","购买数量","备注"};
 String[][] data = new String[list.size()][11];
 String[][] data2=new String[10][7];
 ExcelUtil eu =new ExcelUtil(name1,name2,data,data2,response);
 eu.write();

ExcelUtil

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号