package ebc.base.common;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
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.HSSFColor;
public class ExportExcelUtil {
private static Connection conn ;
private static String Driver = "org.gjt.mm.mysql.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/cmdb";
private static String username = "root";
private static String password = "root";
//表头
public static final String[] tableHeader =
{"品牌","所属公司","分公司","店铺编码","渠道","业务渠道",
"店铺成本中心","店铺名称","POS店铺名称","上级","店铺间数",
"开店时间","关店时间","状态","开/关店原因","备注","装修面积",
"城市","城市等级"};
//创建工作本
public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
//创建表
public static HSSFSheet demoSheet = demoWorkBook.createSheet("Sheet1");
//表头的单元格个数目
public static final short cellNumber = (short)tableHeader.length;
//数据库表的列数
public static final int columNumber = tableHeader.length;
/**
* 获得数据库连接
* @return conn
*/
public static Connection getConn(){
try {
Class.forName(Driver);
conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 查找结果集
* @return
* @throws SQLException
*/
public ResultSet selectAllDataFromDB() throws SQLException{
conn = getConn();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select J_BRAND,J_COM,J_DQ,J_DEPOTID,f_hzfs,B_CHANNEL,F_COSTCENTER," +
"j_realname,J_NAME,J_FORM,P_JS,F_BEGINDATE,F_ENDDATE," +
"J_FREEZE,f_kgdyy,f_bz,P_AREA,G_CS,G_CZ from storeinfo");
return rs;
}
/**
* 创建表头
* @return
*/
public void createTableHeader(){
// HSSFHeader header = demoSheet.getHeader();
// header.setCenter("大区信息表");
HSSFRow headerRow = demoSheet.createRow((short) 0);
for(int i = 0;i < cellNumber;i++){
HSSFCell headerCell = headerRow.createCell((short) i);
headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
headerCell.setCellValue(tableHeader[i]);
headerCell.setCellStyle(getTitleStyle());
}
}
/**
* 创建行
* @param cells
* @param rowIndex
*/
public void createTableRow(List<String> cells,short rowIndex){
//创建第rowIndex行
HSSFRow row = demoSheet.createRow((short) rowIndex);
row.setHeight((short) (15.625*20));
HSSFCellStyle style = getCellStyle();
for(short i = 0;i < cells.size();i++)
{
//创建第i个单元格
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(cells.get(i));
}
}
/**
* 创建整个Excel表
* @throws SQLException
*
*/
public void createExcelSheeet() throws SQLException{
createTableHeader();
ResultSet rs = selectAllDataFromDB();
int rowIndex = 1;
while(rs.next()){
List<String> list = new ArrayList<String>();
for(int i = 1;i <= columNumber;i++)
{
list.add(rs.getString(i));
}
createTableRow(list,(short)rowIndex);
rowIndex++;
}
}
/**
* 导出表格
* @param sheet
* @param os
* @return
* @throws IOException
*/
public void expord(OutputStream os) throws IOException{
demoSheet.setGridsPrinted(true);
HSSFFooter footer = demoSheet.getFooter();
footer.setRight("Page " + HSSFFooter.page() + " of " +
HSSFFooter.numPages());
demoWorkBook.write(os);
}
/**
* 设置头部样式
* @return HSSFCellStyle
*/
public HSSFCellStyle getTitleStyle(){
HSSFCellStyle style = demoWorkBook.createCellStyle();
HSSFFont font = demoWorkBook.createFont();
demoSheet.setColumnWidth((short)7, (short) 7000);
demoSheet.setColumnWidth((short)8, (short) 7000);
demoSheet.setColumnWidth((short)11, (short) 5000);
demoSheet.setColumnWidth((short)12, (short) 5000);
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);// 设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFillForegroundColor(HSSFColor.LAVENDER.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
style.setFont(font);
return style;
}
/**
* 设置表格格样式
* @return HSSFCellStyle
*/
public HSSFCellStyle getCellStyle(){
HSSFCellStyle style = demoWorkBook.createCellStyle();
HSSFFont font = demoWorkBook.createFont();
font.setFontHeightInPoints((short) 10);// 设置字体大小
style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setFont(font);
return style;
}
}
在rest类中调用ExportExcelUtil
package ebc.baseserver.rest;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.HeaderParam;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.UriInfo;
import ebc.base.common.ExportExcelUtil;
import ebc.base.wrapper.ResponseWrapper;
import ebc.base.wrapper.Wrapper;
@Path("/base/exportexcel")
public class ExportExcelResource {
@Context
ServletContext sc;
@Context
UriInfo uriInfo;
@Context
HttpServletRequest request;
@Context
HttpServletResponse response;
@GET
@Produces("application/json")
public Wrapper Export(@HeaderParam("Range") String range){
long time = System.currentTimeMillis();
ExportExcelUtil export = new ExportExcelUtil();
OutputStream out = null;
String filename = "店铺信息表.xsl";
ResultSet rs = null;
try{
export.createTableHeader();
filename = URLEncoder.encode(filename,"utf-8");//解决在IE中文件名乱码
rs = export.selectAllDataFromDB();
int rowIndex = 1;
while(rs.next()){
List<String> list = new ArrayList<String>();
for(int i = 1;i <= ExportExcelUtil.columNumber;i++)
{
list.add(rs.getString(i));
}
export.createTableRow(list,(short)rowIndex);
rowIndex++;
}
response.setContentType("application/ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"),"ISO-8859-1"));
out = response.getOutputStream();
export.expord(out);
ResponseWrapper result = new ResponseWrapper(this);
result.setCode("msg.001");
result.setType("success");
System.out.println("成功导出"+rowIndex+"条数据到"+filename+",共用时"+(System.currentTimeMillis()-time)/1000.0+"秒");
return result;
}catch(Exception e){
e.printStackTrace();
return new ResponseWrapper(e);
}finally{
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
最后备注下
excel导出后文件名乱码了。
解决办法：
response.setContentType("application/ms-excel");
String browser = request.getHeader("user-agent");
if(browser.indexOf("Firefox")!=-1){
response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"),"ISO-8859-1"));
}else{
filename = URLEncoder.encode(filename,"utf-8");
response.setHeader("Content-disposition", "attachment;filename="+filename);
}
out = response.getOutputStream();