使用JExcel导出excel文件

package org.aaa.portal.tools;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class ExcelUtils {
    public static final String COLUMN_KEY="key";    //列的键,对应JSONObject的key
    public static final String COLUMN_TEXT="text";    //列头名称
    /**
     * 把JSONArray存储为Excel
     * @param fileName    完整文件路径名称
     * @param sheetName    sheet 名称
     * @param ja JSONArray
     * @param columns    
     *     List<Map<String, String>> 按照list Item的顺序生成列
     *    Map格式:        
             Map<String,String> column1 =new HashMap<String,String>();
            column1.put(ExcelUtils.COLUMN_KEY, "sUserName");
            column1.put(ExcelUtils.COLUMN_TEXT, "用户名");
            columns.add(column1);
     */
    public static boolean exportFromJSONArray(String fileName, String sheetName,JSONArray ja,
            List<Map<String, String>> columns) {
        boolean isSuccess=true;
        WritableWorkbook book;
        try {
            //确保保存文件目录存在
            String filePathStr=fileName.substring(0,fileName.lastIndexOf(File.separatorChar));
            File filePath=new File(filePathStr);
            if(!filePath.exists()){
                filePath.mkdirs();
            }
            //确保保存文件存在
            File file=new File(fileName);
            if(!file.exists()){
                file.createNewFile();
            }
            // 在指定地址创建EXCEL表
            book = Workbook.createWorkbook(file);
            // 设置第一个工作薄的名字
            WritableSheet sheet = book.createSheet(sheetName, 0);

            // 设置列头
            for (int i = 0; i < columns.size(); i++) {
                // 设置列宽
                sheet.setColumnView(i, 20);
                // 设置列头样式
                // 分别设置表头,表元的格式
                WritableFont fontb = new WritableFont(WritableFont.ARIAL, 8,
                        WritableFont.BOLD, false); // 粗体
                WritableCellFormat formatb = new WritableCellFormat(fontb);
                formatb.setAlignment(Alignment.CENTRE); // 水平居中
                formatb.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
                formatb.setBorder(Border.ALL, BorderLineStyle.THIN); // 边框
                // 填充列名
                Label labelb = new Label(i, 0, columns.get(i).get("text"),
                        formatb);
                sheet.addCell(labelb);
            }


            //设置数据单元格格式
            WritableFont fontc = new WritableFont(WritableFont.ARIAL, 8,
                    WritableFont.NO_BOLD, false);
            WritableCellFormat formatc = new WritableCellFormat(fontc);
            formatc.setAlignment(Alignment.CENTRE);
            formatc.setVerticalAlignment(VerticalAlignment.CENTRE);
            formatc.setBorder(Border.ALL, BorderLineStyle.THIN);
            // 填充数据
            for (int i = 0; i < ja.size(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                // 按照预设的列顺序填充
                for (int j = 0; j < columns.size(); j++) {
                    String key = columns.get(j).get("key");
                    Object text = jo.get(key);
                    // 如果是数字,则按照数字的格式填充
                    if (text != null && text.toString().matches("\\d+")) {
                        Number numberc = new Number(j, i+1, Long.parseLong(text.toString()),
                                formatc);
                        sheet.addCell(numberc);
                    } else {
                        //其余全按文字处理
                        Label labelb = new Label(j, i+1, text.toString(), formatc);
                        sheet.addCell(labelb);
                    }
                }
            }

            
            book.write();
            book.close();
        } catch (IOException e) {
            e.printStackTrace();
            isSuccess=false;
        } catch (RowsExceededException e) {
            e.printStackTrace();
            isSuccess=false;
        } catch (WriteException e) {
            e.printStackTrace();
            isSuccess=false;
        }
        return isSuccess;
    }
}

 

更多相关文章
一周排行