POI 导出excel

有道笔记地址：http://note.youdao.com/share/?id=521dd834dba53cc4a599ad69f9842dbc&type=note

 

样式

package com.wondersgroup.qdyth.m01.common.service.impl;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

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.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.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.struts2.ServletActionContext;

import com.wondersgroup.qdyth.m01.common.dao.CommonDao;
import com.wondersgroup.qdyth.m01.common.model.bo.NetDataMain;
import com.wondersgroup.qdyth.m01.common.model.dto.CodeType;
import com.wondersgroup.qdyth.m01.common.model.vo.FileExportVO;
import com.wondersgroup.qdyth.m01.common.service.CommonExcelService;
import com.wondersgroup.uaas.LoginUtil;
import com.wondersgroup.wssip.commons.dao.CommonJdbcDaoUtils;


public class CommonExcelServiceImpl implements CommonExcelService{
    private CommonDao commonDao;
    public CommonDao getCommonDao() {
        return commonDao;
    }
    public void setCommonDao(CommonDao commonDao) {
        this.commonDao = commonDao;
    }
    public String exportExcel(String titlename, List<String[]> titles, List<NetDataMain> mains)
        throws IOException{
        String sql = "select aaa102,aaa103 from aa10web where aaa100=?";
        CodeType code1 =  CommonJdbcDaoUtils.getFirst(sql, CodeType.class, "DOWNPATH");
        HttpServletRequest request = ServletActionContext.getRequest();
        //int random = (int)(Math.random()*1000);
        /*SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String dates = sdf.format(new Date());*/
        if((mains==null||mains.size()==0)&&(titles==null||titles.size()==0))
            return null;
        
        String aab001 = LoginUtil.getLoginName(request);
        String writeName  = code1.getAaa103()+aab001+".xls";
        //FileInputStream fileInputStream = new FileInputStream(readName);
        //POIFSFileSystem fis = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        
        
        
        //表头
        sheet.addMergedRegion(new Region(0,(short)0,0,(short)(titles.size()-1)));
        HSSFRow noe = sheet.createRow(0);
        HSSFCell eon = noe.createCell((short)0);
        eon.setCellValue(new HSSFRichTextString(titlename));
        HSSFCellStyle styletitle = wb.createCellStyle();
        styletitle.setLeftBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styletitle.setRightBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styletitle.setBottomBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styletitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styletitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short)24);
        styletitle.setFont(font);
        eon.setCellStyle(styletitle);
        
        //标题栏样式
        HSSFCellStyle styleTitle = wb.createCellStyle();
        styleTitle.setLeftBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleTitle.setRightBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleTitle.setBottomBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleTitle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        //标题栏
        HSSFRow rowTtile = sheet.createRow(1);
        for(int j=0;j<titles.size();++j){
            HSSFCell cell = rowTtile.createCell((short)j);
            cell.setCellStyle(styleTitle);
            short width = 0;
            if(titles.get(j).length==2){
                width = (short)(Integer.parseInt(titles.get(j)[1])*100);
            }else{
                short w = (short)(titles.get(j)[0].getBytes().length*300);
                if(w>width){
                    sheet.setColumnWidth((short)j,w);
                    width = w;
                }
            }
            cell.setCellValue(new HSSFRichTextString(titles.get(j)[0]));
            sheet.setColumnWidth((short)j,width);
        }
        //设置样式
         HSSFDataFormat format = wb.createDataFormat();
        HSSFCellStyle styleCell1 = wb.createCellStyle();
        styleCell1.setLeftBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCell1.setRightBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCell1.setBottomBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCell1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCell1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleCell1.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        styleCell1.setDataFormat(format.getFormat("@"));
        HSSFCellStyle styleCell2 = wb.createCellStyle();
        styleCell2.setLeftBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCell2.setRightBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCell2.setBottomBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCell2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCell2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleCell2.setFillForegroundColor(HSSFColor.WHITE.index);
        styleCell2.setDataFormat(format.getFormat("@"));

        for(int i=0;i<mains.size();++i){
            HSSFRow row = sheet.createRow(i+2);
            //设置行高
            row.setHeight((short)270);
            NetDataMain main = mains.get(i);
            for(int j=0;j<titles.size();++j){
                HSSFCell cell = row.createCell((short)j);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if(i%2==0){
                    cell.setCellStyle(styleCell1);
                }else{
                    cell.setCellStyle(styleCell2);
                }
                if(j==0)
                    cell.setCellValue(new HSSFRichTextString(main.getData01()));
                else if(j==1)
                    cell.setCellValue(new HSSFRichTextString(main.getData02()));
                else if(j==2)
                    cell.setCellValue(new HSSFRichTextString(main.getData03()));
                else if(j==3)
                    cell.setCellValue(new HSSFRichTextString(main.getData04()));
                else if(j==4)
                    cell.setCellValue(new HSSFRichTextString(main.getData05()));
                else if(j==5)
                    cell.setCellValue(new HSSFRichTextString(main.getData06()));
                else if(j==6)
                    cell.setCellValue(new HSSFRichTextString(main.getData07()));
                else if(j==7)
                    cell.setCellValue(new HSSFRichTextString(main.getData08()));
                else if(j==8)
                    cell.setCellValue(new HSSFRichTextString(main.getData09()));
                else if(j==9)
                    cell.setCellValue(new HSSFRichTextString(main.getData10()));
                else if(j==10)
                    cell.setCellValue(new HSSFRichTextString(main.getData11()));
                else if(j==11)
                    cell.setCellValue(new HSSFRichTextString(main.getData12()));
                else if(j==12)
                    cell.setCellValue(new HSSFRichTextString(main.getData13()));
                else if(j==13)
                    cell.setCellValue(new HSSFRichTextString(main.getData14()));
                else if(j==14)
                    cell.setCellValue(new HSSFRichTextString(main.getData15()));
                else if(j==15)
                    cell.setCellValue(new HSSFRichTextString(main.getData16()));
                else if(j==16)
                    cell.setCellValue(new HSSFRichTextString(main.getData17()));
                else if(j==17)
                    cell.setCellValue(new HSSFRichTextString(main.getData18()));
                else if(j==18)
                    cell.setCellValue(new HSSFRichTextString(main.getData19()));
                else if(j==19)
                    cell.setCellValue(new HSSFRichTextString(main.getData20()));
                else if(j==20)
                    cell.setCellValue(new HSSFRichTextString(main.getData21()));
                else if(j==21)
                    cell.setCellValue(new HSSFRichTextString(main.getData22()));
                else if(j==22)
                    cell.setCellValue(new HSSFRichTextString(main.getData23()));
                else if(j==23)
                    cell.setCellValue(new HSSFRichTextString(main.getData24()));
                else if(j==24)
                    cell.setCellValue(new HSSFRichTextString(main.getData25()));
                else if(j==25)
                    cell.setCellValue(new HSSFRichTextString(main.getData26()));
                else if(j==26)
                    cell.setCellValue(new HSSFRichTextString(main.getData27()));
                else if(j==27)
                    cell.setCellValue(new HSSFRichTextString(main.getData28()));
                else if(j==28)
                    cell.setCellValue(new HSSFRichTextString(main.getData29()));
                else if(j==29)
                    cell.setCellValue(new HSSFRichTextString(main.getData30()));
                //String ss = cell.getRichStringCellValue().getString();
                //设置列宽
                /*if(ss.length()>0){
                    short w = (short)(ss.getBytes().length*300);;
                    if(w>width){
                        sheet.setColumnWidth((short)j,w);
                        width = w;
                    }
                }*/
            }
        }
        File file =new File(code1.getAaa103());
        file.mkdirs();
        FileOutputStream fos = new FileOutputStream(writeName);
        wb.write(fos);
        fos.close();
        //fileInputStream.close();
        return writeName;
    }
    public void exportExcel(String sql, List<String[]> titles,FileExportVO exportVO){
        try {
            HttpServletRequest request = ServletActionContext.getRequest();
            //String aab001 = LoginUtil.getLoginName(request);
            String filepath=exportExcel(exportVO.getFilename(), titles, commonDao.getExportData(sql));
            exportVO.setFilepath(filepath);
            //exportVO.setFilename(exportVO.getFilename()+".xls");new String (s.getbytes(‘GBK’),'iso8859-1')
           exportVO.setFilename(new String((exportVO.getFilename()+".xls").getBytes("GBK"),"ISO8859-1"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 

导出demo

package com.common.excel.inport;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

public class export {
    static List<beans> list;
    
    public export() {
        list=new ArrayList<beans>();
        beans b;
        for(int i=0;i<10;i++){
            b=new beans();
            b.setAge("23");
            b.setBirthday("199012232610");
            b.setName("吴国志");
            list.add(b);
        }

    }
    public static void exprot(){
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        
        List<String[]> titles =new ArrayList<String[]>();
        String[] ss={"姓名","年龄","出生日期"};
        titles.add(new String[]{"姓名"});
        titles.add(new String[]{"年龄"});
        titles.add(new String[]{"出生日期"});
        
        //表头
        sheet.addMergedRegion(new Region(0,(short)0,0,(short)(ss.length-1)));
        HSSFRow noe = sheet.createRow(0);
        HSSFCell eon = noe.createCell((short)0);
        eon.setCellValue(new HSSFRichTextString("导出标题"));
        HSSFCellStyle styletitle = wb.createCellStyle();
        styletitle.setLeftBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styletitle.setRightBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styletitle.setBottomBorderColor(HSSFColor.BLACK.index);
        styletitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styletitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styletitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short)24);
        styletitle.setFont(font);
        eon.setCellStyle(styletitle);
        
        //标题栏样式
        HSSFCellStyle styleTitle = wb.createCellStyle();
        styleTitle.setLeftBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleTitle.setRightBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleTitle.setBottomBorderColor(HSSFColor.BLACK.index);
        styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleTitle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        //标题栏
        HSSFRow rowTtile = sheet.createRow(1);
        for(int j=0;j<titles.size();++j){
            HSSFCell cell = rowTtile.createCell((short)j);
            cell.setCellStyle(styleTitle);
            short width = 0;
            if(titles.get(j).length==2){
                width = (short)(Integer.parseInt(titles.get(j)[1])*100);
            }else{
                short w = (short)(titles.get(j)[0].getBytes().length*300);
                if(w>width){
                    sheet.setColumnWidth((short)j,w);
                    width = w;
                }
            }
            cell.setCellValue(new HSSFRichTextString(titles.get(j)[0]));
            sheet.setColumnWidth((short)j,width);
        }
        //设置样式
        HSSFDataFormat format = wb.createDataFormat();
        HSSFCellStyle styleCell1 = wb.createCellStyle();
        styleCell1.setLeftBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCell1.setRightBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCell1.setBottomBorderColor(HSSFColor.BLACK.index);
        styleCell1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCell1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCell1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleCell1.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        styleCell1.setDataFormat(format.getFormat("@"));
        HSSFCellStyle styleCell2 = wb.createCellStyle();
        styleCell2.setLeftBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleCell2.setRightBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleCell2.setBottomBorderColor(HSSFColor.BLACK.index);
        styleCell2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleCell2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleCell2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleCell2.setFillForegroundColor(HSSFColor.WHITE.index);
        styleCell2.setDataFormat(format.getFormat("@"));

        for(int i=0;i<list.size();++i){
            HSSFRow row = sheet.createRow(i+2);
            //设置行高
            row.setHeight((short)270);
            beans main = list.get(i);
            for(int j=0;j<titles.size();++j){
                HSSFCell cell = row.createCell((short)j);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if(i%2==0){
                    cell.setCellStyle(styleCell1);
                }else{
                    cell.setCellStyle(styleCell2);
                }
                if(j==0)
                    cell.setCellValue(new HSSFRichTextString(main.getName()));
                else if(j==1)
                    cell.setCellValue(new HSSFRichTextString(main.getAge()));
                else if(j==2)
                    cell.setCellValue(new HSSFRichTextString(main.getBirthday()));
            
                //String ss = cell.getRichStringCellValue().getString();
                //设置列宽
                /*if(ss.length()>0){
                    short w = (short)(ss.getBytes().length*300);;
                    if(w>width){
                        sheet.setColumnWidth((short)j,w);
                        width = w;
                    }
                }*/
            }
        }
        File file =new File("c:/print/temp");
        file.mkdirs();
        FileOutputStream fos;
        try {
            fos = new FileOutputStream("c:/print/temp/测试.xls");
            wb.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            
            e.printStackTrace();
        } catch (IOException e) {
            
            e.printStackTrace();
        }
        
        System.out.println("ok");
    }
    public static void main(String[] args) {
        export e=new export();
        e.exprot();
    }
}

test测试

package com.common.excel.inport;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class importtest {
public static void main(String[] args) throws BiffException, IOException {
    File newFile = new File("d:\\123.xls");
    Workbook book = Workbook.getWorkbook(newFile) ;
    //取得第一个sheet
    Sheet sheet = book.getSheet(0);
    //取得行数
    int rows = sheet.getRows();
    System.out.println(rows);
    for(int i=0;i<rows;i++){
        Cell [] cell = sheet.getRow(i);
        System.out.println(cell.length);
        for(int j=0; j<4; j++) {
            System.out.println(sheet.getCell(j, i).getContents());
        }
    }
}

}

 

posted @ 2013-08-28 23:01  小伙、不一般  阅读(2596)  评论(0)  编辑  收藏  举报