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

Source: Internet
Author: User
Tags reflection

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.