POI大数据Excel生成

package com.hd.erpreport.controller;

import java.io.File;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import com.hd.erpreport.utils.ExportDownLoadUtil;

@Controller

@RequestMapping("SalesExportHJController/")

public class SalesExportHJController {

    @RequestMapping("exportSales.do")

    public void exportSales(HttpServletRequest req,HttpServletResponse res){

        String begin = req.getParameter("begin");//2018-08-01

        String end = req.getParameter("end");

//        System.out.println("begin = " + begin);

//        System.out.println("end = " + end);

        //得到两个日期之间总的月数

//        int totalMonth = (getYearAndMonth(end)-getYearAndMonth(begin))/100 * 12 + (getYearAndMonth(end)-getYearAndMonth(begin)) % 100 ;

        int beginYear = getYearFourI(begin);//开始的年

        int endYear = getYearFourI(end);//结束的年

        int beginMonth = getMonthI(begin);

        int endMonth = getMonthI(end);

        String[] titles = { "销售订单号", "订单日期", "订单项次", "销售物料号", "品名", "规格型号",

                "关联品号", "销售数量", "交货日", "采购/生产单号", "采购单项次","工作中心", "物料号",

                "物料品名", "规格型号", "件号", "采购/生产数量", "已领料数量", "已完成数量", "计划开始日期",

                "实际开始日期", "计划完成日期","实际完成日期", "延期开始天数", "延期完成天数", "状态",

                "库存数量", "序列号" };

        String[] resultTitles = { "DDH","DDRQ","DDXC","XSWLH","XSWLPM","XSWLGG","XSWLJH","XSSL","JHR","CGGDDH","CGXC","GZZX",

                "WLH","WLPM","GGXH","JH","CGSCSL","YWCSL","JHKSRQ","JHWCRQ","SJKSRQ","SJWCRQ","YQKSTS","YQWCTS",

                "YLLSL","ZT","KCSL","XLHHZ"};

        // 连接数据库

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet result = null;

        FileOutputStream fout = null;

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载驱动，不同的数据库不同

            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.2:1521:topprod", "hdzsq", "hdzsq");// 建立连接

            conn.setAutoCommit(false);// 禁止自动提交，设置回滚点

            // 1、生成一个包含相应月数的工作簿

            String sheetName;

//            int month = getMonthI();

            SXSSFWorkbook  wb = new SXSSFWorkbook (1024);// 创建工作簿

            for (int k = beginYear; k <= endYear; k++) {

                while(true){

                    //如果不到12

                    if (k >= endYear && beginMonth > endMonth) {

                        break;

                    }

                    sheetName = getSheetName(k,beginMonth);// 2018年1月-2018年11月

                    Sheet sheet = wb.createSheet(sheetName);// 创建mouth个sheet并返回

                    // sheet写对应的内容

                    Row row = sheet.createRow(0);// 创建第一行

                    for (int j = 0; j < 28; j++) {

                        row.createCell(j).setCellValue(titles[j]);

                    }

                    String sql_sel = "select * from ddzt_v where ddh like 'SOD1-HD" + k%100 + StringUtils.leftPad(beginMonth+"", 2,"0") + "%'";

                    ps = conn.prepareStatement(sql_sel);

                    result = ps.executeQuery();

                    int l = 1;

                    while (result.next()) {

                        // 把得到的数据写到Excel中

                        row = sheet.createRow(l++);

                        for (int j = 0; j < 28; j++) {

                            row.createCell(j).setCellValue(result.getString(resultTitles[j]));

                        }

                    }

                    //如果处理的月份是12月，跳出当前循环，外层从1开始循环，否则月份加1

                    if (beginMonth % 12 == 0) {

                        beginMonth = 1;

                        break;

                    }

                    beginMonth++ ;

                }

            }

            /*for (int i = 1; i <= month; i++) {

                sheetName = getSheetName(i);// 2018年1月-2018年11月

                Sheet sheet = wb.createSheet(sheetName);// 创建mouth个sheet并返回

                // sheet写对应的内容

                Row row = sheet.createRow(0);// 创建第一行

                for (int j = 0; j < 28; j++) {

                    row.createCell(j).setCellValue(titles[j]);

                }

                String sql_sel = "select * from ddzt_v where ddh like 'SOD1-HD18" + StringUtils.leftPad(i+"", 2,"0") + "%'";

                ps = conn.prepareStatement(sql_sel);

                result = ps.executeQuery();

                int k = 1;

                while (result.next()) {

                    // 把得到的数据写到Excel中

                    row = sheet.createRow(k++);

                    for (int j = 0; j < 28; j++) {

                        row.createCell(j).setCellValue(result.getString(resultTitles[j]));

                    }

                }

            }*/

            String fileName = "/销售订单状态导出.xlsx";

            String path = new File(req.getSession().getServletContext().getRealPath("/")) + fileName;

            // 文件的输出路径

            fout = new FileOutputStream(path);

            wb.write(fout);

            fout.close();

            wb.dispose();

            //把生成的文件下载下来

            File file = new File(req.getSession().getServletContext().getRealPath(fileName));

            ExportDownLoadUtil.resDownload(res,file);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    //year = 2018-01-02

    public static String getYearFourByStr(String year){

        return year.substring(0,4);//

    }

    public static String getYearTwoByStr(String year){

        return year.substring(2,4);//

    }

    public static String getMonthByStr(String year){

        return year.substring(5,7);//

    }

    public static int getYearAndMonth(String year){

        return Integer.parseInt(year.substring(0,4)+year.substring(5,7));//

    }

    public static int getYearFourI(String year){

        return Integer.parseInt(getYearFourByStr(year));//得到整形的年

    }

    public static int getMonthI(String year){

        return Integer.parseInt(getMonthByStr(year));//得到整形的月

    }

    public static String getSheetName(int year,int month){

        return year + "年" + month + "月";

    }

}