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);//2018
    }
    public static String getYearTwoByStr(String year){
        return year.substring(2,4);//18
    }
    public static String getMonthByStr(String year){
        return year.substring(5,7);//01
    }
    public static int getYearAndMonth(String year){
        return Integer.parseInt(year.substring(0,4)+year.substring(5,7));//201801
    }
    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 + "月";
    }

}