File import and export code required
/**
* Copyright &copy; 2012-2014 <a href="/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import org.;
import org.;
import ;
import ;
import ;
import ;
import ;
import ;
/**
* Export Excel files (export "XLSX" format, supporting large-data export @see)
* @author ThinkGem
* @version 2013-04-21
*/
public class ExportExcel {
private static Logger log = ();
/**
* Workbook object
*/
private SXSSFWorkbook wb;
/**
* Worksheet Objects
*/
private Sheet sheet;
/**
* Style list
*/
private Map<String, CellStyle> styles;
/**
* Current line number
*/
private int rownum;
/**
* Annotation list (Object[]{ ExcelField, Field/Method })
*/
List<Object[]> annotationList = ();
/**
* Field map
*/
LinkedHashMap<String,String> fieldMap;
HashMap<String, String> dictTypes;
/**
* Constructor
* @param title table title, passing "empty value", indicating no title
* @param cls entity object, get the title
*/
public ExportExcel(String title, Class<?> cls){
this(title, cls, 1);
}
/**
* Constructor
* @param title table title, passing "empty value", indicating no title
* @param fieldMap, get the fields and field titles to export
*/
public ExportExcel(String title, LinkedHashMap<String,String> fieldMap){
= fieldMap;
dictTypes = new HashMap<String,String>();
// Initialize
int colunm = 0;
List<String> headerList = ();
for (String key : ()){
String t = (key);
HashMap<String, String> map = (t, ";", "=", false);
if(("name") != null){
t = ("name");
}
if(("dictType") != null){
(""+(colunm), ("dictType"));
}
colunm++;
(t);
}
initialize(title, headerList);
}
/**
* Constructor
* @param title table title, passing "empty value", indicating no title
* @param cls entity object, get the title
* @param type Export type (1: Export data; 2: Export template)
* @param groups Import grouping
*/
public ExportExcel(String title, Class<?> cls, int type, int... groups){
// Get annotation field
Field[] fs = ();
for (Field f : fs){
ExcelField ef = ();
if (ef != null && (()==0 || ()==type)){
if (groups!=null && >0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ()){
if (g == efg){
inGroup = true;
(new Object[]{ef, f});
break;
}
}
}
}else{
(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = ();
for (Method m : ms){
ExcelField ef = ();
if (ef != null && (()==0 || ()==type)){
if (groups!=null && >0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ()){
if (g == efg){
inGroup = true;
(new Object[]{ef, m});
break;
}
}
}
}else{
(new Object[]{ef, m});
}
}
}
// Field sorting
(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
// Initialize
List<String> headerList = ();
for (Object[] os : annotationList){
String t = ((ExcelField)os[0]).title();
// If it is export, remove the comment if (type==1){
String[] ss = (t, "**", 2);
if (==2){
t = ss[0];
}
}
(t);
}
initialize(title, headerList);
}
/**
* Constructor
* @param title table title, passing "empty value", indicating no title
* @param headers table header array
*/
public ExportExcel(String title, String[] headers) {
initialize(title, (headers));
}
/**
* Constructor
* @param title table title, passing "empty value", indicating no title
* @param headerList header list
*/
public ExportExcel(String title, List<String> headerList) {
initialize(title, headerList);
}
/**
* Initialization function
* @param title table title, passing "empty value", indicating no title
* @param headerList header list
*/
private void initialize(String title, List<String> headerList) {
= new SXSSFWorkbook(500);
= ("Export");
= createStyles(wb);
// Create title
if ((title)){
Row titleRow = (rownum++);
(30);
Cell titleCell = (0);
(("title"));
(title);
(new CellRangeAddress((),
(), (), ()-1));
}
// Create header
if (headerList == null){
throw new RuntimeException("headerList not null!");
}
Row headerRow = (rownum++);
(16);
for (int i = 0; i < (); i++) {
Cell cell = (i);
(("header"));
String[] ss = ((i), "**", 2);
if (==2){
(ss[0]);
Comment comment = ().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
(new XSSFRichTextString(ss[1]));
(comment);
}else{
((i));
}
(i);
}
for (int i = 0; i < (); i++) {
int colWidth = (i)*2;
(i, colWidth < 3000 ? 3000 : colWidth);
}
("Initialize success.");
}
/**
* Create table style
* @param wb workbook object
* @return style list
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = ();
(CellStyle.ALIGN_CENTER);
(CellStyle.VERTICAL_CENTER);
Font titleFont = ();
("Arial");
((short) 16);
(Font.BOLDWEIGHT_BOLD);
(titleFont);
("title", style);
style = ();
(CellStyle.VERTICAL_CENTER);
(CellStyle.BORDER_THIN);
(IndexedColors.GREY_50_PERCENT.getIndex());
(CellStyle.BORDER_THIN);
(IndexedColors.GREY_50_PERCENT.getIndex());
(CellStyle.BORDER_THIN);
(IndexedColors.GREY_50_PERCENT.getIndex());
(CellStyle.BORDER_THIN);
(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = ();
("Arial");
((short) 10);
(dataFont);
("data", style);
style = ();
(("data"));
(CellStyle.ALIGN_LEFT);
("data1", style);
style = ();
(("data"));
(CellStyle.ALIGN_CENTER);
("data2", style);
style = ();
(("data"));
(CellStyle.ALIGN_RIGHT);
("data3", style);
style = ();
(("data"));
// (true);
(CellStyle.ALIGN_CENTER);
(IndexedColors.GREY_50_PERCENT.getIndex());
(CellStyle.SOLID_FOREGROUND);
Font headerFont = ();
("Arial");
((short) 10);
(Font.BOLDWEIGHT_BOLD);
(());
(headerFont);
("header", style);
return styles;
}
/**
* Add a line
* @return line object
*/
public Row addRow(){
return (rownum++);
}
/**
* Add a cell
* @param row Added row
* @param column Add column number
* @param val Add value
* @return Cell object
*/
public Cell addCell(Row row, int column, Object val){
return (row, column, val, 0, );
}
/**
* Add a cell
* @param row Added row
* @param column Add column number
* @param val Add value
* @param align Align (1: left; 2: center; 3: right)
* @return Cell object
*/
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
Cell cell = (column);
CellStyle style = ("data"+(align>=1&&align<=3?align:""));
try {
if (val == null){
("");
} else if (val instanceof String) {
((String) val);
} else if (val instanceof Integer) {
((Integer) val);
} else if (val instanceof Long) {
((Long) val);
} else if (val instanceof Double) {
(new DecimalFormat(".#####").format(val));
} else if (val instanceof Float) {
((Float) val);
} else if (val instanceof Date) {
DataFormat format = ();
(("yyyy-MM-dd"));
(((Date)val));
} else {
if (fieldType != ){
((String)("setValue", ).invoke(null, val));
}else{
((String)(().getName().replaceAll(().getSimpleName(),
"fieldtype."+().getSimpleName()+"Type")).getMethod("setValue", ).invoke(null, val));
}
}
} catch (Exception ex) {
("Set cell value ["+()+","+column+"] error: " + ());
(());
}
(style);
return cell;
}
/**
* Add data (by adding data)
* @return list data list
*/
public <E> ExportExcel setDataList(List<E> list){
for (E e : list){
int colunm = 0;
Row row = ();
StringBuilder sb = new StringBuilder();
if(e instanceof Map){
@SuppressWarnings("unchecked")
Map<String,Object> map = (Map<String,Object>)e;
for(String key : ()){
Object value = (key);
String columnDictType = (colunm+"");
if ((columnDictType)){
value = (value==null?"":(), columnDictType, "");
}
(row, colunm++, value == null ? "" : (), 0, );
(value + ", ");
}
}
else{
for (Object[] os : annotationList){
ExcelField ef = (ExcelField)os[0];
Object val = null;
// Get entity value
try{
if ((())){
val = (e, ());
}else{
if (os[1] instanceof Field){
val = (e, ((Field)os[1]).getName());
}else if (os[1] instanceof Method){
val = (e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
}
}
// If is dict, get dict label
if ((())){
val = (val==null?"":(), (), "");
}
}catch(Exception ex) {
// Failure to ignore
(());
val = "";
}
(row, colunm++, val, (), ());
(val + ", ");
}
("Write success: ["+()+"] "+());
}
}
return this;
}
/**
* Output data stream
* @param os Output data stream
*/
public ExportExcel write(OutputStream os) throws IOException{
(os);
return this;
}
/**
* Output to client
* @param fileName Output file name
*/
public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
();
("application/octet-stream; charset=utf-8");
("Content-Disposition", "attachment; filename="+(fileName));
write(());
return this;
}
/**
* Output to file
* @param fileName Output file name
*/
public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
FileOutputStream os = new FileOutputStream(name);
(os);
return this;
}
/**
* Clean up temporary files
*/
public ExportExcel dispose(){
();
return this;
}
}
Export test
public static void main(String[] args) throws Throwable {
List<String> headerList = ();
for (int i = 1; i <= 10; i++) {
("Table"+i);
}
List<String> dataRowList = ();
for (int i = 1; i <= (); i++) {
("data"+i);
}
List<List<String>> dataList = ();
for (int i = 1; i <=1000000; i++) {
(dataRowList);
}
ExportExcel ee = new ExportExcel("Table Title", headerList);
for (int i = 0; i < (); i++) {
Row row = ();
for (int j = 0; j < (i).size(); j++) {
(row, j, (i).get(j));
}
}
("target/");
();
("Export success.");
}
/**
* Copyright &copy; 2012-2014 <a href="/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .;
import ;
import .;
import ;
import ;
import ;
import ;
import ;
import ;
import org.;
import org.;
import ;
import ;
import ;
import ;
import ;
/**
* Import Excel files (supports "XLS" and "XLSX" formats)
* @author ThinkGem
* @version 2013-03-10
*/
public class ImportExcel {
private static Logger log = ();
/**
* Workbook object
*/
private Workbook wb;
/**
* Worksheet Objects
*/
private Sheet sheet;
/**
* Title line number
*/
private int headerNum;
/**
* Constructor
* @param path Import file and read the first worksheet
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* Constructor
* @param path Import file object and read the first worksheet
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* Constructor
* @param path Import file
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @param sheetIndex Worksheet Number
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* Constructor
* @param path Import file object
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @param sheetIndex Worksheet Number
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this((), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* Constructor
* @param file import file object
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @param sheetIndex Worksheet Number
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this((), (), headerNum, sheetIndex);
}
/**
* Constructor
* @param path Import file object
* @param headerNum headerNum headerNum header number, data line number = title line number +1
* @param sheetIndex Worksheet Number
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if ((fileName)){
throw new RuntimeException("Import document is empty!");
}else if(().endsWith("xls")){
= new HSSFWorkbook(is);
}else if(().endsWith("xlsx")){
= new XSSFWorkbook(is);
}else{
throw new RuntimeException("The document format is incorrect!");
}
if (()<sheetIndex){
throw new RuntimeException("There is no worksheet in the document!");
}
= (sheetIndex);
= headerNum;
("Initialize success.");
}
/**
* Get the line object
* @param rownum
* @return
*/
public Row getRow(int rownum){
return (rownum);
}
/**
* Get the data line number
* @return
*/
public int getDataRowNum(){
return headerNum+1;
}
/**
* Get the last data line number
* @return
*/
public int getLastDataRowNum(){
return ()+headerNum;
}
/**
* Get the last column number
* @return
*/
public int getLastCellNum(){
return (headerNum).getLastCellNum();
}
/**
* Get cell value
* @param row Get row
* @param column Get cell column number
* @return Cell value
*/
public Object getCellValue(Row row, int column){
Object val = "";
try{
Cell cell = (column);
if (cell != null){
if (() == Cell.CELL_TYPE_NUMERIC){
val = ();
}else if (() == Cell.CELL_TYPE_STRING){
val = ();
}else if (() == Cell.CELL_TYPE_FORMULA){
val = ();
}else if (() == Cell.CELL_TYPE_BOOLEAN){
val = ();
}else if (() == Cell.CELL_TYPE_ERROR){
val = ();
}
}
}catch (Exception e) {
return val;
}
return val;
}
/**
* Get the imported data list
* @param cls import object type
* @param groups Import grouping
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
List<Object[]> annotationList = ();
// Get annotation field
Field[] fs = ();
for (Field f : fs){
ExcelField ef = ();
if (ef != null && (()==0 || ()==2)){
if (groups!=null && >0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ()){
if (g == efg){
inGroup = true;
(new Object[]{ef, f});
break;
}
}
}
}else{
(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = ();
for (Method m : ms){
ExcelField ef = ();
if (ef != null && (()==0 || ()==2)){
if (groups!=null && >0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ()){
if (g == efg){
inGroup = true;
(new Object[]{ef, m});
break;
}
}
}
}else{
(new Object[]{ef, m});
}
}
}
// Field sorting
(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
//("Import column count:"+());
// Get excel data
List<E> dataList = ();
for (int i = (); i < (); i++) {
E e = (E)();
int column = 0;
Row row = (i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
Object val = (row, column++);
if (val != null){
ExcelField ef = (ExcelField)os[0];
// If is dict type, get dict value
if ((())){
val = ((), (), "");
//("Dictionary type value: ["+i+","+colunm+"] " + val);
}
// Get param type and type cast
Class<?> valType = ;
if (os[1] instanceof Field){
valType = ((Field)os[1]).getType();
}else if (os[1] instanceof Method){
Method method = ((Method)os[1]);
if ("get".equals(().substring(0, 3))){
valType = ();
}else if("set".equals(().substring(0, 3))){
valType = ((Method)os[1]).getParameterTypes()[0];
}
}
//("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == ){
String s = (());
if((s, ".0")){
val = (s, ".0");
}else{
val = (());
}
}else if (valType == ){
val = (()).intValue();
}else if (valType == ){
val = (()).longValue();
}else if (valType == ){
val = (());
}else if (valType == ){
val = (());
}else if (valType == ){
val = ((Double)val);
}else{
if (() != ){
val = ().getMethod("getValue", ).invoke(null, ());
}else{
val = (().getName().replaceAll(().getSimpleName(),
"fieldtype."+()+"Type")).getMethod("getValue", ).invoke(null, ());
}
}
} catch (Exception ex) {
("Get cell value ["+i+","+column+"] error: " + ());
val = null;
}
// set entity value
if (os[1] instanceof Field){
(e, ((Field)os[1]).getName(), val);
}else if (os[1] instanceof Method){
String mthodName = ((Method)os[1]).getName();
if ("get".equals((0, 3))){
mthodName = "set"+(mthodName, "get");
}
(e, mthodName, new Class[] {valType}, new Object[] {val});
}
}
(val+", ");
}
(e);
("Read success: ["+i+"] "+());
}
return dataList;
}
}
Import tests
public static void main(String[] args) throws Throwable {
ImportExcel ei = new ImportExcel("target/", 1);
for (int i = (); i < (); i++) {
Row row = (i);
for (int j = 0; j < (); j++) {
Object val = (row, j);
(val+", ");
}
("\n");
}
}