使用POI输出（生成）Excel文件

继上一篇“使用POI读取Excel文件”文章之后有对生成Excel文件进行如下操作：

导出Excel文件：

package com.sanji.io;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.sanji.entity.Excel;

public class ExcelWriter {
    /**
     * 导出xls格式的excel
     * 
     * @param list
     */
    public void create(List<Excel> list) {
        try {
            // 导出xls格式的excel
            Workbook wb = new HSSFWorkbook();
            // 导出xlsx格式的excel
            // Workbook wb = new XSSFWorkbook();
            // 创建一个SHEET
            Sheet sheet1 = wb.createSheet("sheet1");
            // 设置表头要显示的内容
            String[] title = { "编号", "名称", "产品价格" };
            int i = 0;
            // 创建一行
            Row row = sheet1.createRow((short) 0);
            // 填充标题.将标题放入第一行各列
            for (String s : title) {
                Cell cell = row.createCell(i);
                cell.setCellValue(s);
                i++;// 变量
            }
            
            /*+====创建内容=====*/
            int j = 1;
            for (Excel e : list) {
                System.err.println(e);
                Row row1=null;
                row1 = sheet1.createRow((short) j);// 创建行
                row1.createCell(0).setCellValue(e.getId());// 创建格子并赋值
                row1.createCell(1).setCellValue(e.getName());// 创建格子并赋值
                row1.createCell(2).setCellValue(e.getSalary());// 创建格子并赋值
                j++;//变量
                 
            }
            
            FileOutputStream fileOut = new FileOutputStream("E:\\test.xlsx");
            wb.write(fileOut);
            fileOut.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("到出失败");
        }
    }

}

测试类：

package com.sanji.test;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.sanji.dao.ExcelDaoImpl;
import com.sanji.entity.Excel;
import com.sanji.io.ExcelReader;
import com.sanji.io.ExcelWriter;

public class test {
    public static void main(String[] args) {
        try {
            /**
             *  对读取Excel表格标题测试
             */
            InputStream is = new FileInputStream("d:\\test2.xls");//读取excel文件
            ExcelReader excelReader = new ExcelReader();//获取工具类
            String[] title = excelReader.readExcelTitle(is);//读取标题
            System.out.println("获得Excel表格的标题:");
            for (String s : title) {
                System.out.print(s + " ");
            }
            // 对读取Excel表格内容测试
            InputStream is2 = new FileInputStream("d:\\test2.xls");
            String[][] arr = excelReader.readExcelContent(is2);
            /**
             * 将二维数组转换成对象数组批量存入数据库
             */
            List<Excel> list=new ArrayList<Excel>();
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i < arr.length; i++) {
                Excel exc=new Excel();//将数据转换成对象
                exc.setId(arr[i][0]);//添加数据
                exc.setName(arr[i][1]);
                exc.setSalary(arr[i][2]);
                list.add(exc);
                for(int j=0;j<arr[i].length;j++){
                    System.out.print(arr[i][j]+"\t");
                }
                System.out.println();
            }
            //读取到的数据添加到数据库
//            new ExcelDaoImpl().addList(list);
            
            /**
             * 获取数据生成excel文件
             */
            ExcelWriter excelWriter=new ExcelWriter();
//          list=new ExcelDaoImpl().findAll();
            excelWriter.create(list);//生成Excel文件 

        } catch (Exception e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }
    }
}

数据库操作：

/**
     * 从数据库获取数据
     * @return list<Excel>;列表
     */
    public List<Excel> findAll() throws Exception {
        List<Excel> list=new ArrayList<Excel>();
        Excel e = null;
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rst = null;
        try {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement(
                    "SELECT * FROM excel ");
            rst = prep.executeQuery();
            while(rst.next()){
                e = new Excel();
                e.setId(rst.getString("id"));
                e.setName(rst.getString("name"));
                e.setSalary(rst.getString("salary"));
                list.add(e);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            throw e1;
        }finally{
            DBUtil.close(conn);
        }
        return list;
    }

转载于:https://my.oschina.net/ChenGuop/blog/505065