作用:通过jxl包生成excel文件。示例请看main方法
特点:
1、通过java的反射特性,将jxl生成excel的逻辑,和业务数据解耦。在LinkedHashMap参数中定义每一列的标题以及对应的javabean属性,生成excel时,就会根据map插入的先后顺序,依次在excel中添加列,每列的标题为map的value值,内容为对应的javabean属性。
2、通过一个map,可以很方便地配置excel中每列的内容和顺序
特点:
1、通过java的反射特性,将jxl生成excel的逻辑,和业务数据解耦。在LinkedHashMap参数中定义每一列的标题以及对应的javabean属性,生成excel时,就会根据map插入的先后顺序,依次在excel中添加列,每列的标题为map的value值,内容为对应的javabean属性。
2、通过一个map,可以很方便地配置excel中每列的内容和顺序
3、优化显示。数字用千分位格式,且右对齐;时间类型的值,转成标准的yyyy-MM-dd HH:mm:ss形式;其他表格内容居中显示;标题粗体;表格根据宽度自适应显示
package mqq.sdet.rdm.common.util;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import jxl.CellView;
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.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExportExcelUtil
{
public static final int RESULT_SUCC = 0;
public static final int RESULT_FAIL = -1;
public static final String TYPE_YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
/**
* 将数据转成成excel。 特性: 1、将时间类型的值转成yyyy-MM-dd HH:mm:ss 2、将数字类型的值转成带千分符的形式,并右对齐
* 3、除数字类型外,其他类型的值居中显示
*
* @param keyMap
* 定义标题及每一列对应的JavaBean属性。标题的先后顺序,对应keyMap的插入顺序;
* map中的key值为JavaBean属性,value为标题
* @param listContent
* 表格内容,List中的每一个元素,对应到excel的每一行
* @param os
* 结果输出流
* @return
*/
public final int export(LinkedHashMap<String, String> keyMap, List<Object> listContent, OutputStream os)
{
int rs = RESULT_SUCC;
try
{
// 创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(os);
// 创建名为sheet1的工作表
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
// 设置字体
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 12);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
// 标题居中
WritableCellFormat titleFormat = new WritableCellFormat(BoldFont);
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
titleFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐
titleFormat.setWrap(false); // 文字是否换行
// 正文居中
WritableCellFormat contentCenterFormat = new WritableCellFormat(NormalFont);
contentCenterFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
contentCenterFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
contentCenterFormat.setAlignment(Alignment.CENTRE);
contentCenterFormat.setWrap(false);
// 正文右对齐
WritableCellFormat contentRightFormat = new WritableCellFormat(NormalFont);
contentRightFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
contentRightFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
contentRightFormat.setAlignment(Alignment.RIGHT);
contentRightFormat.setWrap(false);
// 设置标题,标题内容为keyMap中的value值,标题居中粗体显示
Iterator titleIter = keyMap.entrySet().iterator();
int titleIndex = 0;
while (titleIter.hasNext())
{
Map.Entry<String, String> entry = (Map.Entry<String, String>) titleIter.next();
sheet.addCell(new Label(titleIndex++, 0, entry.getValue(), titleFormat));
}
// 设置正文内容
for (int i = 0; i < listContent.size(); i++)
{
Iterator contentIter = keyMap.entrySet().iterator();
int colIndex = 0;
int listIndex = 0;
while (contentIter.hasNext())
{
Map.Entry<String, String> entry = (Map.Entry<String, String>) contentIter.next();
Object key = entry.getKey();
Field field = listContent.get(i).getClass().getDeclaredField(key.toString());
field.setAccessible(true);
Object content = field.get(listContent.get(i));
String contentStr = null != content ? content.toString() : "";
WritableCellFormat cellFormat = contentCenterFormat;
// 将数字转变成千分位格式
String numberStr = getNumbericValue(contentStr);
// numberStr不为空,说明是数字类型。
if (null != numberStr && !numberStr.trim().equals(""))
{
contentStr = numberStr;
// 数字要右对齐
cellFormat = contentRightFormat;
}
else
{
// 如果是时间类型。要格式化成标准时间格式
String timeStr = getTimeFormatValue(field, content);
// timeStr不为空,说明是时间类型
if (null != timeStr && !timeStr.trim().equals(""))
{
contentStr = timeStr;
}
}
sheet.addCell(new Label(colIndex++, i + 1, contentStr, cellFormat));
}
}
// 宽度自适应。能够根据内容增加宽度,但对中文的支持不好,如果内容中包含中文,会有部分内容被遮盖
for (int i = 0; i < keyMap.size(); i++)
{
CellView cell = sheet.getColumnView(i);
cell.setAutosize(true);
sheet.setColumnView(i, cell);
}
workbook.write();
workbook.close();
}
catch (Exception e)
{
rs = RESULT_FAIL;
e.printStackTrace();
}
return rs;
};
/**
* 获取格式化后的时间串
*
* @param field
* @param content
* @return
*/
private String getTimeFormatValue(Field field, Object content)
{
String timeFormatVal = "";
if (field.getType().getName().equals(java.sql.Timestamp.class.getName()))
{
Timestamp time = (Timestamp) content;
timeFormatVal = longTimeTypeToStr(time.getTime(), TYPE_YYYY_MM_DD_HH_MM_SS);
}
else if (field.getType().getName().equals(java.util.Date.class.getName()))
{
Date time = (Date) content;
timeFormatVal = longTimeTypeToStr(time.getTime(), TYPE_YYYY_MM_DD_HH_MM_SS);
}
return timeFormatVal;
}
/**
* 获取千分位数字
*
* @param str
* @return
*/
private String getNumbericValue(String str)
{
String numbericVal = "";
try
{
Double doubleVal = Double.valueOf(str);
numbericVal = DecimalFormat.getNumberInstance().format(doubleVal);
}
catch (NumberFormatException e)
{
// if exception, not format
}
return numbericVal;
}
/**
* 格式化时间
*
* @param time
* @param formatType
* @return
*/
public String longTimeTypeToStr(long time, String formatType)
{
String strTime = "";
if (time >= 0)
{
SimpleDateFormat sDateFormat = new SimpleDateFormat(formatType);
strTime = sDateFormat.format(new Date(time));
}
return strTime;
}
public static class TestBean
{
private String strTest;
private int intTest;
private Timestamp timeTest;
public String getStrTest()
{
return strTest;
}
public void setStrTest(String strTest)
{
this.strTest = strTest;
}
public int getIntTest()
{
return intTest;
}
public void setIntTest(int intTest)
{
this.intTest = intTest;
}
public Timestamp getTimeTest()
{
return timeTest;
}
public void setTimeTest(Timestamp timeTest)
{
this.timeTest = timeTest;
}
}
public static void main(String[] args)
{
long start = System.currentTimeMillis();
List<Object> li = new ArrayList<Object>();
TestBean testBean = new TestBean();
testBean.setIntTest(8888);
testBean.setStrTest("88888.888");
testBean.setTimeTest(new Timestamp(System.currentTimeMillis()));
for (int i = 0; i < 1000; i++)
{
li.add(testBean);
}
LinkedHashMap<String, String> keyMap = new LinkedHashMap<String, String>();
keyMap.put("timeTest", "time类型");
keyMap.put("intTest", "int类型");
keyMap.put("strTest", "string类型");
OutputStream out;
try
{
ExportExcelUtil util = new ExportExcelUtil();
out = new FileOutputStream("d:/exportExcel/test28.xls");
util.export(keyMap, li, out);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}