JavaEE_POI匯出Excel (網路下載) (本地匯出) 工具類的編寫

JavaEE_POI匯出Excel  (網路下載)  (本地匯出) 工具類的編寫

博主這幾天忙活了報表資料用Excel匯出的功能:

這裡給出這個Demo的下載地址(MyEclipse2014 下開發,喜歡的朋友在文章下面評論 或給個贊)

http://pan.baidu.com/s/1hr9qcLY

涉及到了幾方面的知識,總結如下:

1.POI 匯出Excel

2.JavaEE 通過HttpResponseSevlet 實現檔案下載

3.Excel 檔名下載中文的顯示

http://blog.csdn.net/u010003835/article/details/50857611

1.POI 匯出Excel:

先講解步驟:

(1).匯入POI包 本文這裡用的是POI 3.9   連結   http://pan.baidu.com/s/1i4xAAjz

(2).POI 建立 Excel

  0.  建立工作本

	// 0.建立工作本
HSSFWorkbook excelWorkBook = new HSSFWorkbook();

  1.  建立Excel表

  excelName是指定當前Excel的表名

                // 1.建立表
HSSFSheet excelSheet = null;
if (this.excelName == null) {
this.excelName = new String();
}
excelSheet = excelWorkBook.createSheet(this.excelName);

  2.   建立表頭並設定表頭項

  表頭即第0行,其他的資料即從第1行開始,當然可以不建立表頭

// 2.建立表頭: 建立一行
HSSFRow headerRow = excelSheet.createRow((short) 0);
for (int i = 0; i < this.excelHeaders.length; i  ) {
// 建立一個單元格
HSSFCell headerCell = headerRow.createCell((short) i);
// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
// CellStyle cs = new CellStyle();
// 設定cell的值
headerCell.setCellValue(excelHeaders[i]);
}

 3.  根據查詢出來的值(JavaBean)設定單元格

	// 3.根據查詢出來的結果集results,填寫excel表格
if (results != null) {
T objectT = null;
for (int index = 0; index < results.size(); index  ) {
// 4.建立一行
HSSFRow tableRow = excelSheet.createRow((short) index   1); // 建立行,因為第一行是表頭,
// 即row(0)
objectT = results.get(index);
//this.selectedTableCells(tableRow, objectT);
// ********** selectedTableCell的大致實現 ***********
HSSFRow row = demoSheet.createRow((short) index);
for (short i = 0; i < cells.size(); i  ) {
// 建立第i個單元格
HSSFCell cell = row.createCell((short) i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(cells.get(i));
}
}
}

 4.  匯出Excel檔案,通過IO流

//判斷是下載到指定路徑,還是網路下載,
//本地下載,建立本地檔案流,
//否則,利用repsonse的檔案流
if(this.getResponse() == null){
OutputStream ioFileStream = null;
try {
ioFileStream = new FileOutputStream(this.filePath   this.fileName  ".xls");
excelWorkBook.write(ioFileStream);
ioFileStream.flush();
ioFileStream.close();
} catch (Exception e) {
e.printStackTrace();
}			
}

(3).一個完整的栗子(還用到了JDBC連線。。。,不熟悉的同學去網上找個栗子,需要JDBC連結包)

簡單例子:存粹的POI匯出Excel

文件目錄結構:

JavaBean: User

package bean;
public class User {
private Integer index;
private String userName;
private String password;
public Integer getIndex() {
return index;
}
public void setIndex(Integer index) {
this.index = index;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static void main(String[] args) {
User user = null;
user.getIndex();
}
}

Excel匯出類:  ExcelUtilVersionTwo.java

package util;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public abstract class ExcelUtilVersionTwo<T> implements TableCells<T> {
private String[] excelHeaders = new String[] {};// excel表頭
private String excelName = new String(); // execl表名
private Integer cellsNum = 0; // 匯出的單元格有幾列
private String fileName = new String(); // 匯出的檔案的名字
public String[] getExcelHeaders() {
return excelHeaders;
}
public void setExcelHeaders(String[] excelHeaders) {
this.excelHeaders = excelHeaders;
}
public String getExcelName() {
return excelName;
}
public void setExcelName(String excelName) {
this.excelName = excelName;
}
public Integer getCellsNum() {
return cellsNum;
}
public void setCellsNum(Integer cellsNum) {
this.cellsNum = cellsNum;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
@Override
public abstract void selectedTableCells(HSSFRow tableRow, T t);
@SuppressWarnings("deprecation")
public void exportExcel(List<T> results) {
// 0.建立工作本
HSSFWorkbook excelWorkBook = new HSSFWorkbook();
// 1.建立表
HSSFSheet excelSheet = null;
if (this.excelName == null) {
this.excelName = new String();
}
excelSheet = excelWorkBook.createSheet(this.excelName);
// 2.建立表頭: 建立一行
HSSFRow headerRow = excelSheet.createRow((short) 0);
for (int i = 0; i < this.excelHeaders.length; i  ) {
// 建立一個單元格
HSSFCell headerCell = headerRow.createCell((short) i);
// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
// CellStyle cs = new CellStyle();
// 設定cell的值
headerCell.setCellValue(excelHeaders[i]);
}
// 3.根據查詢出來的結果集results,填寫excel表格
if (results != null) {
T objectT = null;
for (int index = 0; index < results.size(); index  ) {
// 4.建立一行
HSSFRow tableRow = excelSheet.createRow((short) index   1); // 建立行,因為第一行是表頭,
// 即row(0)
objectT = results.get(index);
this.selectedTableCells(tableRow, objectT);
// ********** selectedTableCell的大致實現 ***********
// HSSFRow row = demoSheet.createRow((short) index);
// for (short i = 0; i < cells.size(); i  ) {
// // 建立第i個單元格
// HSSFCell cell = row.createCell((short) i);
// // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(cells.get(i));
// }
}
}
// 4.將excel匯出到檔案中
FileOutputStream out = null;
// 如果沒有名字則檔名為data時間 excelName
if (this.fileName.equals(new String())) {
SimpleDateFormat tmp = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String dateStr = tmp.format(new Date());
this.setFileName(dateStr   this.getExcelName());
}
try {
out = new FileOutputStream(fileName);
// excelSheet.setGridsPrinted(true);
// HSSFFooter footer = excelSheet.getFooter();
// footer.setRight("Page "   HSSFFooter.page()   " of "
//   HSSFFooter.numPages());
excelWorkBook.write(out);
JOptionPane.showMessageDialog(null, "表格已成功匯出到 : "   fileName);
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "表格匯出出錯,錯誤資訊 :"   e
"\n錯誤原因可能是表格已經開啟。");
e.printStackTrace();
}
}
public void exportExcel(List<T> results, String[] headers, String excelName) {
this.setExcelHeaders(headers);
this.setExcelName(excelName);
this.exportExcel(results);
}
/**
* @note 匯出excel報表
* @param results
*            查詢出來的結果集
* @param headers
*            表格的頭
* @param excelName
*            excel報表名
* @param fileName
*            匯出的excel檔名
*/
public void exportExcel(List<T> results, String[] headers,
String excelName, String fileName) {
this.setExcelHeaders(headers);
this.setExcelName(excelName);
this.setFileName(fileName);
this.exportExcel(results);
}
public static void main(String[] args) {
Date date = new Date();
String fileName = "D:\\使用者匯出報表"   date.toString()   ".xls";
System.out.println(fileName);
String[] list = new String[] {};
System.out.println(list.length);
}
}

介面: TableCells<T>

package util;
import org.apache.poi.hssf.usermodel.HSSFRow;
/**
* @author szh
* @date 2016-3-9
* @param <T>
*/
public interface TableCells<T> {
/**
* @note 該介面在ExcelUtil中被實現,主要功能: 根據 T(po類)指定欄位 設定tableRow的資料,
*       並可以做一定的業務處理,(更好的實現方式,在ExcelUtil中做成抽象方法)
* @param tableRow
* @param t
*/
void selectedTableCells(HSSFRow tableRow, T t);
}

匯出Excel測試類:ExportExcelUser

package service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.HSSFRow;
import util.ExcelUtilVersionTwo;
import bean.User;
public class ExportExcelUser extends ExcelUtilVersionTwo<User> {
@Override
public void selectedTableCells(HSSFRow tableRow, User t) {
HSSFCell indexCell = tableRow.createCell(0);
indexCell.setCellValue(t.getIndex());
HSSFCell userNameCell = tableRow.createCell(1);
userNameCell.setCellValue(t.getUserName());
HSSFCell passCell = tableRow.createCell(2);
passCell.setCellValue(t.getPassword());
}
public List<User> getAllUser() {
List<User> useList = new ArrayList<User>();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "123456");
Statement sm = conn.createStatement();
ResultSet rs = sm.executeQuery("select id,name,password from user");
while (rs.next()) {
List<String> list = new ArrayList<String>();
for (int i = 1; i <= 3; i  ) {
list.add(rs.getString(i));
}
User user = new User();
user.setIndex(Integer.valueOf(list.get(0)));
user.setUserName(list.get(1));
user.setPassword(list.get(2));
useList.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}
return useList;
}
public static void main(String[] args) {
ExportExcelUser exportExcelUser = new ExportExcelUser();
List<User> list = exportExcelUser.getAllUser();
for (int i = 0; i < list.size(); i  ) {
System.out.println(list.get(i).getIndex()   " "
list.get(i).getUserName()   " "
list.get(i).getPassword());
}
exportExcelUser.exportExcel(list, new String[] { "序號", "使用者名稱", "密碼" },
"使用者資訊表", "D:\\user表資訊.xls");
}
}

表結構:

測試用例:

2.JavaEE 通過HttpResponseSevlet 實現檔案下載

http://www.cnblogs.com/xdp-gacl/p/4200090.html

這裡結合前後臺給大家講解一下: 專案中用到的框架是Spring SpringMvc Mybatis

前臺頁面:

前臺頁面中的Html 部分

點選匯出按鈕的js效果:(注意這裡不能通過JQuery Post,Get等Ajax方法,因為JavaScrip中沒有檔案流的概念,講解請參考博文  

1.   JAVA WEB用servlet下載檔案不能彈出對話方塊

http://bbs.csdn.net/topics/390630180



2. http://www.jb51.net/article/53479.htm)

/* 匯出excel */
$(function(){
$("#exportExcel").click(function(){
var url = appCtx "userClassDailyTotal/exportExcel.do" "?" $("#listForm").serialize();
//將表單序列化提交
window.location.href = url;
});
});

後臺SpringMvc

Controller部分:

	/**
* @param userClassMonthlyTotal
* @param request
* @param response
* @param session
* @return
* @throws Exception
*/
@SuppressWarnings("unused")
@RequestMapping(value = "/exportExcel.do")
public ModelAndView exportExcel(UserClassMonthlyTotal userClassMonthlyTotal, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception {
Object obj = session.getAttribute(ConstantAdmin.SESSION_ADMIN_INFO);
if (obj instanceof UserOrganization) {
UserOrganization userOrg = (UserOrganization) obj;
int organizationId = userOrg.getOrganizationId();
userClassMonthlyTotal.setOrganizationId(organizationId);
} else if (obj instanceof SysUser) {
SysUser user = (SysUser) obj;
}
userClassMonthlyTotalService.selectExportExcel(userClassMonthlyTotal, response);
return null;
}

Service部分:

	@Transactional(propagation=Propagation.NOT_SUPPORTED)
public void selectExportExcel(UserClassMonthlyTotal userClassMonthlyTotal, HttpServletResponse response){
List<UserClassMonthlyTotal> list = userClassMonthlyTotalMapper.selectBySelectiveNoPage(userClassMonthlyTotal);
class UserClassMonthlyStatExcel extends ExcelUtilVersionTwo<UserClassMonthlyTotal>{
@Override
public void selectedTableCells(HSSFRow tableRow,
UserClassMonthlyTotal t) {
DecimalFormat floatConvert = new DecimalFormat("##0.00");
SimpleDateFormat monthConvert = new SimpleDateFormat("yyyy-MM");
tableRow.createCell(0).setCellValue(t.getUserName());
tableRow.createCell(1).setCellValue(floatConvert.format(t.getShouldHour()));
tableRow.createCell(2).setCellValue(floatConvert.format(t.getActualHour()));
tableRow.createCell(3).setCellValue(floatConvert.format(t.getOvertimeHour()));
tableRow.createCell(4).setCellValue(floatConvert.format(t.getAbsentDay()));
tableRow.createCell(5).setCellValue(t.getDelayCount());
tableRow.createCell(6).setCellValue(t.getEarlyLeaveCount());
tableRow.createCell(7).setCellValue(monthConvert.format(t.getUserClassMonth()));
}
}
UserClassMonthlyStatExcel userMonthlyStat = new UserClassMonthlyStatExcel();
SimpleDateFormat convert = new SimpleDateFormat("yyyyMMddHHmmssSSS");
Date now = new Date();
String nowString = convert.format(now);
userMonthlyStat.exportExcel(list, new String[]{"使用者名稱","應工作時長(時)","實際工作時長(時)","加班時長(時)","缺勤時長(時)","遲到次數(次)","早退次數(次)","統計月份"},"每月統計報表", nowString "每月統計報表", response);
}

編寫的介面:selectTableCells

主要功能:根據業務實現對查詢出來的資料怎麼處理,設定到Excel 單元格中。

package cn._2vin.yannan.util;
import org.apache.poi.hssf.usermodel.HSSFRow;
/**
* @author szh
* @date 2016-3-9
* @param <T>
*/
public interface TableCells<T> {
/**
* @note 該介面在ExcelUtil中被實現,主要功能: 根據 T(po類)指定欄位 設定tableRow的資料,
*       並可以做一定的業務處理,(更好的實現方式,在ExcelUtil中做成抽象方法)
* @param tableRow
* @param t
*/
void selectedTableCells(HSSFRow tableRow, T t);
}

包裝的介面類:可以實現下載資料到本地指定路徑和從網路下載兩種功能。裡面涵蓋了中文檔名下載解決地方法:http://blog.csdn.net/u010003835/article/details/50857611

package cn._2vin.yannan.util;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public abstract class ExcelUtilVersionTwo<T> implements TableCells<T> {
private String[] excelHeaders = new String[] {};// excel表頭
private String excelName = new String(); // execl表名
private String filePath = new String();	// 儲存到本地的實際路徑
private String fileName = new String(); // 匯出的檔案的名字
private HttpServletResponse response = null; //HttpResponse
public String[] getExcelHeaders() {
return excelHeaders;
}
public void setExcelHeaders(String[] excelHeaders) {
this.excelHeaders = excelHeaders;
}
public String getExcelName() {
return excelName;
}
public void setExcelName(String excelName) {
this.excelName = excelName;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
private HttpServletResponse getResponse() {
return response;
}
private void setResponse(HttpServletResponse response) {
this.response = response;
}
@Override
public abstract void selectedTableCells(HSSFRow tableRow, T t);
@SuppressWarnings("deprecation")
private void exportExcel(List<T> results){
// 0.建立工作本
HSSFWorkbook excelWorkBook = new HSSFWorkbook();
// 1.建立表
HSSFSheet excelSheet = null;
if (this.excelName == null) {
this.excelName = new String();
}
excelSheet = excelWorkBook.createSheet(this.excelName);
// 2.建立表頭: 建立一行
HSSFRow headerRow = excelSheet.createRow((short) 0);
for (int i = 0; i < this.excelHeaders.length; i  ) {
// 建立一個單元格
HSSFCell headerCell = headerRow.createCell((short) i);
// headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
// CellStyle cs = new CellStyle();
// 設定cell的值
headerCell.setCellValue(excelHeaders[i]);
}
// 3.根據查詢出來的結果集results,填寫excel表格
if (results != null) {
T objectT = null;
for (int index = 0; index < results.size(); index  ) {
// 4.建立一行
HSSFRow tableRow = excelSheet.createRow((short) index   1); // 建立行,因為第一行是表頭,
// 即row(0)
objectT = results.get(index);
this.selectedTableCells(tableRow, objectT);
// ********** selectedTableCell的大致實現 ***********
// HSSFRow row = demoSheet.createRow((short) index);
// for (short i = 0; i < cells.size(); i  ) {
// // 建立第i個單元格
// HSSFCell cell = row.createCell((short) i);
// // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(cells.get(i));
// }
}
}
// 4.將excel匯出到檔案中
// 如果沒有名字則檔名為data時間 excelName
if (this.fileName.equals(new String()) || this.fileName==null) {
SimpleDateFormat tmp = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String dateStr = tmp.format(new Date());
this.setFileName(dateStr   this.getExcelName());	
}
//判斷是下載到指定路徑,還是網路下載,
//本地下載,建立本地檔案流,
//否則,利用repsonse的檔案流
if(this.getResponse() == null){
OutputStream ioFileStream = null;
try {
ioFileStream = new FileOutputStream(this.filePath   this.fileName  ".xls");
excelWorkBook.write(ioFileStream);
ioFileStream.flush();
ioFileStream.close();
} catch (Exception e) {
e.printStackTrace();
}			
}
else{
this.response.setContentType("application/vnd.ms-excel");
this.response.setHeader("Cache-Control", "no-store");
SimpleDateFormat createDayConvert = new SimpleDateFormat("yyyyMMddHHmmssSSS");		//日期轉換器
String fileString = createDayConvert.format(new Date()) this.excelName   ".xls";
//解決中文亂碼問題
try {
response.setHeader("Content-Disposition", "attachment; filename="
new String( fileString.getBytes("utf-8"), "ISO8859-1" ));
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
OutputStream ioWebStream = null;
try {
ioWebStream = response.getOutputStream();
excelWorkBook.write(ioWebStream);
ioWebStream.flush();
ioWebStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* @note 從頁面上下載excel表格
* @param results  查詢出來的結果集
* @param headers  表格的頭
* @param excelName excel表名
* @param fileName 匯出的excel檔名
* @param response HttpServletResponse
*/
public void exportExcel(List<T> results, String[] headers, String excelName, String fileName, HttpServletResponse response) {
this.setExcelHeaders(headers);
this.setExcelName(excelName);
this.setFileName(fileName);
this.setResponse(response);
this.exportExcel(results);
}
/**
* @note 匯出excel報表到本地的指定路徑
* @param results  查詢出來的結果集
* @param headers  表格的頭
* @param excelName excel表名
* @param filePath 本地的儲存路徑
* @param fileName 匯出的excel檔名
*/
public void exportExcel(List<T> results, String[] headers,
String excelName, String filePath, String fileName) {
this.setExcelHeaders(headers);
this.setExcelName(excelName);
this.setFilePath(filePath);
this.setFileName(fileName);
this.exportExcel(results);
}
}