当先锋百科网

首页 1 2 3 4 5 6 7

数据库导出到excel

项目结构同上一篇

泛型通用的写法

ExportExcel.java

package excel;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;

public class ExportExcel<T> {
    public void exportExcel(String title, String[] headers, List<T> list, OutputStream out){
        //声明一个工作薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = hssfWorkbook.createSheet(title);
        //设置表格默认列宽度
        sheet.setDefaultColumnWidth(15);
        //生成一个样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //设置样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成字体
        HSSFFont font = hssfWorkbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for(int i = 0; i < headers.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        int index = 0;
        for(T t: list){
            index++;
            row = sheet.createRow(index);
            Field[] fields = t.getClass().getDeclaredFields();
            for(int i = 0; i < fields.length; i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try{
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName,
                            new Class[] {});
                    Object value = getMethod.invoke(t, new Object[] {});
                    String textValue = value.toString();
                    HSSFRichTextString richString = new HSSFRichTextString(textValue);
                    HSSFFont font3 = hssfWorkbook.createFont();
                    font3.setColor(HSSFColor.BLUE.index);
                    richString.applyFont(font3);
                    cell.setCellValue(richString);
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        try{
            hssfWorkbook.write(out);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
    
    
    public void exportExcels(List<T> list, OutputStream out){
        //声明一个工作薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = hssfWorkbook.createSheet();
        //设置表格默认列宽度
        sheet.setDefaultColumnWidth(20);
        //生成一个样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //设置样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成字体
        HSSFFont font = hssfWorkbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        T x = list.get(0);
        for(int i = 0; i < x.getClass().getDeclaredFields().length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(x.getClass().getDeclaredFields()[i].getName());
            cell.setCellValue(text);
        }
        int index = 0;
        for(T t: list){
            index++;
            row = sheet.createRow(index);
            Field[] fields = t.getClass().getDeclaredFields();
            for(int i = 0; i < fields.length; i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try{
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName,
                            new Class[] {});
                    Object value = getMethod.invoke(t, new Object[] {});
                    String textValue;
                    if(value == null){
                        continue;
                    }
                    if(value instanceof Date){
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
                        textValue = sdf.format(date);
                    }else{
                        textValue = value.toString();
                    }
                    HSSFRichTextString richString = new HSSFRichTextString(textValue);
                    HSSFFont font3 = hssfWorkbook.createFont();
                    font3.setColor(HSSFColor.BLUE.index);
                    richString.applyFont(font3);
                    cell.setCellValue(richString);
                }catch (Exception e) {
//                    e.printStackTrace();
                }
            }
        }
        try{
            hssfWorkbook.write(out);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
}

非泛型硬编码的写法：

package client;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import mysql.mapper.StudentMapper;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import station.mapper.StationApplyMapper;

import excel.ExportExcel;

import Student.StationApply;
import Student.StationApplyExample;
import Student.Student;
import Student.StudentExample;

public class PoiDemo {
    
    public static void main(String[] args) throws IOException{
        long t1 = System.currentTimeMillis();
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-dao.xml");
        StationApplyMapper stationApplyMapper = (StationApplyMapper) ctx.getBean("stationApplyMapper");
        StationApplyExample stationApplyExample = new StationApplyExample();
        List<StationApply> list = stationApplyMapper.selectByExample(stationApplyExample);
        OutputStream out = new FileOutputStream("D://a.xls");
//        new ExportExcel<Student>().exportExcel("test", headers, list, out);
//        new ExportExcel<StationApply>().exportExcels(list, out);
        exportExcels(list, out);
        out.close();
        System.out.println("success!");
        long t2 = System.currentTimeMillis();
        System.out.println(t2 - t1);
    }
    
    public static  void exportExcels(List<StationApply> list, OutputStream out){
        //声明一个工作薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = hssfWorkbook.createSheet();
        //设置表格默认列宽度
        sheet.setDefaultColumnWidth(20);
        //生成一个样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //设置样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成字体
        HSSFFont font = hssfWorkbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        StationApply x = list.get(0);
        for(int i = 0; i < x.getClass().getDeclaredFields().length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(x.getClass().getDeclaredFields()[i].getName());
            cell.setCellValue(text);
        }
        int index = 0;
        for(StationApply t: list){
            if(t == null){
                continue;
            }
            index++;
            row = sheet.createRow(index);
            HSSFCell cell = row.createCell(0);
//            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
            try{
                HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(t.getId()));
                cell.setCellValue(richString);
                cell = row.createCell(1);
                richString = new HSSFRichTextString(String.valueOf(t.getGmtCreate()));
                cell.setCellValue(richString);
                cell = row.createCell(2);
                richString = new HSSFRichTextString(String.valueOf(t.getGmtModified()));
                cell.setCellValue(richString);
                cell = row.createCell(3);
                richString = new HSSFRichTextString(t.getCreator());
                cell.setCellValue(richString);
                cell = row.createCell(4);
                richString = new HSSFRichTextString(t.getModifier());
                cell.setCellValue(richString);
                cell = row.createCell(5);
                richString = new HSSFRichTextString(t.getIsDeleted());
                cell.setCellValue(richString);
                cell = row.createCell(6);
                richString = new HSSFRichTextString(t.getIsDeleted());
                cell.setCellValue(richString);
                cell = row.createCell(7);
                richString = new HSSFRichTextString(t.getName());
                cell.setCellValue(richString);
                cell = row.createCell(8);
                richString = new HSSFRichTextString(t.getState());
                cell.setCellValue(richString);
                cell = row.createCell(9);
                richString = new HSSFRichTextString(t.getApplierName());
                cell.setCellValue(richString);
                cell = row.createCell(10);
                richString = new HSSFRichTextString(t.getIdenNum());
                cell.setCellValue(richString);
                cell = row.createCell(11);
                richString = new HSSFRichTextString(t.getMobile());
                cell.setCellValue(richString);
                cell = row.createCell(12);
                richString = new HSSFRichTextString(t.getCovered());
                cell.setCellValue(richString);
                cell = row.createCell(13);
                richString = new HSSFRichTextString(t.getProducts());
                cell.setCellValue(richString);
                cell = row.createCell(14);
                richString = new HSSFRichTextString(t.getLogisticsState());
                cell.setCellValue(richString);
                cell = row.createCell(15);
                richString = new HSSFRichTextString(t.getDescription());
                cell.setCellValue(richString);
                cell = row.createCell(16);
                richString = new HSSFRichTextString(t.getFormat());
                cell.setCellValue(richString);
                cell = row.createCell(17);
                richString = new HSSFRichTextString(t.getAlipayAccount());
                cell.setCellValue(richString);
                cell = row.createCell(18);
                richString = new HSSFRichTextString(t.getTaobaoNick());
                cell.setCellValue(richString);
                cell = row.createCell(19);
                richString = new HSSFRichTextString(String.valueOf(t.getStationId()));
                cell.setCellValue(richString);
                cell = row.createCell(20);
                richString = new HSSFRichTextString(String.valueOf(t.getOwnOrgId()));
                cell.setCellValue(richString);
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        try{
            hssfWorkbook.write(out);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
}
}

测试耗时2s左右 测试数据10000条记录 每条记录20个字段

web实例http://www.cnblogs.com/xwdreamer/archive/2011/07/20/2296975.html

转载于:https://www.cnblogs.com/tuifeideyouran/p/4630312.html