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;
}
}
转载于:https://www.cnblogs.com/DajiangDev/p/3421428.html
相关资源：jxl(jExcelAPI)的基本使用方法
