package com.jshop.action.tools;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.rmi.server.ExportException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Blank;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* @author "fliay" 把需要的数据转导出长exl
* @param topics
* excle的列名
* @param data
* excel接收的数据
* @param excelFileName
* 导出excel的路径
* @param tableName导出表的名字
*/
public class ExportXLS {
private static WorkbookSettings ws = new WorkbookSettings();;
private static String encoding;
public String getEncoding() {
return encoding;
}
public static void setEncoding(String encoding) {
encoding = encoding;
ws.setEncoding(encoding);
}
/**
* 将给定的数据导出为excel
*
* @param topics
* excel的列名
* @param data
* excel接收的数据
* @param exportPath
* 导出的excel 目的地
* @param tableName
* 要导出的表的名
*/
public static void exportToExcel(String[] topics, Map<Integer, List> data,
String excelFileName, String tableName) {
File excel = new File(excelFileName);
try {
WritableWorkbook workBook = Workbook.createWorkbook(excel, ws);
WritableSheet sheet = workBook.createSheet(tableName, 0);
addTopics(sheet, topics);
addData(sheet, data);
workBook.write();
workBook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
String[] topics={"email","phone"};
Map<Integer, List> data =new HashMap<Integer, List>();
String a="631233828@qq.com";
String b="sfsf@qq.com";
String a1="456789";
String b1="123456";
List list1=new ArrayList();
List list2=new ArrayList();
list1.add(a);
list1.add(a1);
list2.add(b);
list2.add(b1);
data.put(1, list1);
data.put(2, list2);
ExportXLS e=new ExportXLS();
String fil="D://sss.xls";
String table="UserMessage";
e.exportToExcel(topics,data,fil,table);
}
/**
* 给单元表添加列
*
* @param sheet
* 单元表
* @param topics
* 列名
*/
private static void addTopics(WritableSheet sheet, String[] topics) {
for (int i = 0; i < topics.length; i++) {
WritableCell cell = new Label(i, 0, topics[i]);
try {
sheet.addCell(cell);
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private static void addData(WritableSheet sheet, Map<Integer, List> data) {
for (int i = 1; i <= data.size(); i++) {
List row = data.get(i);
int column = 0;
for (Object o : row) {
WritableCell cell = null;
if (o == null) {
cell = new Blank(column, i);
} else {
String ot = o.getClass().getSimpleName().trim();
if (ot.equalsIgnoreCase("Integer")) {
cell = new Number(column, i, (Integer) o);
} else if (ot.equalsIgnoreCase("Date")
|| ot.equalsIgnoreCase("Timestamp")) {
DateFormat customDateFormat = new DateFormat(
"yyyy-MM-dd hh:mm:ss");
WritableCellFormat dateFormat = new WritableCellFormat(
customDateFormat);
cell = new DateTime(column, i, (Date) o, dateFormat);
} else if (ot.equalsIgnoreCase("String")) {
cell = new Label(column, i, (String) o);
} else if (ot.equalsIgnoreCase("double")) {
cell = new Number(column, i, (Double) o);
} else if (ot.equalsIgnoreCase("long")) {
cell = new Number(column, i, (Long) o);
} else if (ot.equalsIgnoreCase("boolean")) {
cell = new Boolean(column, i,
((java.lang.Boolean) o).booleanValue());
}
}
try {
sheet.addCell(cell);
column++;
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
java生成exl文件
最新推荐文章于 2022-07-13 19:33:27 发布