mg4377娱乐娱城官网_mg4377娱乐手机版_www.mg4377.com

Java项目导出excel,本人写的java

时间:2019-06-06 17:18来源:mg4377娱乐手机版
web开垦中,叁个连串的一般性必要也囊括导出excel,一般接纳POI做总括报表导出excel。 连串框架:SpringMVC Spring Mybatis Java项目导出excel,本人写的java。权限框架:Shiro ①、创设对应的fo

web开垦中,叁个连串的一般性必要也囊括导出excel,一般接纳POI做总括报表导出excel。

连串框架:SpringMVC Spring Mybatis
Java项目导出excel,本人写的java。权限框架:Shiro
①、创设对应的formbean 不是model,里面属性为急需导出的属性,这里作者只导出编码和称号

web开拓中,二个系统的普通要求也富含导出excel,一般採用POI做总括报表导出excel。

近年项目要用到excel导出效果,在此以前也写过类似的代码。因为此番项目中反复用到excel导出。此次长了纪念力整理了一下 分享给大伙

导出excel工具类:

public class StoreBillInfoForm extends BaseForm {
    private static final long serialVersionUID = 1L;

    private String store_no;                                     编码
    private String store_name;                                   名称

    public String getStore_no() {
        return store_no;
    }
    public void setStore_no(String store_no) {
        this.store_no = store_no;
    }
    public String getStore_name() {
        return store_name;
    }
    public void setStore_name(String store_name) {
        this.store_name = store_name;
    }
}

导出excel工具类:

应接一同商量

    import java.io.FileOutputStream;  
    import java.io.IOException;  
    import java.io.OutputStream;  
    import java.util.List;  
    import java.util.Map;  

    import org.apache.poi.hssf.usermodel.HSSFCell;  
    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;  

    public class ExportExcel {  

        private ExportExcel() {  
            super();  
        }  

        public static void exportExcel(List<Object> list, Map<Integer, Long> map,  
                String[] titles) throws IOException {  
            // 创建Excel文档  
            HSSFWorkbook hwb = new HSSFWorkbook();  
            // sheet 对应一个工作页  
            HSSFSheet sheet = hwb.createSheet("exportReport");  
            int colNum = titles.length;  
            // 创建第一行  
            HSSFRow firstrow = sheet.createRow(0);  
            HSSFCell[] firstcell = new HSSFCell[colNum];  
            for (int col = 0; col < colNum; col  ) {  
                firstcell[col] = firstrow.createCell(col);  
                firstcell[col].setCellValue(new HSSFRichTextString(titles[col]));  
            }  

            // 插入记录  
            int rowNum = map.size();  
            for (int i = 0; i < rowNum; i  ) {  
                // 从第二行开始  
                HSSFRow row = sheet.createRow(i   1);  
                // 插入list中的字段  
                for (int col = 0; col < colNum - 2; col  ) {  
                    HSSFCell cell = row.createCell(col);  
                    cell.setCellValue(list.get(col).toString());  
                }  
                // 插入月份或日期  
                row.createCell(colNum - 2).setCellValue(i   1);  
                // 插入总量  
                row.createCell(colNum - 1).setCellValue(map.get(i   1));  
            }  
            String fileName = titles[1].substring(0, 2);  
            if (colNum == 4) {  
                fileName  = list.get(0)   "_"   list.get(1)   "年_年度报表";  
            } else if (colNum == 5) {  
                fileName  = list.get(0)   "_"   list.get(1)   "年"   list.get(2)  
                          "月_月度报表";  
            }  
            // 创建文件输出流,准备输出电子表格  
            OutputStream out = new FileOutputStream("../webapps/UsedMallMinaServer/files/"  
                      fileName   ".xls");  
            hwb.write(out);  
            out.close();  
        }  
    }  

2、点击导出开关,跳转到action对应的点子
findShopBillInfoByCondition方法要在service实习 和 在对应的xml里面写,就要下边写上。

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
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;

public class ExportExcel {

    private ExportExcel() {
        super();
    }

    public static void exportExcel(List<Object> list, Map<Integer, Long> map,
            String[] titles) throws IOException {
        // 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        // sheet 相应一个工作页
        HSSFSheet sheet = hwb.createSheet("exportReport");
        int colNum = titles.length;
        // 创建第一行
        HSSFRow firstrow = sheet.createRow(0);
        HSSFCell[] firstcell = new HSSFCell[colNum];
        for (int col = 0; col < colNum; col  ) {
            firstcell[col] = firstrow.createCell(col);
            firstcell[col].setCellValue(new HSSFRichTextString(titles[col]));
        }

        // 插入记录
        int rowNum = map.size();
        for (int i = 0; i < rowNum; i  ) {
            // 从第二行開始
            HSSFRow row = sheet.createRow(i   1);
            // 插入list中的字段
            for (int col = 0; col < colNum - 2; col  ) {
                HSSFCell cell = row.createCell(col);
                cell.setCellValue(list.get(col).toString());
            }
            // 插入月份或日期
            row.createCell(colNum - 2).setCellValue(i   1);
            // 插入总量
            row.createCell(colNum - 1).setCellValue(map.get(i   1));
        }
        String fileName = titles[1].substring(0, 2);
        if (colNum == 4) {
            fileName  = list.get(0)   "_"   list.get(1)   "年_年度报表";
        } else if (colNum == 5) {
            fileName  = list.get(0)   "_"   list.get(1)   "年"   list.get(2)
                      "月_月度报表";
        }
        // 创建文件输出流。准备输出电子表格
        OutputStream out = new FileOutputStream("../webapps/UsedMallMinaServer/files/"
                  fileName   ".xls");
        hwb.write(out);
        out.close();
    }
}

生成excel的主工具类:

@Controller 
public class StoreBillInfoAction extends BaseManageAction {
    private final String FILE_PATH = this.getClass().getClassLoader().getResource("").getPath();

    @RequestMapping(value="/control/store/billinfo/export")
    public void export(StoreBillInfoForm formbean, HttpServletResponse response) throws IOException{
        Condition condition = new Condition();

        QueryResult<StoreBillInfo> queryresult = to(StoreBillInfoService.class).findShopBillInfoByCondition(condition);
         String fileName = "供应商编码名称表("   DateTimeUtils.fullStrFormat()   ").xls";            //导出excel文件名称
         List<StoreBillInfo> list = queryresult.getResultList();
         if(list != null && !list.isEmpty()){
             BillSheet.billInfoExport(FILE_PATH, list, formbean, fileName, response);
         }
    }
}
public class ExcelFactory<T> {
    private static final String EXCEL_FONT_FAMILY_SETTING = "Monaco"; //设置字体
    private static final int EXCEL_FONT_SIZE_SETTING = 12;  //设置字体大小

    private WritableWorkbook writableWorkbook = null;
    private WritableSheet writableSheet = null;

    public WritableWorkbook createExcel(OutputStream os, Excel excel,
                                        List<String> header, List<T> datas, ExcelMapper<T> mapper)
            throws RowsExceededException, WriteException { //生成excel
        try {
            writableWorkbook = Workbook.createWorkbook(os);
            writableSheet = writableWorkbook.createSheet(excel.getSheetName(),
                    excel.getSheetNum());
            SheetSettings settings = writableSheet.getSettings();
            settings.setVerticalFreeze(1);

            // Write the title
            if (header != null && header.size() > 0) {
                for (int i = 0; i < header.size(); i  ) {
                    writableSheet.addCell(new Label(i, 0, header.get(i),
                            setHeaderCellStyle(new CellStyle(EXCEL_FONT_FAMILY_SETTING, EXCEL_FONT_SIZE_SETTING))));
                }
            }

            // Write data to file
            if (datas != null && datas.size() > 0) {
                for (int i = 1; i <= datas.size(); i  ) {
                    mapper.mapToExcel(writableSheet, datas.get(i - 1),
                            setBodyCellStyle(new CellStyle(EXCEL_FONT_FAMILY_SETTING, EXCEL_FONT_SIZE_SETTING)), i);
                }
            }
        } catch (IOException e) {
            return null;
        }

        return writableWorkbook;
    }

    private WritableCellFormat setHeaderCellStyle(CellStyle style) throws WriteException {
        WritableFont font = new WritableFont(
                WritableFont.createFont(style.getFont()), style.getFontSize(), WritableFont.BOLD);
        WritableCellFormat cellFormat = setCentre(style, font);

        return cellFormat;
    }

    private WritableCellFormat setBodyCellStyle(CellStyle style) throws WriteException {
        WritableFont font = new WritableFont(
                WritableFont.createFont(style.getFont()), style.getFontSize(), WritableFont.NO_BOLD);
        WritableCellFormat cellFormat = setCentre(style, font);

        return cellFormat;
    }

    private WritableCellFormat setCentre(CellStyle style, WritableFont font)
            throws WriteException {
        WritableCellFormat cellFormat = new WritableCellFormat(font);
        cellFormat.setBackground(style.getBgColor());
        cellFormat.setAlignment(Alignment.CENTRE);
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        return cellFormat;
    }
}

编辑:mg4377娱乐手机版 本文来源:Java项目导出excel,本人写的java

关键词: java Code excel导出