Flex+java exporting data from the database to the Excel table (POI) in the specified directory

Data is written to Excel using the Apache POI:

A tool class in Java background (this tool class is useful for adding and facilitating different fields)

/* The following method is to convert the list to an Excel worksheet */
public static Hssfworkbook Getworkbook (list<exportable> List)
Throws Exception {
Hssfworkbook workbook = new Hssfworkbook ();
Hssfsheet sheet = workbook.createsheet ("Sheet1");
String[] ColumnNames;
String[] Columnmethods;

First, we read the first element in the list, which determines the column name of the worksheet and the method array that corresponds to the output data.
Exportable exp = list.get (0);
ColumnNames = Exp.getcolumnnames ();
Columnmethods = Exp.getcolumnmethods ();

Hssfrow row = sheet.createrow (0); Create line 1th, which is the output table header
Hssfcell cell;
for (int i = 0; i < columnnames.length; i++) {
Cell = Row.createcell (i); Create Column I
Cell.setcellvalue (New hssfrichtextstring (Columnnames[i]));
}

The following is the output of the data for each row
for (int i = 0; i < list.size (); i++) {
Exp = (exportable) list.get (i);
row = Sheet.createrow (i + 1);//Create Line I+1
for (int j = 0; J < Columnmethods.length; J + +) {
Cell = Row.createcell (j);//Create Column J
Method method;
method = Exp.getclass (). GetMethod (Columnmethods[j]); The reflection mechanism is used here to get the result object returned by the corresponding method by the method name.
Object obj = Method.invoke (exp);
if (obj! = null) {
Cell.setcellvalue (Obj.tostring ());
}
}
}
return workbook;
}

/**
* Convert Exportbean to Excel worksheet
*
* @param Exportbeans Collection, each element represents a sheet
* @return
* @throws Exception
*/
public static Hssfworkbook getworkbooks (Exportbean<?> ... exportbeans)
Throws Exception {

Hssfworkbook workbook = new Hssfworkbook ();

Hssfcellstyle CellStyle = Workbook.createcellstyle ();
Cellstyle.setdataformat (Hssfdataformat.getbuiltinformat ("M/d/yy h:mm"));

for (exportbean<?> Eb:exportbeans) {
Hssfsheet sheet = workbook.createsheet (Eb.getsheetname ());

string[] headers = eb.getheaders ();

Hssfrow row = sheet.createrow (0); Create line 1th, which is the output table header
Hssfcell cell;

Set table header bold style
Hssffont font = Workbook.createfont ();
Font.setboldweight (Hssffont.boldweight_bold);
Hssfcellstyle Cellstyleheader = Workbook.createcellstyle ();
Cellstyleheader.setfont (font);

for (int i = 0; i < headers.length; i++) {
Set column widths
Sheet.setcolumnwidth (short) I, 6000);
Cell = Row.createcell (i); Create Column I
Cell.setcellstyle (Cellstyleheader);

Cell.setcellvalue (New hssfrichtextstring (Headers[i]));
}

string[] GetMethods = Eb.getgetmethods ();
List rows = Eb.getrows ();

The following is the output of the data for each row
for (int i = 0; i < rows.size (); i++) {
Object RowData = Rows.get (i);
row = Sheet.createrow (i + 1);//Create Line I+1

for (int j = 0; J < Getmethods.length; J + +) {

Cell = Row.createcell (j);//Create Column J
Cell.setcellstyle (CellStyle);
method = null;
try {
method = Rowdata.getclass (). GetMethod (Getmethods[j]); The reflection mechanism is used here to get the result object returned by the corresponding method by the method name.
} catch (Exception e) {
Todo:handle exception
E.printstacktrace ();
}
Object obj = Method.invoke (RowData);
if (obj! = null) {
Cell.setcelltype (Hssfcell.);
if (obj instanceof java.util.Date) {
Cell.setcellvalue ((java.util.Date) obj);
Cell.setcellstyle (CellStyle);
} else if (obj instanceof BigDecimal) {
Cell.setcellvalue (((BigDecimal) obj). Longvalue ());
} else {
Cell.setcellvalue (Obj.tostring ());
}
}
}
}

}
return workbook;
}

Write Workbook to InputStream
public static InputStream Workbook2inputstream (Hssfworkbook workbook,
Bytearrayoutputstream BAOs, String fileName) throws Exception {
Workbook.write (BAOs);
Baos.flush ();
byte[] AA = Baos.tobytearray ();
InputStream Excelstream = new Bytearrayinputstream (AA, 0, aa.length);
return excelstream;
}

Writes workbook to a byte array
public static byte[] Workbook2bytearray (Hssfworkbook workbook,
Bytearrayoutputstream BAOs) throws Exception {
Workbook.write (BAOs);
Baos.flush ();
byte[] AA = Baos.tobytearray ();
return AA;
}

/**
* Take into account the multi-sheet situation temporarily add a data structure to encapsulate it
* @author Wang Yunshu
* Time 2014-7-16 5:30:23
*/
public class Exportbean<t> {

Private String SheetName; Sheet name

Private string[] headers; Table header
Private string[] GetMethods; Method Collection
Private list<t> rows; Data rows

Public string[] Getgetmethods () {
return getmethods;
}
public void Setgetmethods (string[] getmethods) {
This.getmethods = GetMethods;
}
Public String Getsheetname () {
return sheetname;
}
public void Setsheetname (String sheetname) {
This.sheetname = SheetName;
}

Public string[] Getheaders () {
return headers;
}
public void Setheaders (string[] headers) {
This.headers = headers;
}
Public list<t> getRows () {
return rows;
}
public void Setrows (list<t> rows) {
This.rows = rows;
}
}

Java code:

public void Dbtoexcels () {

byte[] bytes = NULL;

try {

exportbean<circuit> EB = new Exportbean<circuit> ();
Eb.setheaders (new string[] {"id", "First name", "Time"});
Eb.setgetmethods (new string[] {"GetId", "Getcircuit_type", "getsystem_id"});
list<circuit> list = Circuitbo.findall ();
Eb.setrows (list);
Eb.setsheetname ("Information");
Hssfworkbook Workbook = Poiutil.getworkbooks (EB);
Bytearrayoutputstream BAOs = new Bytearrayoutputstream ();
bytes = Poiutil.workbook2bytearray (workbook, BAOs);

} catch (Exception e) {
E.printstacktrace ();
Log.error ("Failed to export data", e);
}
return bytes;
}

　　//flex Code :

Alert.show ("Do you want to save the file! "," hint ", alert.yes| Alert.no,null,function (evt) {
if (Evt.detail = = Alert.yes) {
var fileref:filereference = new Filereference ();
Fileref.save (Data.result, "information sheet. xls");
}
});

Flex+java exporting data from the database to the Excel table (POI) in the specified directory