Java export Excel file through XML, Java Excel Export tool class, Java Export Excel tool class

Java export Excel file through XML, Java Excel Export tool class, Java Export Excel tool class

==============================

? Copyright Sweet potato Yiu September 13, 2017

http://www.cnblogs.com/fanshuyao/

Directly on the code:

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;

import org.apache.commons.lang.StringUtils;

public class ExportUtil {

/ **
* Only export attributes included in includeFieldNames
* @param sheetName The name of the lower left corner of the table
* @param firstRowTitle The title that needs to be set in the first line, if it is empty, it will not be set
* @param list The data collection to be displayed
* @param headers table property column name array
* @param includeFieldNames included entity attributes
* @param widths The width of the column. If it is not set (Null), the default is set. The setting is set according to the corresponding column width (can be one, it can be the corresponding number, a shaped number, such as 200)
* @param outputStream The stream object associated with the output device can export the EXCEL document to a local file or network
* @param datetimePattern time format, when empty (Null or empty string), the default is yyyy-MM-dd HH: mm: ss
* @throws Exception
* /
@SuppressWarnings ({"unchecked", "rawtypes"})
public <T> void exportExcel (String sheetName, String firstRowTitle, List <T> list, String [] headers, String [] includeFieldNames, Integer [] widths,
ServletOutputStream outputStream, String datetimePattern) throws Exception {
The
// default output format
if (StringUtils.isBlank (datetimePattern)) {
datetimePattern = "yyyy-MM-dd HH: mm: ss";
}
The
// Create an excel application file
StringBuffer sb = new StringBuffer ();
sb.append ("<? xml version = \" 1.0 \ "?>");
sb.append ("\ n");
sb.append ("<? mso-application progid = \" Excel.Sheet \ "?>");
sb.append ("\ n");
sb.append ("<Workbook xmlns = \" urn: schemas-microsoft-com: office: spreadsheet \ "");
sb.append ("\ n");
sb.append ("xmlns: o = \" urn: schemas-microsoft-com: office: office \ "");
sb.append ("\ n");
sb.append ("xmlns: x = \" urn: schemas-microsoft-com: office: excel \ "");
sb.append ("\ n");
sb.append ("xmlns: ss = \" urn: schemas-microsoft-com: office: spreadsheet \ "");
sb.append ("\ n");
sb.append ("xmlns: html = \" http: //www.w3.org/TR/REC-html40 \ ">");
sb.append ("\ n");
The
sb.append ("<Styles> \ n");
The
/ * Set column header style * /
sb.append ("<Style ss: ID = \" header \ "ss: Name = \" header \ "> \ n"); // ss: ID = “header” corresponds to the following Row ss: StyleID = “header "
sb.append ("<Interior ss: Color = \" # c4d79b \ "ss: Pattern = \" Solid \ "/> \ n"); // Set the background color
sb.append ("<Font ss: FontName = \" Microsoft Yahei \ "x: CharSet = \" 134 \ "ss: Bold = \" Bolder \ "ss: Size = \" 12 \ "/> \ n" ); // Set font
sb.append ("</ Style> \ n");
The
/ * Other default style settings * /
sb.append ("<Style ss: ID = \" Default \ "ss: Name = \" Normal \ "> \ n");
//sb.append("<Alignment ss: Vertical = \ "Center \" /> \ n ");
sb.append ("<Alignment ss: Horizontal = \" Center \ "ss: Vertical = \" Center \ "ss: WrapText = \" 1 \ "/> \ n"); // left and middle right settings, one is Horizontal, one is vertical
sb.append ("<Borders> \ n");
sb.append ("<Border ss: Position = \" Left \ "ss: LineStyle = \" Continuous \ "ss: Color = \" # 666 \ "ss: Weight = \" 1 \ "/> \ n") ; // Left border setting
sb.append ("<Border ss: Position = \" Right \ "ss: LineStyle = \" Continuous \ "ss: Color = \" # 666 \ "ss: Weight = \" 1 \ "/> \ n") ; // Right border setting
sb.append ("<Border ss: Position = \" Bottom \ "ss: LineStyle = \" Continuous \ "ss: Color = \" # 666 \ "ss: Weight = \" 1 \ "/> \ n") ; // Bottom border setting
sb.append ("<Border ss: Position = \" Top \ "ss: LineStyle = \" Continuous \ "ss: Color = \" # 666 \ "ss: Weight = \" 1 \ "/> \ n") ; // Top border setting
sb.append ("</ Borders> \ n");
sb.append ("<Font ss: FontName = \" 宋体 \ "x: CharSet = \" 134 \ "ss: Size = \" 12 \ "/> \ n");
sb.append ("<Interior /> \ n");
sb.append ("<NumberFormat /> \ n");
sb.append ("<Protection /> \ n");
sb.append ("</ Style> \ n");
The
sb.append ("</ Styles> \ n");
The
try {
The
// Generate a table
int headersLength = headers.length;
The
sb.append ("<Worksheet ss: Name = \" "+ sheetName +" \ ">");
sb.append ("\ n");
sb.append ("<Table ss: ExpandedColumnCount = \" "+ headersLength
+ "\" ss: ExpandedRowCount = \ "1000000 \" x: FullColumns = \ "1 \" x: FullRows = \ "1 \"> ");
sb.append ("\ n");
The
if (! StrUtils.isEmptyArray (widths)) {
if (widths.length> 1) {
for (int i = 0; i <headersLength; i ++) {
sb.append ("<Column ss: AutoFitWidth = \" 0 \ "ss: Width = \" "+ widths [i] +" \ "/>");
}
} else {
for (int i = 0; i <headersLength; i ++) {
sb.append ("<Column ss: AutoFitWidth = \" 0 \ "ss: Width = \" "+ widths [0] +" \ "/>");
}
}
}
The
The
// output the title of the first line
if (! StrUtils.isBlank (firstRowTitle)) {
// ss: StyleID can add row or Cell, add it to Row, the whole row (including empty Cell) has, plus Cell, only Cell.
sb.append ("<Row ss: Height = \" 30 \ ">");
sb.append ("<Cell ss: StyleID = \" header \ "ss: MergeAcross = \" "+ (headersLength-1) +" \ "> <Data ss: Type = \" String \ ">" + firstRowTitle + "</ Data> </ Cell>");
sb.append ("</ Row>");
}
The
// output column header
sb.append ("<Row>");
for (int i = 0; i <headersLength; i ++) {
sb.append ("<Cell ss: StyleID = \" header \ "> <Data ss: Type = \" String \ ">" + headers [i] + "</ Data> </ Cell>");
}
sb.append ("</ Row>");
The
// Build table data
for (int j = 0; j <list.size (); j ++) {
The
sb.append ("<Row>");
The
T t = (T) list.get (j);
The
for (int i = 0; i <includeFieldNames.length; i ++) {
The
// Get the attribute name
String fieldName = includeFieldNames [i];
The
String getMethodName = "get" + fieldName.substring (0, 1) .toUpperCase () + fieldName.substring (1);
The
// Get the class object
Class tCls = t.getClass ();
The
// Get attribute value
Object value = null;
The
try {
// Get class method Method getMethod = tCls.getMethod (getMethodName, new Class [] {});
The
// Get attribute value
value = getMethod.invoke (t, new Object [] {});
The
} catch (NoSuchMethodException e) {
// continue to loop
continue;
}
The
// Force the type conversion after judging the value type
String textValue = "";
if (value instanceof Integer) {
// int value = ((Integer) value) .intValue ();
textValue = value.toString ();
} else if (value instanceof String) {
// String s = (String) value;
textValue = value.toString ();
} else if (value instanceof Double) {
// double d = ((Double) value) .doubleValue ();
textValue = String.format ("%. 2f", value);
} else if (value instanceof Float) {
// float f = ((Float) value) .floatValue ();
textValue = value.toString ();
} else if (value instanceof Long) {
// long l = ((Long) value) .longValue ();
textValue = value.toString ();
} else if (value instanceof Boolean) {
// boolean b = ((Boolean) value) .booleanValue ();
textValue = value.toString ();
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat (datetimePattern);
textValue = sdf.format (date);
} else if ((value instanceof BigDecimal)) {
textValue = value.toString ();
} else {
if (value! = null) {
continue;
}
}
The
sb.append ("<Cell> <Data ss: Type = \" String \ ">");
The
// If it is not image data, use regular expressions to determine whether textValue consists entirely of numbers
if (StringUtils.isNotBlank (textValue)) {
The
Pattern p = Pattern.compile ("^ // d + (//.// d +)? $");
Matcher matcher = p.matcher (textValue);
if (matcher.matches ()) {
// treat numbers as doubles
sb.append (Double.parseDouble (textValue));
} else {
sb.append (textValue);
}
The
}
The
sb.append ("</ Data> </ Cell>");
The
}
The
sb.append ("</ Row>");
sb.append ("\ n");
The
}
The
sb.append ("</ Table>");
sb.append ("<WorksheetOptions xmlns = \" urn: schemas-microsoft-com: office: excel \ ">");
sb.append ("\ n");
sb.append ("<ProtectObjects> False </ ProtectObjects>");
sb.append ("\ n");
sb.append ("<ProtectScenarios> False </ ProtectScenarios>");
sb.append ("\ n");
sb.append ("</ WorksheetOptions>");
sb.append ("\ n");
sb.append ("</ Worksheet>");
sb.append ("</ Workbook>");
sb.append ("\ n");
The
} catch (SecurityException e) {
e.printStackTrace ();
} catch (IllegalArgumentException e) {
e.printStackTrace ();
} catch (IllegalAccessException e) {
e.printStackTrace ();
} catch (InvocationTargetException e) {
e.printStackTrace ();
}
The
try {
outputStream.write (sb.toString (). getBytes ());
outputStream.flush ();
outputStream.close ();
sb = null;
} catch (IOException e) {
e.printStackTrace ();
} finally {
try {
outputStream.close ();
} catch (IOException e) {
e.printStackTrace ();
}
}
}

The

}


 

How to use:

@RequestMapping ("/ exportMsCard")
public void exportMsCard (HttpServletRequest req, HttpServletResponse res,
Integer pageIndex, Integer pageSize,
String cardNo, String mobile, String cinemaBaseName, Date startDate, Date endDate) {
try {
if (endDate! = null) {
endDate = DateUtils.dateAdd (endDate, 1, false);
}
List <MsCard> msCards = msCardService.list (pageIndex, pageSize,
cardNo, mobile, cinemaBaseName, startDate, endDate);
The
String [] headers = {"Member Card Number", "Member Nickname", "Mobile Number", "Cinema Name", "Creation Time"};
String [] includeFieldNames = {"cardNo", "nickname", "mobile", "cinemaBaseName", "createTime"};
// Set the file suffix and encode
String fileName = new String ("Operation platform-membership card package-membership card list. Xls" .getBytes ("UTF-8"), "iso8859-1");
// Set the encoding method of the response;
res.setCharacterEncoding ("gb2312");
res.setHeader ("Content-disposition", "attachment; filename =" + fileName);
res.setContentType ("application / msexcel; charset = UTF-8");
// Export order Excel
ExportUtil exportUtil = new ExportUtil ();
exportUtil.exportExcel ("sheet", "", msCards, headers, includeFieldNames, new Integer [] {200}, res.getOutputStream (), null);
} catch (Exception e) {
e.printStackTrace ();
}
}


 Note: You cannot directly request through Ajax, you need to submit through the form.

 

Attributes used by xml to Excel:



 

Add one:

ss: MergeAcross means cross-column merge, as follows:

// output the title of the first line
if (! StrUtils.isBlank (firstRowTitle)) {
// ss: StyleID can add row or Cell, add it to Row, the whole row (including empty Cell) has, plus Cell, only Cell.
sb.append ("<Row ss: Height = \" 30 \ ">");
sb.append ("<Cell ss: StyleID = \" header \ "ss: MergeAcross = \" "+ (headersLength-1) +" \ "> <Data ss: Type = \" String \ ">" + firstRowTitle + "</ Data> </ Cell>");
sb.append ("</ Row>");
}


 Pay attention to whether you need to subtract 1, because it means how many columns are spanned, that is to merge other columns, not including yourself, so you need to subtract 1

 

Universal method:

If you suddenly need to add some unknown attributes, you can first create an Excel file, make a template out, and then save it as an Xml file. Note that it is an Xml file. After saving, open the Xml file to see how some attributes of the template you created should be Settings. But the opened xml file is messy, so find the corresponding grid by searching.

 

See the source code download: http://fanshuyao.iteye.com/blog/2393131

 

=============================

? Copyright Sweet Potato Yao September 13, 2017

http://www.cnblogs.com/fanshuyao/

Java Export Excel file through Xml, Java Excel export tool class, Java export Excel tool class