Java Export XLS

Package Com.spring.mvc.xls;

Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.text.DecimalFormat;
Import Java.util.Date;
Import Java.util.HashMap;
Import Java.util.Iterator;
Import Java.util.Map;
Import java.util.Properties;
Import Java.util.Set;

Import Org.apache.commons.logging.Log;
Import org.apache.commons.logging.LogFactory;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import Org.apache.poi.hssf.usermodel.HSSFCellStyle;
Import Org.apache.poi.hssf.usermodel.HSSFDataFormat;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import org.apache.poi.hssf.util.Region;
Import Org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
* Function Description: Excel manipulating Class

* @date: 2013-4-13
*/
public class Exceltemplate {
private static Log logger = Logfactory.getlog (Exceltemplate.class);
private static final String DATAS = "DATAS";

Private Hssfworkbook Workbook;
Private Hssfsheet sheet;
Private Hssfrow CurrentRow;
Private Map styles = new HashMap (); Default style configuration for data rows
Private Map confstyles = new HashMap (); To identify a style configuration by setting "#STYLE_XXX"
private int initrow; Data output start line
private int initcol; Data Output Start column
private int num; Index number
private int currentcol; Current column
private int currentrowindex; Current row index
private int rowheight = 22; Row height
private int lastlownum = 0;
Private String cellstyle = null;

Private Exceltemplate () {
}

/**
* Create Exceltemplate objects with default templates
* @return Exceltemplate objects that have been initialized according to the template
*/
Public Exceltemplate newinstance () {
Return newinstance ("Templates/default.xls");
}

/**
* Specify template to create Exceltemplate object
* @param templates Template Name
* @return Exceltemplate objects that have been initialized according to the template
*/
public static exceltemplate newinstance (String templates) {
try {
Exceltemplate Excel = new Exceltemplate ();
Poifsfilesystem fs = new Poifsfilesystem (new FileInputStream (New File (templates));
Excel.Workbook = new Hssfworkbook (FS);
Excel.Sheet = excel.workbook.getSheetAt (0);
Find configuration
Excel.initconfig ();
Find other style configurations
Excel.readcellstyles ();
Delete a configuration line
Excel.sheet.removeRow (Excel.sheet.getRow (Excel.initrow));
return Excel;
} catch (Exception e) {
E.printstacktrace ();
Logger.trace ("An exception occurred creating an Excel object", E);
throw new RuntimeException ("an exception occurred in creating an Excel object");
}
}


/**
* Set a specific cell style that can be reached by defining "#STYLE_XX" in the template file, such as:
* #STYLE_1, the parameter passed in is "Style_1"
* @param style
*/
public void Setcellstyle (String style) {
CellStyle = style;
}

/**
* Cancel a specific cell format and restore the default configuration value, which is the value of the Datas row
*/
public void Setcelldefaultstyle () {
CellStyle = null;
}

/**
* Create a new line
* @param index count starting from 0
*/
public void CreateRow (int index) {
If there is a subsequent row in the area where the data is currently inserted, move the row that follows it backward
if (Lastlownum > Initrow && index > 0) {
Sheet.shiftrows (index + initrow, Lastlownum + index,1,true,true);
}
CurrentRow = Sheet.createrow (index + initrow);
Currentrow.setheight ((short) rowheight);
Currentrowindex = index;
Currentcol = Initcol;
}
Public Hssfcell Getcell (int row,int column) {
Hssfcell Cell=null;
try{
Cell=sheet.getrow (Row). Getcell (column);
}catch (Exception e) {}
return cell;
}
/**
* Creates a new column on the current line based on the string value passed in
* @param value (string) of the Value column
*/
public void Createcell (String value) {
Hssfcell cell = Createcell ();
Cell.setcelltype (hssfcell.cell_type_string);
Cell.setcellvalue (value);
}
public void Createcell (float value) {
Hssfcell cell = Createcell ();
Hssfcellstyle Cstyle = (hssfcellstyle) confstyles.get (CellStyle);
if (Null==cstyle) {
Cstyle=workbook.createcellstyle ();
Confstyles.put (Cellstyle,cstyle);
}
Cstyle.setdataformat (Hssfdataformat.getbuiltinformat ("0.00"));
Cell.setcellstyle (Cstyle);
Cell.setcelltype (Hssfcell.cell_type_numeric);
DecimalFormat df=new DecimalFormat ("#0.00");
Cell.setcellvalue (Double.parsedouble (Df.format (value)));
}
public void Createcell (double value) {
Hssfcell cell = Createcell ();
Hssfcellstyle Cstyle = (hssfcellstyle) confstyles.get (CellStyle);
if (Null==cstyle) {
Cstyle=workbook.createcellstyle ();
Confstyles.put (Cellstyle,cstyle);
}
Cstyle.setdataformat (Hssfdataformat.getbuiltinformat ("0.00"));
Cell.setcellstyle (Cstyle);
Cell.setcelltype (Hssfcell.cell_type_numeric);
DecimalFormat df=new DecimalFormat ("#0.00");
Cell.setcellvalue (Double.parsedouble (Df.format (value)));
}
public void Createdoublecell (double value) {
Hssfcell cell = Createcell ();
Hssfcellstyle Cstyle = (hssfcellstyle) confstyles.get (CellStyle);
if (Null==cstyle) {
Cstyle=workbook.createcellstyle ();
Confstyles.put (Cellstyle,cstyle);
}
Cell.setcelltype (Hssfcell.cell_type_numeric);
Cell.setcellvalue (value);
}
public void Createpercentagecell (double value) {
Hssfcellstyle CellStyle = Workbook.createcellstyle ();
Cellstyle.setdataformat (Hssfdataformat.getbuiltinformat ("0%"));

Hssfcell cell = Createcell ();
Hssfcellstyle Cstyle = (hssfcellstyle) confstyles.get (CellStyle);
if (Null==cstyle) {
Cstyle=workbook.createcellstyle ();
Confstyles.put (Cellstyle,cstyle);
}

Cell.setcellstyle (CellStyle);
Cell.setcelltype (Hssfcell.cell_type_numeric);
Cell.setcellvalue (value);
}
public void Createnumbercell (Integer value) {
Hssfcell cell = Createcell ();
Cell.setcelltype (Hssfcell.cell_type_numeric);
Cell.setcellvalue (value);
}
public void Createnumbercell (Long value) {
Hssfcell cell = Createcell ();
Cell.setcelltype (Hssfcell.cell_type_numeric);
Cell.setcellvalue (value);
}
/**
* Create a new column on the current line based on the date value passed in
* In this case (incoming date), you can define the corresponding column in the template
* Date format, so you can flexibly control the date format of the output through the template
* @param Value Date
*/
public void Createcell (Date value) {
Hssfcell cell = Createcell ();
Cell.setcellvalue (value);
}

/**
* Creates a sequence number column for the current row, usually at the beginning of a line
* Note To use this method, you must call the Initpagenumber method before creating the line
*/
public void Createserialnumcell () {
Hssfcell cell = Createcell ();
Cell.setcellvalue (Currentrowindex + num);
}

@SuppressWarnings ("deprecation")
Private Hssfcell Createcell () {
Hssfcell cell = Currentrow.createcell ((short) currentcol++);
Cell.setencoding (HSSFCELL.ENCODING_UTF_16);
Hssfcellstyle style = (Hssfcellstyle) styles.get (New Integer (Cell.getcellnum ()));
if (style! = null) {
Cell.setcellstyle (style);
}

Set a specific format
if (CellStyle! = null) {
Hssfcellstyle ts = (hssfcellstyle) confstyles.get (CellStyle);
if (ts! = null) {
Cell.setcellstyle (TS);
}
}
return cell;
}

/**
* Get an instance of the current Hssfworkbook
* @return
*/
Public Hssfworkbook Getworkbook () {
return workbook;
}

/**
* Gets the cell style defined in the template, if not defined, returns an empty
* Style name defined by @param style template
* @return template defines the style of the cell and returns null if there is no definition
*/
Public Hssfcellstyle Gettemplatestyle (String style) {
Return (Hssfcellstyle) confstyles.get (style);
}

/**
* Replace the text parameters in the template
* Parameters start with "#"
* @param props
*/
public void ReplaceParameters (Properties props) {
if (props = = NULL | | props.size () = = 0) {
Return
}
Set propsets = Props.entryset ();
Iterator Rowit = Sheet.rowiterator ();
while (Rowit.hasnext ()) {
Hssfrow row = (Hssfrow) rowit.next ();
if (row = = null) continue;
int celllength = Row.getlastcellnum ();
for (int i=0; i<celllength; i++) {
Hssfcell cell = (Hssfcell) Row.getcell ((short) i);
if (cell = = null) continue;
String value = Cell.getstringcellvalue ();
if (value! = null && value.indexof ("#")! =-1) {
for (Iterator iter = Propsets.iterator (); Iter.hasnext ();) {
Map.entry Entry = (map.entry) iter.next ();
Value = Value.replaceall ("#" +entry.getkey (), (String) Entry.getvalue ());
}
}
Cell.setencoding (HSSFCELL.ENCODING_UTF_16);
Cell.setcellvalue (value);
}
}
}

/**
* Initialize Excel Configuration
*/
private void Initconfig () {
Lastlownum = Sheet.getlastrownum ();
Iterator Rowit = Sheet.rowiterator ();
Boolean configfinish = false;
while (Rowit.hasnext ()) {
if (configfinish) {
Break
}
Hssfrow row = (Hssfrow) rowit.next ();
if (row = = null) continue;
int celllength = Row.getlastcellnum ();
int rownum = Row.getrownum ();
for (int i=0; i<celllength; i++) {
Hssfcell cell = (Hssfcell) Row.getcell ((short) i);
if (cell = = null) continue;
String config = Cell.getstringcellvalue ();
if (datas.equalsignorecase (config)) {
The bank is the data start line and the style configuration line, need to read the corresponding configuration information
Initrow = Row.getrownum ();
RowHeight = Row.getheight ();
Initcol = Cell.getcellnum ();
Configfinish = true;
}
if (configfinish) {
Readcellstyle (cell);
}
}
}
}

/**
* Read the cell's style
* @param cell
*/
@SuppressWarnings ("Unchecked")
private void Readcellstyle (Hssfcell cell) {
Hssfcellstyle style = Cell.getcellstyle ();
if (style = = null) return;

Styles.put (New Integer (Cell.getcellnum ()), style);
}

/**
* Read the style configuration of other cells in the template
*/
private void Readcellstyles () {
Iterator Rowit = Sheet.rowiterator ();
while (Rowit.hasnext ()) {
Hssfrow row = (Hssfrow) rowit.next ();
if (row = = null) continue;
int celllength = Row.getlastcellnum ();
for (int i=0; i<celllength; i++) {
Hssfcell cell = (Hssfcell) Row.getcell ((short) i);
if (cell = = null) continue;
String value = Cell.getstringcellvalue ();
if (value! = null && value.indexof ("#STYLE_")! =-1) {
Hssfcellstyle style = Cell.getcellstyle ();
if (style = = null) continue;
Confstyles.put (value.substring (1), style);
Remove it
Row.removecell (cell);
}
}
}
}
public void SetValue (int rownum,int colnum,string value) {
Hssfrow Row=this.sheet.getrow (RowNum);
Hssfcell Cell=row.getcell (short) colnum);
if (cell==null) {
Cell = Row.createcell ((short) colnum);
}
Cell.setencoding (HSSFCELL.ENCODING_UTF_16);
Cell.setcellvalue (value);
}
public void mergedregion (int rownum,int colnum,int cols,int rows) {
Sheet.addmergedregion (New Region (RowNum, (short) colnum,cols, (short) rows));
}
}

Encapsulating the Export XLS method

Package Com.spring.mvc.xls;

Import java.io.IOException;
Import java.util.List;

Import Javax.servlet.http.HttpServletRequest;
Import Javax.servlet.http.HttpServletResponse;

Import org.springframework.web.bind.annotation.RequestMapping;
Import Org.springframework.web.context.request.RequestContextHolder;
Import org.springframework.web.context.request.ServletRequestAttributes;

/**
* @description Function Description: Export XLS method
* @author Author: Zhou Zhiwei
* @param parameters:
* @createdate Build Date: 2014-8-1 3:38:52
* @projectname Project Name: SPRING_MVC
* @packageclass Package and class name: Com.spring.mvc.xls.Xls.java
*/
public class Xls {

/**
* @projectname Project Name: SPRING_MVC
* @packageclass Package and class name: Com.spring.mvc.xls.Xls.java
* @description Function Description: Export XLS method
* @author Author: Zhou Zhiwei
* @param parameters: @param response
* @param parameters: @param xls template Name
* @param parameters: Generate XLS title @param xlsname
* @param parameters: @param list result set
* @param parameters: @throws IOException
* @return return type: void
* @createdate Build Date: 2014-8-1 3:37:42
*/
@SuppressWarnings ("unused")
@RequestMapping (value = "AEE")

public static void Datawrite (HttpServletResponse response,string xls,string xlsname,list List) throws ioexception{
int count=0;
HttpServletRequest req = ((servletrequestattributes) requestcontextholder.getrequestattributes ()). GetRequest ();
@SuppressWarnings ("deprecation")
String Realpath = Req.getrealpath ("/static/template/" +xls+ ". xls");
Exceltemplate template =exceltemplate.newinstance (Realpath);
for (int i = 0; i < list.size (); i++) {
List tmp= (list) list.get (i);
Template.createrow (count);
Template.createnumbercell (integer.valueof (count+1));
for (int j = 1; J < Tmp.size ()-1; j + +) {
Template.createcell (Tmp.get (j). ToString ());
}
count++;
}
Response.reset ();
Response.setcontenttype ("APPLICATION/X-DOWNLOAD;CHARSET=GBK");
Response.setheader ("Content-disposition", "Attachment;filename=" +java.net.urlencoder.encode (Xlsname + ". xls", " UTF-8 "));
Template.getworkbook (). Write (Response.getoutputstream ());
Response.getoutputstream (). Flush ();
Response.getoutputstream (). Close ();
}
}

Called in the control layer

/**
* @projectname Project Name: SPRING_MVC
* @packageclass Package and class name: Com.spring.mvc.Test.java
* @description Function Description: Export XLS
* @author Author: Zhou Zhiwei
* @param parameters: @param response
* @param parameters: @throws Exception
* @return return type: void
* @createdate Build Date: 2014-8-1 3:45:54
*/
@RequestMapping (value = "xls")

public void xls (HttpServletResponse response) throws exception{
List List = new ArrayList ();
List=testservice.tu ();
Xls.datawrite (Response, "Expcontractlist", "Contract", list);
}

Java Export XLS