java导出Excel文档

• 1.准备必要的jar包

  poi-3.7-20101029.jar

• 2.ExcelUtil.java 工具类

package com.test3;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelUtil {
    public static boolean exportExcel(List<Map<String, Object>> dataList,Map<String, String> titleMap,File file){
        boolean flag=true;
        if (dataList==null||dataList.size()==0) {
            return false;
        }
        if (titleMap==null||titleMap.size()==0) {
            return false;
        }
        //创建一个workbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("sheet1");

        //表头样式和字体
        HSSFCellStyle titleStyle = wb.createCellStyle(); 
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 
        titleStyle.setBorderTop((short)1); 
        titleStyle.setBorderLeft((short)1);
        titleStyle.setBorderRight((short)1);
        titleStyle.setBorderBottom((short)1);

        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)20);   //--->设置字体大小  
        titleFont.setFontName("宋体");   //--->设置字体，是什么类型例如：宋体  
        titleStyle.setFont(titleFont);

        //数据单元格样式、字体
        HSSFCellStyle cellStyle = wb.createCellStyle();  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 
        cellStyle.setBorderTop((short)1); 
        cellStyle.setBorderLeft((short)1);
        cellStyle.setBorderRight((short)1);
        cellStyle.setBorderBottom((short)1);

        Font colTitleFont = wb.createFont();
        colTitleFont.setFontHeightInPoints((short)16);   //--->设置字体大小  
        colTitleFont.setFontName("宋体");   //--->设置字体，是什么类型例如：宋体  
        cellStyle.setFont(colTitleFont);

        //创建标题行
        HSSFRow rowOne = sheet.createRow(0);
        rowOne.setHeightInPoints(30);//设置标题行高
        //合并单元格   四个参数分别是：起始行，结束行，起始列，结束列  
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleMap.size()-1));
        HSSFCell cell1 = rowOne.createCell(0);
        cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell1.setCellStyle(titleStyle);
        cell1.setCellValue("导出列表");

        //创建表头行
        HSSFRow title_row = sheet.createRow(1);   
        title_row.setHeightInPoints(25);//设置表头行高

        //创建表头行单元格、添加各表头
        HSSFCell title_cell = null;
        int i=0;
        for(Entry<String, String> entity : titleMap.entrySet()){
            sheet.setColumnWidth(i, (short) (35.7 * 200));//设置列宽
            title_cell = title_row.createCell(i);
            title_cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置表头单元格类型
            title_cell.setCellStyle(titleStyle);//应用表头样式
            title_cell.setCellValue(entity.getValue());
            i++;
        }

        //加入数据
        HSSFRow row = null;
        HSSFCell cell = null;
        for(int j = 0; j<dataList.size(); j++){
            Map<String, Object> entity = (Map<String, Object>) dataList.get(j);
            int ii = 0;
            row = sheet.createRow(j+2);
            for(Entry<String, String> title : titleMap.entrySet()){
                cell = row.createCell(ii);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellStyle(cellStyle);//应用内容单元格样式
                if(entity.get(title.getKey())!=null){
                    cell.setCellValue(entity.get(title.getKey()).toString());
                }else {
                    cell.setCellValue("");
                }
                ii++;
            }
        }
        try {
            FileOutputStream f = new FileOutputStream(file);
            wb.write(f);//写入文件
            f.flush();
            f.close();
        } catch (Exception e) {
            e.printStackTrace();
            flag=false;
        }
        return flag;

    }
}

• 3.Text.java 测试类

package com.test3;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Test {

    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        // 要访问的数据库
        String url = "jdbc:oracle:thin:@192.168.1.5:1521:orcl";
        String user = "orcl";
        String password = "orcl";
        try {
            // 加载驱动程序
            Class.forName(driver);
            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);
            if (!conn.isClosed())
                System.out.println("----------start----------");
            String sql = "select c.special_plan_id,c.plan_name,c.begin_time,c.end_time,c.corp_id,c.dep_id,c.user_id,"
                    + "c.state,c.method,c.low_risk,c.high_risk,to_char(c.make_time,'YYYY-mm-dd') make_time,"
                    + "c.type from check_specialplan c";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
                }
                list.add(map);
            }
            rs.close();
            st.close();
            conn.close();

            Map<String, String> titleMap = new LinkedHashMap<String, String>();
            titleMap.put("SPECIAL_PLAN_ID", "id");
            titleMap.put("PLAN_NAME", "计划名称");
            titleMap.put("BEGIN_TIME", "开始时间");
            titleMap.put("END_TIME", "结束时间");
            titleMap.put("CORP_ID", "公司id");
            titleMap.put("DEP_ID", "部门id");
            titleMap.put("USER_ID", "制定人id");
            titleMap.put("STATE", "状态");
            titleMap.put("METHOD", "方法");
            titleMap.put("LOW_RISK", "low");
            titleMap.put("HIGH_RISK", "high");
            titleMap.put("MAKE_TIME", "制定时间");
            titleMap.put("TYPE", "类型");
            ExcelUtil.exportExcel(list, titleMap, new File("E:\\桌面\\123.xls"));

            System.out.println("-----------end-----------");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

• 4.效果图