POI导出Excel例子

最新推荐文章于 2024-08-15 23:35:23 发布
最新推荐文章于 2024-08-15 23:35:23 发布
阅读量156 收藏
点赞数
分类专栏： POI 导出Excel 文章标签： POI 导出Excel excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xp9802/article/details/84622841
版权

package com.ufida.cutm.export;

 

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.FileWriter;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import org.apache.poi.hssf.usermodel.HSSFCell;

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.poifs.filesystem.POIFSFileSystem;

 

import com.ufida.cutm.dao.DBConnection;

import com.ufida.cutm.util.ConstantsExcel;

import com.ufida.cutm.util.ExcelWriter;

import com.ufida.cutm.util.StringUtilCommon;

import com.ufida.cutm.util.ZipCompressorByAnt;

import com.ufida.cutm.vo.T1;

 

 

/**

 * 测试导出excel

 * 最大数据量

 * 2011-3-9 22：00

 * @author zengq

 *

 */

public class ExportExcel {

 

    public static final String[] excelHeader=new String[]{"id","ida","idb","name","pwd","aa","bb","cc","dd","ee","ff","gg",

                                                           "hh","ii","jj","kk","ll","mm","nn","oo","pp","qq","rr","ss","tt"};

     

    public static final String File_Path="E:\\projects\\java\\ws_0\\export\\data";

    /**

     * @author zengq

     * @param sql 获取记录行数的sql

     * @param recordStr sql语句中记录数的别名

     * @return 总记录数

     * 2011-3-10 14:25

     * @throws Exception

     */

    public  Long getRecordNumber(String sql,String recordStr) throws Exception{

        Connection conn=null;

        PreparedStatement pst=null;

        ResultSet rs1=null;

        recordStr=StringUtilCommon.getPropertyValue(recordStr);

        try {

            Long recordNum=0l;

            conn=DBConnection.getConnection();

 

             pst=conn.prepareStatement(sql);

             rs1=pst.executeQuery();

             

            while(rs1.next()){

                if(recordStr.length()==0){

                    recordNum=rs1.getLong(1);

                }else{

                    recordNum=rs1.getLong(recordStr);

                }

            }

            return recordNum;

        } catch (RuntimeException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally{

            closeResource(rs1,pst,conn);

        }

        return new Long(0);

    }

     

    public List getAll(String sql) throws Exception{

        Connection conn=null;

        PreparedStatement pst=null;

        ResultSet rs1=null;

        List list=null;

        //FileOutputStream out=null;

        FileWriter out=null;

        try {

             list=new ArrayList();

            if(sql==null){

             sql="select *from t1 ";

            }

            conn=DBConnection.getConnection();

//      Statement st=conn.createStatement();

//      ResultSet rs1=st.executeQuery(sql);

            pst=conn.prepareStatement(sql);

            rs1=pst.executeQuery();

            rs1.last();

            System.out.println("rs1.getRow()="+rs1.getRow());

            rs1.beforeFirst();

            File file=new File("c:/test.doc");

//          if(!file.exists()){

//              file=new File("c:/test.doc");

//          }

             //out=new FileOutputStream(file,true);

//           out=new FileWriter(file,true);

            while(rs1.next()){

                T1 t=new T1();

                t.setId(rs1.getLong("id"));

                t.setIda(rs1.getLong("ida"));

                t.setIdb(rs1.getString("idb"));

                t.setName(rs1.getString("name"));

                t.setPwd(rs1.getString("pwd"));

                 

                t.setAa(rs1.getString("aa"));

                t.setBb(rs1.getString("bb"));

                t.setCc(rs1.getString("cc"));

                t.setDd(rs1.getString("dd"));

                 

                t.setEe(rs1.getString("ee"));

                t.setFf(rs1.getString("ff"));

                t.setGg(rs1.getString("gg"));

                t.setHh(rs1.getString("hh"));

                t.setIi(rs1.getString("ii"));

                t.setJj(rs1.getString("jj"));

                t.setKk(rs1.getString("kk"));

                t.setLl(rs1.getString("ll"));

                t.setMm(rs1.getString("mm"));

                t.setNn(rs1.getString("nn"));

                t.setOo(rs1.getString("oo"));

                t.setPp(rs1.getString("pp"));

                t.setQq(rs1.getString("qq"));

                t.setRr(rs1.getString("rr"));

                t.setSs(rs1.getString("ss"));

                t.setTt(rs1.getString("tt"));

                 

                //System.out.println("t1="+t);

                //out.write(t.toString().getBytes());

//              out.write(t.toString()+"\t\r");

                list.add(t);

            }

            System.out.println("空一行*********************************************");

             

        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally{

            closeResource(rs1,pst,conn);

            if(out!=null){

                out.flush();

                out.close();

                out=null;

            }

        }

        return list;

         

}

 

    public  void closeResource(ResultSet rs1,Statement pst,Connection conn) throws Exception{

        if(rs1!=null){

            rs1.close();

            rs1=null;

        }if(pst!=null){

            pst.close();

            pst=null;

        }

        DBConnection.closeConnection(conn);

        System.gc();

    }

    /**

     * 获取总页数

     * @param recordNums

     * @param pageSize

     * @return

     * @throws Exception

     */

    public static Long getTotalPage(Long recordNums,Long pageSize) throws Exception{

        if(recordNums==0||pageSize==0)

            return 0l;

        return (long)Math.ceil(recordNums/(double)pageSize);

    }

     

    /**

     * 为sql语句增加提取数

     * @param basicSql

     * @param begin

     * @param limitNum

     * @return

     * @throws Exception

     */

    public static String getPageSql(String basicSql,Long begin,Long limitNum) throws Exception{

        basicSql=StringUtilCommon.getPropertyValue(basicSql);

        if(basicSql.length()==0)

            return "";

        String sql=" limit "+begin+","+limitNum;

        return sql;

    }

     

    public Map getPageList(Map argsMap) throws Exception{

        String path=(String)argsMap.get("path");

        if(path==null){

            path=this.File_Path;

        }

        File  file=new File(path+"/test0.xls");

        String sql="select count(*) record from t1";

        Long totalNum=getRecordNumber(sql, "");

        System.out.println("totalNum="+totalNum);

        Long pageSize=10000l;

        Long totalPage=getTotalPage(totalNum, pageSize);

        System.out.println("totalPage="+totalPage);

        String sql2="select *from t1 limit ";

        ExcelWriter writer=new ExcelWriter();

        for (int i = 0; i < totalPage; i++) {

            if(i==0){

                sql2+=" 0,"+pageSize;

                System.out.println("sql2="+sql2);

                List list=getAll(sql2);

                WriteToExcel(file,list);

            }else{

                sql2="select *from t1 limit "+pageSize*i+","+pageSize;

                System.out.println("sql2="+sql2);

                List list=getAll(sql2);

                WriteToExcel(new File(path+"/test"+i+".xls"), list);

                //reWirte(file, list);

            }

             

        }

         

        return null;

    }

    HSSFWorkbook workbook=null;

    HSSFSheet sheet=null;

    HSSFRow row=null;

    HSSFCell cell=null;

    public void WriteToExcel(File file,List list) throws Exception{

        if(file==null&&!file.exists()){

            System.out.println("文件不能为空！");

            return;

        }

         

        FileOutputStream out=new FileOutputStream(file,true);

        BufferedOutputStream buffout=new BufferedOutputStream(out);

        ExcelWriter writer=new ExcelWriter();

        writer=new ExcelWriter(buffout);

        writer.createRow(0);

        for (int i = 0; i < excelHeader.length; i++) {

            writer.setCell(i, excelHeader[i]);

        }

        int rownum=0;

        for (int j=0;j<list.size();j++) {

            T1 t=(T1)list.get(j);

            //System.out.println("WriteToExcel="+t);

            //Thread.sleep(1000);

            writer.createRow(rownum++);

            for(int k=0;k<excelHeader.length;k++){

                String header=excelHeader[k];

                //System.out.println("j="+j+",k="+k+",header="+header);

                if(header.equals("id")){

                  writer.setCell(0,t.getId());

                }else if(header.equals("ida")){

                    writer.setCell(1,t.getIda()+"ida");

                }else if(header.equals("idb")){

                    writer.setCell(2,t.getIdb()+"idb");

                }else if(header.equals("name")){

                    writer.setCell(3,t.getName()+"name");

                }else if(header.equals("pwd")){

                    writer.setCell(4,t.getPwd()+"pwd");

                }

                //下一个10行

                else if(header.equals("aa")){

                    writer.setCell(5,t.getAa()+"aa");

                }else if(header.equals("bb")){

                    writer.setCell(6,t.getBb()+"bb");

                }else if(header.equals("cc")){

                    writer.setCell(7,t.getCc()+"cc");

                }else if(header.equals("dd")){

                    writer.setCell(8,t.getDd()+"dd");

                }else if(header.equals("ee")){

                    writer.setCell(9,t.getEe()+"ee");

                }else if(header.equals("ff")){

                    writer.setCell(10,t.getFf()+"ff");

                }else if(header.equals("gg")){

                    writer.setCell(11,t.getGg()+"gg");

                }else if(header.equals("hh")){

                    writer.setCell(12,t.getHh()+"hh");

                }else if(header.equals("ii")){

                    writer.setCell(13,t.getIi()+"ii");

                }else if(header.equals("jj")){

                    writer.setCell(14,t.getJj()+"jj"); 

                }

                //下一个10行

                else if(header.equals("kk")){

                    writer.setCell(15,t.getKk()+"kk");

                }else if(header.equals("ll")){

                    writer.setCell(16,t.getLl()+"ll");

                }else if(header.equals("mm")){

                    writer.setCell(17,t.getMm()+"mm");

                }else if(header.equals("nn")){

                    writer.setCell(18,t.getNn()+"nn");

                }else if(header.equals("oo")){

                    writer.setCell(19,t.getOo()+"oo");

                }else if(header.equals("pp")){

                    writer.setCell(20,t.getPp()+"pp");

                }else if(header.equals("qq")){

                    writer.setCell(21,t.getQq()+"qq");

                }else if(header.equals("rr")){

                    writer.setCell(22,t.getRr()+"rr");

                }else if(header.equals("ss")){

                    writer.setCell(23,t.getSs()+"ss");

                }else if(header.equals("tt")){

                    writer.setCell(24,t.getTt()+"tt");

                }

                 

            }

        }

         try {

               writer.export();

               writer.releaseResource();

               System.out.println("file.size="+file.length()/(1000*1000)+"mb");

               System.out.println(" 导出Excel：文件[成功] ");

              } catch (IOException ex) {

               System.out.println(" 导出Excel：文件[失败] ");

               ex.printStackTrace();

              }

    }

    /**

     * @param args

     */

    public static void main(String[] args) {

        // TODO Auto-generated method stub

            ExportExcel export = new ExportExcel();

 

        try {

            Long start=System.currentTimeMillis();

            export.getPageList(new HashMap());

            Long end=System.currentTimeMillis();

            System.out.println("生成excel總計时间="+(end-start));

            //export.buildExcelByXml(null, null);

//          export.buildMutiSheetExcel(null);

            System.out.println("*************生成excel成功!");

             

        } catch (Exception e) {

            System.out.println("*************生成excel失败!");

            e.printStackTrace();

        }

        try {

            System.out.println("*****************下面开始打包！");

            Long start=System.currentTimeMillis();

            ZipCompressorByAnt zca = new ZipCompressorByAnt("e:/ZipCompressorByAnt.zip");

            zca.compress(export.File_Path);

            Long end=System.currentTimeMillis();

            System.out.println("打包總計时间="+(end-start));

            System.out.println("*****************打包成功！");

        } catch (RuntimeException e) {

            System.out.println("*****************打包失败！");

            e.printStackTrace();

        }

    }

     

     

     

     

     

     

     

     

     

     

     

 

     

     

     

     

     

     

    //追加行

    public void reWirte(File file,List list) throws FileNotFoundException, IOException {

        //获取workbook

        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(

                new FileInputStream(file)));

        //获取sheet

        HSSFSheet sheet = wb.getSheetAt(0);

        //获取最后一行行号，从0开始

        int maxRow = sheet.getLastRowNum();

        HSSFRow row = null;//sheet.createRow(maxRow + 1);

//      row.createCell((short) 0).setCellValue("追加的1");

     

        int rownum=0;

        for (int j=0;j<list.size();j++) {

            T1 t=(T1)list.get(j);

            //System.out.println("WriteToExcel="+t);

            row=sheet.createRow(++maxRow);

            for(int k=0;k<excelHeader.length;k++){

                String header=excelHeader[k];

                //System.out.println("j="+j+",k="+k+",header="+header);

                if(header.equals("id")){

                  row.createCell((short) 0).setCellValue(t.getId());

                }else if(header.equals("ida")){

                    row.createCell((short) 1).setCellValue(t.getIda());

                }else if(header.equals("idb")){

                    row.createCell((short) 2).setCellValue(t.getIdb()+"idb");

                }else if(header.equals("name")){

                    row.createCell((short) 3).setCellValue(t.getName()+"name");

                }else if(header.equals("pwd")){

                    row.createCell((short) 4).setCellValue(t.getPwd()+"pwd");

                }else if(header.equals("aa")){

                    row.createCell((short) 5).setCellValue(t.getAa()+"aa");

                }else if(header.equals("bb")){

                    row.createCell((short) 6).setCellValue(t.getBb()+"bb");

                }else if(header.equals("cc")){

                    row.createCell((short) 7).setCellValue(t.getCc()+"cc");

                }else if(header.equals("dd")){

                    row.createCell((short) 8).setCellValue(t.getDd()+"dd");

                }else if(header.equals("ee")){

                    row.createCell((short) 9).setCellValue(t.getEe()+"ee");

                }else if(header.equals("ff")){

                    row.createCell((short) 10).setCellValue(t.getFf()+"ff");

                }else if(header.equals("gg")){

                    row.createCell((short) 11).setCellValue(t.getGg()+"gg");

                }else if(header.equals("hh")){

                    row.createCell((short) 12).setCellValue(t.getHh()+"hh");

                }else if(header.equals("ii")){

                    row.createCell((short) 13).setCellValue(t.getIi()+"ii");

                }else if(header.equals("jj")){

                    row.createCell((short) 14).setCellValue(t.getJj()+"jj");   

                }else if(header.equals("kk")){

                    row.createCell((short) 15).setCellValue(t.getKk()+"kk");

                }else if(header.equals("ll")){

                    row.createCell((short) 16).setCellValue(t.getLl()+"ll");

                }else if(header.equals("mm")){

                    row.createCell((short) 17).setCellValue(t.getMm()+"mm");

                }else if(header.equals("nn")){

                    row.createCell((short) 18).setCellValue(t.getNn()+"nn");

                }else if(header.equals("oo")){

                    row.createCell((short) 19).setCellValue(t.getOo()+"oo");

                }else if(header.equals("pp")){

                    row.createCell((short) 20).setCellValue(t.getPp()+"pp");

                }else if(header.equals("qq")){

                    row.createCell((short) 21).setCellValue(t.getQq()+"qq");

                }else if(header.equals("rr")){

                    row.createCell((short) 22).setCellValue(t.getRr()+"rr");

                }else if(header.equals("ss")){

                    row.createCell((short) 23).setCellValue(t.getSs()+"ss");

                }else if(header.equals("tt")){

                    row.createCell((short) 24).setCellValue(t.getTt()+"tt");

                }

                 

            }

        }

        FileOutputStream fos = new FileOutputStream(file);

        try {

            wb.write(fos);

            fos.close();

            System.out.println("*****************追加文件成功!");

        } catch (RuntimeException e) {

            // TODO Auto-generated catch block

            System.out.println("*****************追加文件失败!");

            e.printStackTrace();

        }

    }

     

    /**

     * 通过xml文档生成excel文件

     * @param argsMap

     * @throws Exception

     */

    public void buildExcelByXml(Map<String, String> argsMap,File file) throws Exception{

         

        String sql="select count(*) record from t1";

        Long totalNum=new ExportExcel().getRecordNumber(sql, "");

        System.out.println("totalNum="+totalNum);

        Long pageSize=10000l;

        Long totalPage=ExportExcel.getTotalPage(totalNum, pageSize);

        System.out.println("totalPage="+totalPage);

        String sql2="select *from t1 limit ";

        ExcelWriter writer=new ExcelWriter();

        String contents="";

        for (int i = 0; i < totalPage; i++) {

            if(i==0){

                sql2+=" 0,"+pageSize;

                System.out.println("sql2="+sql2);

                List list=getAll(sql2);

                contents=getXmlExcelContentByList(null, list);

                System.out.println("第"+i+"次*************************contents.length()="+contents.length()/(1000*1000)+"mb");

            }else{

                sql2="select *from t1 limit "+pageSize*i+","+pageSize;

                System.out.println("sql2="+sql2);

                List list=getAll(sql2);

                contents+=getXmlExcelContentByList(null, list);

                System.out.println("第"+i+"次**************************contents.length()="+contents.length()/(1000*1000)+"mb");

            }

             

        }

        if(argsMap==null){

          argsMap=new HashMap();

        }

        argsMap.put(ConstantsExcel.Excel_Main_String_Key, contents);

        createXmlExcel(argsMap,file);

    }

     

    public void createXmlExcel(Map map,File file) throws Exception{

        if(file==null){

             file = new File("c:/exprot.xls");

              

        }if(!file.exists()){

            file.mkdir();

        }

         

        StringBuffer bf = new StringBuffer();

        bf.append(ConstantsExcel.Excel_Header_String);

        String contents=(String) map.get(ConstantsExcel.Excel_Main_String_Key);

        if(contents==null||contents.length()==0){

            contents="";

        }

        bf.append(contents);

         

        bf.append(ConstantsExcel.Excel_Foot_String);

        try {

            FileWriter write=new FileWriter(file);

            //write.append(bf.toString());

            write.write(bf.toString());

            write.flush();

            write.close();

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

         

    }

     

    public String getXmlExcelContentByList(String str,List list) throws Exception{

        if(str==null){

          str="";

        }

        if(list==null||list.size()==0){

            return null;

        }

        System.out.println("*****************开始生成content!");

        for (Object obj : list) {

            T1 t=(T1) obj;

            str+="<tr height=19 style='height:14.25pt'>                                "    

            +" <td height=19 width=72 style='height:14.25pt;width:54pt'>"+t.getId()+"</td>    "    

            +" <td width=72 style='width:54pt'>"+t.getIda()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getIdb()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getName()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getPwd()+"</td>                             "   

             

            +" <td width=72 style='width:54pt'>"+t.getAa()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getBb()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getCc()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getDd()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getEe()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getFf()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getGg()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getHh()+"</td>                             "  

            +" <td width=72 style='width:54pt'>"+t.getIi()+"</td>                             "  

            +" <td width=72 style='width:54pt'>"+t.getJj()+"</td>                             "    

             

            +" <td width=72 style='width:54pt'>"+t.getKk()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getLl()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getMm()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getNn()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getOo()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getPp()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getQq()+"</td>                             "    

            +" <td width=72 style='width:54pt'>"+t.getRr()+"</td>                             "   

            +" <td width=72 style='width:54pt'>"+t.getSs()+"</td>                             "

            +" <td width=72 style='width:54pt'>"+t.getTt()+"</td>                             "    

            +"</tr>                                                                " ;

        }

        System.out.println("*****************生成content成功!");

        return str;

    }

     

     

    public Map buildMutiSheetExcel(Map argsMap) throws Exception{

         

        String sql="select count(*) record from t1";

        Long totalNum=new ExportExcel().getRecordNumber(sql, "");

        System.out.println("totalNum="+totalNum);

        Long pageSize=10000l;

        Long totalPage=ExportExcel.getTotalPage(totalNum, pageSize);

        System.out.println("totalPage="+totalPage);

        String sql2="select *from t1 limit ";

         

        String excelName="c:/mutisheet2.xls";

        HSSFWorkbook book=new HSSFWorkbook();

        HSSFRow row=null;

        HSSFCell cell=null;

        File file=new File("c:/sheets.xls");

        FileOutputStream out=null;

        BufferedOutputStream buff=null;

        if(argsMap==null){

            argsMap=new HashMap();

            argsMap.put("book", book);

            argsMap.put("excelName", excelName);

        }

        for (int i = 0; i < totalPage; i++) {

            if(i==0){

                sql2+=" 0,"+pageSize;

                System.out.println("sql2="+sql2);

                List list=getAll(sql2);

                HSSFSheet sheet=book.createSheet();

//              argsMap.put("sheet", sheet);

//              argsMap.put("list", list);

//              buildMutiSheet(argsMap);

                int maxRowNum=sheet.getLastRowNum();

                for (int j=0;j<list.size();j++) {

                    T1 t=(T1)list.get(j);

                    //System.out.println("WriteToExcel="+t);

                    row=sheet.createRow(maxRowNum++);

                    int length=excelHeader.length;

                    for(int k=0;k<5;k++){

                        String header=excelHeader[k];

                        //System.out.println("j="+j+",k="+k+",header="+header);

                        cell=row.createCell((short) k);

                        if(header.equals("id")){

                          cell.setCellValue(t.getId());

                        }else if(header.equals("ida")){

                            cell.setCellValue(t.getIda()+"ida");

                        }else if(header.equals("idb")){

                            cell.setCellValue(t.getIdb()+"idb");

                        }else if(header.equals("name")){

                            cell.setCellValue(t.getName()+"name");

                        }else if(header.equals("pwd")){

                            cell.setCellValue(t.getPwd()+"pwd");

                        }

                    }

                }

                out=new FileOutputStream(file,true);

                buff=new BufferedOutputStream(out);;

                book.write(buff);

                buff.flush();

                buff.close();

                buff=null;

                cell=null;

                row=null;

                sheet=null;

            }else{

                sql2="select *from t1 limit "+pageSize*i+","+pageSize;

                System.out.println("sql2="+sql2);

                HSSFSheet sheet2=book.createSheet();

                 

//              argsMap.put("sheet", sheet);

                List list=getAll(sql2);

                int maxRowNum=sheet2.getLastRowNum();

                for (int j=0;j<list.size();j++) {

                    T1 t=(T1)list.get(j);

                    //System.out.println("WriteToExcel="+t);

                    if(sheet2==null){

                        sheet2=book.createSheet();

                    }

                    if(row==null){

                      row=sheet2.createRow(maxRowNum++);

                    }

                    int length=excelHeader.length;

                    for(int k=0;k<5;k++){

                        String header=excelHeader[k];

                        //System.out.println("j="+j+",k="+k+",header="+header);

                        cell=row.createCell((short) k);

                        if(header.equals("id")){

                          cell.setCellValue(t.getId());

                        }else if(header.equals("ida")){

                            cell.setCellValue(t.getIda()+"ida");

                        }else if(header.equals("idb")){

                            cell.setCellValue(t.getIdb()+"idb");

                        }else if(header.equals("name")){

                            cell.setCellValue(t.getName()+"name");

                        }else if(header.equals("pwd")){

                            cell.setCellValue(t.getPwd()+"pwd");

                        }

                    }

                    out=new FileOutputStream(file,true);

                    buff=new BufferedOutputStream(out);;

                    book.write(buff);

                    buff.flush();

                    buff.close();

                    buff=null;

                    cell=null;

                    row=null;

                    sheet2=null;

//              argsMap.put("list", list);

//              buildMutiSheet(argsMap);

                 

            }

             

         }

        }

         

        return null;

    }

     

    public void buildMutiSheet(Map argsMap) throws Exception{

        String excelName=(String) argsMap.get("excelName");

        excelName=StringUtilCommon.getPropertyValue(excelName);

        if(excelName.length()==0){

            excelName="c:/mutisheet2.xls";

        }

        File file=new File(excelName);

        if(file==null&&!file.exists()){

            file=new File(excelName);

        }

         

        HSSFWorkbook book=(HSSFWorkbook) argsMap.get("book");

        if(book==null){

            book=new HSSFWorkbook();

        }

         

        System.out.println("file.size="+file.length()/(1000*1000)+"mb");

        FileOutputStream out=new FileOutputStream(file,true);

        BufferedOutputStream buffout=new BufferedOutputStream(out);

        HSSFSheet sheet=(HSSFSheet) argsMap.get("sheet");

        HSSFRow row=null;

        HSSFCell cell=null;

        for (int i = 0; i < excelHeader.length; i++) {

            if(sheet==null){

             sheet=book.createSheet();

            }

            if(row==null){

             row=sheet.createRow(0);

            }

            cell=row.createCell((short) i);

            cell.setCellValue(excelHeader[i]);

        }

        cell=null;

        List list=(List) argsMap.get("list");

        if(list==null){

            return;

        }

        //int rownum=0;

        int maxRowNum=sheet.getLastRowNum();

        for (int j=0;j<list.size();j++) {

            T1 t=(T1)list.get(j);

            //System.out.println("WriteToExcel="+t);

            row=sheet.createRow(maxRowNum++);

            int length=excelHeader.length;

            for(int k=0;k<5;k++){

                String header=excelHeader[k];

                //System.out.println("j="+j+",k="+k+",header="+header);

                cell=row.createCell((short) k);

                if(header.equals("id")){

                  cell.setCellValue(t.getId());

                }else if(header.equals("ida")){

                    cell.setCellValue(t.getIda()+"ida");

                }else if(header.equals("idb")){

                    cell.setCellValue(t.getIdb()+"idb");

                }else if(header.equals("name")){

                    cell.setCellValue(t.getName()+"name");

                }else if(header.equals("pwd")){

                    cell.setCellValue(t.getPwd()+"pwd");

                }

                 

                /*else if(header.equals("aa")){

                    cell.setCellValue(t.getAa()+"aa");

                }else if(header.equals("bb")){

                    cell.setCellValue(t.getBb()+"bb");

                }else if(header.equals("cc")){

                    cell.setCellValue(t.getCc()+"cc");

                }else if(header.equals("dd")){

                    cell.setCellValue(t.getDd()+"dd");

                }else if(header.equals("ee")){

                    cell.setCellValue(t.getEe()+"ee");

                }else if(header.equals("ff")){

                    cell.setCellValue(t.getFf()+"ff");

                }else if(header.equals("gg")){

                    cell.setCellValue(t.getGg()+"gg");

                }else if(header.equals("hh")){

                    cell.setCellValue(t.getHh()+"hh");

                }else if(header.equals("ii")){

                    cell.setCellValue(t.getIi()+"ii");

                }else if(header.equals("jj")){

                    cell.setCellValue(t.getJj()+"jj"); 

                }

                 

                else if(header.equals("kk")){

                    cell.setCellValue(t.getKk()+"kk");

                }else if(header.equals("ll")){

                    cell.setCellValue(t.getLl()+"ll");

                }else if(header.equals("mm")){

                    cell.setCellValue(t.getMm()+"mm");

                }else if(header.equals("nn")){

                    cell.setCellValue(t.getNn()+"nn");

                }else if(header.equals("oo")){

                    cell.setCellValue(t.getOo()+"oo");

                }else if(header.equals("pp")){

                    cell.setCellValue(t.getPp()+"pp");

                }else if(header.equals("qq")){

                    cell.setCellValue(t.getQq()+"qq");

                }else if(header.equals("rr")){

                    cell.setCellValue(t.getRr()+"rr");

                }else if(header.equals("ss")){

                    cell.setCellValue(t.getSs()+"ss");

                }else if(header.equals("tt")){

                    cell.setCellValue(t.getTt()+"tt");

                }*/

                cell=null;

            }

        }

         try {

               book.write(buffout);

               buffout.flush();

               buffout.close();

               buffout=null;

               list=null;

               sheet=null;

               row=null;

               cell=null;

               System.out.println(" 导出Excel：文件[成功] ");

              } catch (IOException ex) {

               System.out.println(" 导出Excel：文件[失败] ");

               ex.printStackTrace();

              }

    }

     

    public void writeContent(ExcelWriter writer,List list) throws Exception{

        int rownum=0;

        for (int j=0;j<list.size();j++) {

            T1 t=(T1)list.get(j);

            //System.out.println("WriteToExcel="+t);

            writer.createRow(rownum++);

            for(int k=0;k<excelHeader.length;k++){

                String header=excelHeader[k];

                //System.out.println("j="+j+",k="+k+",header="+header);

                if(header.equals("id")){

                  writer.setCell(0,t.getId());

                }else if(header.equals("ida")){

                    writer.setCell(1,t.getIda()+"ida");

                }else if(header.equals("idb")){

                    writer.setCell(2,t.getIdb()+"idb");

                }else if(header.equals("name")){

                    writer.setCell(3,t.getName()+"name");

                }else if(header.equals("pwd")){

                    writer.setCell(4,t.getPwd()+"pwd");

                }else if(header.equals("aa")){

                    writer.setCell(5,t.getAa()+"aa");

                }else if(header.equals("bb")){

                    writer.setCell(6,t.getBb()+"bb");

                }else if(header.equals("cc")){

                    writer.setCell(7,t.getCc()+"cc");

                }else if(header.equals("dd")){

                    writer.setCell(8,t.getDd()+"dd");

                }

                else if(header.equals("ee")){

                    writer.setCell(9,t.getEe()+"ee");

                }else if(header.equals("ff")){

                    writer.setCell(10,t.getFf()+"ff");

                }

                else if(header.equals("gg")){

                    writer.setCell(11,t.getGg()+"gg");

                }else if(header.equals("hh")){

                    writer.setCell(12,t.getHh()+"hh");

                }

                else if(header.equals("ii")){

                    writer.setCell(13,t.getIi()+"ii");

                }

                    else if(header.equals("jj")){

                    writer.setCell(14,t.getJj()+"jj"); 

                }else if(header.equals("kk")){

                    writer.setCell(15,t.getKk()+"kk");

                }else if(header.equals("ll")){

                    writer.setCell(16,t.getLl()+"ll");

                }else if(header.equals("mm")){

                    writer.setCell(17,t.getMm()+"mm");

                }else if(header.equals("nn")){

                    writer.setCell(18,t.getNn()+"nn");

                }else if(header.equals("oo")){

                    writer.setCell(19,t.getOo()+"oo");

                }else if(header.equals("pp")){

                    writer.setCell(20,t.getPp()+"pp");

                }else if(header.equals("qq")){

                    writer.setCell(21,t.getQq()+"qq");

                }else if(header.equals("rr")){

                    writer.setCell(22,t.getRr()+"rr");

                }else if(header.equals("ss")){

                    writer.setCell(23,t.getSs()+"ss");

                }else if(header.equals("tt")){

                    writer.setCell(24,t.getTt()+"tt");

                }

                 

            }

        }

    }

     

}

xp9802
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
C# NPOI导出多个sheet页的Excel方法
csj1314520的博客
04-18 2039
try { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel文件|*.xlsx"; saveFileDialog.FilterIndex = 2; saveFileDialog.RestoreDirectory = true; ...
poi导出excel 损坏_使用POI导出Excel例子与常见问题
weixin_31782201的博客
12-28 465
代码function toExcel(){$("#formExcel").submit();}@RequestMapping(value="/toExcel",method=RequestMethod.POST)public String toExcel(HttpServletResponse response, String fileName, String tableName, String ...
Java使用POI导出Excel
怪咖@的博客
10-12 9920
在项目开发中往往需要使用到Excel的导入和导出，导入就是从Excel中导入到DB中，而导出就是从DB中查询数据然后使用POI写到Excel上。POI不仅仅可以操作Excel，他的定位是Microsoft Of操作读和写，Microsoft Office其中包含了很多常用的办公文件，例如：Excel、ppt、word、Visio等等…
使用POI导出Excel示例
marila4720的专栏
02-22 542
使用POI导出Excel，需要的Jar包：poi-bin-3.9-20121203.zip 示例代码如下： import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFo
poi导出excel
zhaihonghonghzh的专栏
09-07 974
1、pom.xml文件中添加依赖<!--EasyPOI--> <dependency> <groupId>org.jeecg</groupId> <artifactId>easypoi-base</artifactId> <version>2.3.0.2</version> <exclusions> <exclusion> <g
poi 导出EXCEL 例子
08-27
接下来，我们将创建一个简单的Java程序来演示如何使用POI导出Excel文件。首先，导入必要的POI库： ```java import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import ...
poi导出excel需要的jar
01-06
"poi导出excel需要的jar"指的是在使用Apache POI进行Excel导出时，你需要包含特定的JAR依赖文件。 首先，要实现POI导出Excel的功能，你需要下载Apache POI相关的JAR文件。这些文件通常包括以下核心组件： 1. **poi...
POI导出Excel文件
10-14
以下是一个简化的示例，演示了如何使用POI导出Excel： ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io....
Apache poi 导出excel实例
08-08
这篇博客文章“Apache POI 导出excel实例”将深入探讨如何使用Apache POI库来生成Excel文件，这对于在Java环境中处理大量数据并需要导出为Excel格式的应用非常有用。 首先，我们需要导入Apache POI库到我们的项目中...
POI使用Excel模板文件循环输出行并导出Excel
08-15
在这个特定的例子中，我们将讨论如何使用POI库基于一个Excel模板文件循环输出数据行，并将结果导出为新的Excel文件。 首先，我们需要理解POI库的基本概念。POI提供了HSSF（Horizontally SpreadSheet Format）和XSSF...
免费Excel工作表同类数据合并工具
weixin_53505226的博客
08-14 284
下载地址：https://pan.quark.cn/s/81b1aeb45e4c 　　在Excel表格中，把多行同类数据合并为一行是件令人无比头痛的事情：首先，你得确定哪几条记录是可合并的同类数据，人工对比多个字段难免顾此失彼挂一漏万；其次，待合并的行可能分属多个分组，需要各自合并，但是同组的多行数据往往会分布在天遥地远的距离上，频繁地在表格的上下左右之间往返，势必令人心力憔悴；一旦确定了哪些行可以分别合并之后，还得分成数值和字符串两部分分别执行数值运算或拼接操作；最后，若要把计算结果填入对应的
Excel工作表同类数据合并工具
weixin_53505226的博客
08-12 525
下载地址：https://pan.quark.cn/s/81b1aeb45e4c 　　在Excel表格中，把多行同类数据合并为一行是件令人无比头痛的事情：首先，你得确定哪几条记录是可合并的同类数据，人工对比多个字段难免顾此失彼挂一漏万；其次，待合并的行可能分属多个分组，需要各自合并，但是同组的多行数据往往会分布在天遥地远的距离上，频繁地在表格的上下左右之间往返，势必令人心力憔悴；一旦确定了哪些行可以分别合并之后，还得分成数值和字符串两部分分别执行数值运算或拼接操作；最后，若要把计算结果填入对应的单
快速把文件名统计到excel表的方法
方可软件的博客
08-13 346
快速把文件名统计到EXCEL表的方法，它只能通过专门的软件工具实现，操作系统自带的功能暂时没有这个功能，所以我们要实现这个功能，只能找相关的软件来做，上面介绍的这款批量提取器就是一个专门的软件。
【EasyExcel】导出excel-设置动态表头并导出数据
欢迎来到三十七度的博客
08-15 201
本文主要通过EasyExcel实现动态表头，扩展性还可以，提供的是基本实现，方便自行研究，如有其它问题或者好的方法，可直接留言或私信，欢迎指正。
制作好的excel报表设置打开密码或忘记密码怎么办？
pf_data的博客
08-12 210
excel工作表经常用来做数据统计、工资、报表等的文件格式，这些类型的文件都是很重要的数据资料，为此做这些数据的朋友们都会给他设置一个打开密码，不让其他人随便打开。但随着时间的流逝，我们做的数据报表越来越多了，做好后还不经常使用，所以就把密码忘记了，遇到这种情况时，我们该这么做呢？总的来说，虽然通过使用excel密码找回工具有可能找回密码，但忘记密码还是会带来诸多不便。因此，在设置密码时，大家可以设置易于记忆但又足够复杂的密码，并妥善保管。几分钟后，正确的密码将显示在弹出窗口中。
PHPExcel兼容php7.4以上版本
itfans123的博客
08-15 101
参考官方文档：7.4 版本的向后不兼容更改，非数组的数组样式访问，现在，尝试将 null，bool，int，float 或 resource 类型的值用作数组 ( 例如 $null[“key”] )修改后的PHPExcel链接：https://pan.quark.cn/s/425905b39a48。php7.4后不支持 array{} 这种形式数组取值。PHPExcel 在php下报错。
excel 2019版本的index match搜索功能
最新发布
VB973490770的专栏
08-15 83
按Ctrl+Shift+Enter会出现{},代表数组公式，可以查找一组数据，并返回一组数据，然后根据情况处理即可，可以用JOIN将一组数据合并到一个单元格，用+分割或者其他符号分割即可。A:A="文字")*(sheet2!A:A="文字")*(sheet2!
怎样才算精通 Excel？
wly476923083的博客
08-09 1560
没必要去啃复杂的书，直接把你实际的问题，抛给AI，让AI给你提供针对你的分析思路。比如，我是数据分析小白，但我的工作中有数据，我希望通过数据分析，做一些洞察，我没有思路，也不知道整理哪些数据，该怎么办？在Excel，这是vlookup的一个常用场景，用过的肯定遇到过，设置出错、公式难记等情况。接下来，我会结合我遇到的案例，以及用AI辅助提效的思路，让你无痛搞定Excel。好了，我们思考一下，日常工作中，我们会在哪些情况下，需要用到Excel？还是一样，直接把图片发给AI，让它输出。
分组汇总时保留不变列
smilejingwei的博客
08-14 229
要求：低版本的Excel（不支持groupby函数），按ID分组，对Value1和Value2求和，保留Descr 1和Descr 2。Excel表格的ID列是分类，Value1和Value2是数值，ID相同时Descr 1和Descr 2保持不变。函数 E 将片区转为表格，groups 对表格分组汇总，计算出的新列可以用冒号改名。使用 SPL XLL。
springBoot使用poi导出Excel表格
05-29
好的，我可以为您提供帮助。首先，您需要在您的项目中添加 POI 和 POI-OOXML 依赖。您可以在您的 pom.xml 文件中添加以下依赖： ```xml <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> ``` 接下来，您可以创建一个类来生成 Excel 文件。以下是一个简单的示例： ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io.IOException; public class ExcelExporter { public static void export() throws IOException { // 创建一个工作簿 Workbook workbook = new XSSFWorkbook(); // 创建一个工作表 Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个行 Row row = sheet.createRow(0); // 创建单元格并添加值 Cell cell = row.createCell(0); cell.setCellValue("Hello World"); // 保存工作簿 FileOutputStream outputStream = new FileOutputStream("output.xlsx"); workbook.write(outputStream); workbook.close(); } } ``` 在这个例子中，我们创建了一个工作簿，然后创建了一个工作表和一行。我们然后创建了一个单元格，将值设置为 "Hello World"。最后，我们将工作簿保存到文件中。 您可以通过调用 `ExcelExporter.export()` 来生成 Excel 文件。这将在项目的根目录下创建一个名为 "output.xlsx" 的文件。 当然，这只是一个简单的示例。如果您需要更复杂的 Excel 文件，您可以使用 POI 的其他功能来添加样式、合并单元格等等。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

xp9802

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值