java操作excl导入导出oracle数据库啊解决思路

package com.test;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 生成excel的工具类，不用模板
 * @version 2.0
 * */
public class ExportExcel {
    
    private static ExportExcel bean = new ExportExcel();
    public static ExportExcel getBean() {
        return bean;
    }
    
    private FileOutputStream output;
    private WritableWorkbook book;
    
    /**
     * 导出excel生成文件
     * @param base 根目录(文件存放的目录)
     * @param fileName 文件名
     * @param fullDatas 要导出的数据(这里必须存放String类型的数组)
     * @param title 文件的标题(第一行)
     * @param coteWidths 每一列的宽度
     * @param titles 表头标题
     * @param numCote 需要数字格式化的列的索引
     * @return String 返回输出文件的物理路径
     * */
    @SuppressWarnings("unchecked")
    public String reportExcel(String base, String fileName, List fullDatas, 
            String title, int[] coteWidths, String[] titles, int[] numCote) {
        try {
            createFolder(base);//如果文件夹不存在就创建
            String filePath = base + fileName;//输出文件路径
            WritableCellFormat cellFormat = new WritableCellFormat();
            cellFormat.setAlignment(Alignment.CENTRE);//居中显示
            WritableSheet sheet = getExcel(filePath, title, coteWidths, titles);//获得工作簿
            if(null != sheet){
                for(int i = 0; i < fullDatas.size(); i++){
                    String[] fullData = (String[]) fullDatas.get(i);
                    if(fullData.length == coteWidths.length){//确保数据个数一致
                        for (int j = 0; j < fullData.length; j++) {
                            boolean isNumeric = false;//判断该列是否需要数字化格式
                            if(null != numCote && numCote.length > 0){
                                for(int c = 0; c < numCote.length; c++){
                                    if(j == numCote[c]){
                                        isNumeric = true;
                                        break;
                                    }
                                }
                            }
                            if(isNumeric){
                                Double numeric = Double.parseDouble(fullData[j]);
                                Number number = new Number(j, i + 2, numeric, cellFormat);
                                sheet.addCell(number);//数字格式化
                            }else {
                                Label label = new Label(j, i + 2, fullData[j], cellFormat);
                                sheet.addCell(label);//文本数据
                            }                    
                        }
                    }                
                }
            }            
            book.write();
            book.close();
            output.close();        
            return filePath;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // 生产excel工作簿
    private WritableSheet getExcel(String filePath, String title, int coteWidths[], String[] titles) {
        try {
            if(coteWidths.length != titles.length){
                return null;
            }
            output = new FileOutputStream(filePath);//文件输出流
            book = Workbook.createWorkbook(output); // 创建文件
            WritableSheet sheet = book.createSheet("sheet", 0); // 创建工作薄
            //标题样式
            WritableFont headFont = new WritableFont(
                    WritableFont.ARIAL, 15, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat headCellFormat = new WritableCellFormat(headFont);
            headCellFormat.setAlignment(Alignment.CENTRE);//居中显示
            //表头样式
            WritableFont titleFont = new WritableFont(
                    WritableFont.ARIAL, 11, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont);
            titleCellFormat.setAlignment(Alignment.CENTRE);
            // 设置标题
            sheet.mergeCells(0, 0, titles.length - 1, 0);//合并单元格
            Label label = new Label(0, 0, title ,headCellFormat);
            sheet.addCell(label);//标题
            // 设置表头
            for (int i = 0; i < coteWidths.length; i++) {
                sheet.setColumnView(i, coteWidths[i]); // 设置列的宽度
                Label label1 = new Label(i, 1, titles[i], titleCellFormat);
                sheet.addCell(label1);//表头标题
            }            
            return sheet;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    // 创建文件夹
    public void createFolder(String folderPath) {
        try {
            File folder = new File(folderPath);
            if (!folder.exists()) {
                folder.mkdirs();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        String base = "D:\\opt\\log\\", fileName = "student.xls", title= "学生成绩表";
        String[] titles = new String[]{ "学号", "姓名", "语文", "数学", "排名" };
        int[] coteWidths = new int[]{ 20, 30, 20, 20, 20 };
        int[] numCote = new int[]{ 0, 2, 3, 4 };
        List list = new ArrayList();
        for (int i = 1; i <= numCote.length; i++) {
            list.add(new String[]{ "201000" + i, i + "号学生", String.valueOf(100 - i), String.valueOf(95 - i), String.valueOf(i) });
        }
        System.out.println(ExportExcel.getBean().reportExcel(base, fileName, list, title, coteWidths, titles, numCote));
    }

}















 


 



 阅读




上一篇：用java统计代码行解决方案 下一篇：返回列表