Java Export XLS

Source: Internet
Author: User

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

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.