/**
* SaveExcelUtil.java
* com.linkage.justone4.comm
*
* Function: TODO
*
* ver date author
* ──────────────────────────────────
* Dec 6, 2010 程仁银
*
* Copyright (c) 2010, All Rights Reserved.
*/
package com.linkage.justone4.comm;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* ClassName:SaveExcelUtil
* Project:
* Company: LINKAGE
*
* @author 程仁银
* @version
* @since Ver 1.1
* @Date Dec 6, 2010 4:55:15 PM
*
* @see
*/
public class SaveExcelUtil
{
// private static Log log = LogFactory.getLog(ExcelUtil.class);
private static String shtName = "";
private static String[] cNames = null;
private static String[] cLabels = null;
private static int rpp = 200;
private static HSSFCellStyle style = null;
/**
* 通过给定的Sql导出Excel文件到Response输出流,需要指定Connection
*
* @param response
* HttpServletResponse Response
* @param conn
* Connection 指定的数据库连接
* @param sqlStr
* String 查询的Sql语句
* @param sheetName
* String 导出的Excel Sheet名称
* @param columnNames
* String[] 导出的 Excel 列名称
* @param rowPerPage
* int 每一个Sheet页的行数
* @throws SQLException
* 48.
*/
public static void export(Connection conn,String sqlStr, String sheetName, String columnNames[],
int rowPerPage) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
ps = conn.prepareStatement(sqlStr);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if (rowPerPage <= 10000 && rowPerPage >= 1) {
rpp = rowPerPage;
}
if (!"".equals(sheetName) && null != sheetName) {
shtName = sheetName;
} else {
shtName = rsmd.getTableName(0);
}
cNames = getColumnNames(rsmd);
if (null != columnNames) {
cLabels = columnNames; // compare( columnNames ) ;
} else {
cLabels = cNames;
}
HSSFWorkbook wb = new HSSFWorkbook();
style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFSheet sheet = createSheet(wb, 1);
setSheetColumnTitle(sheet);
int rowCnt = 0;
int sheetNum = 2;
while (rs.next()) {
if (rowCnt == rpp) {
sheet = createSheet(wb, sheetNum);
setSheetColumnTitle(sheet);
rowCnt = 0;
sheetNum++;
}
HSSFRow row = sheet.createRow(rowCnt + 1);
for (int i = 0; i < cNames.length; i++) {
HSSFCell cell = row.createCell((short) i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
String val = rs.getString(cNames[i]);
if (null == val) {
val = "";
}
cell.setCellValue(val.toUpperCase());
}
rowCnt++;
}
try {
// OutputStream os = response.getOutputStream();
// response.reset();
// response.setContentType("application/vnd.ms-excel");
// response.setHeader("Content-disposition", "attachment; filename="+getFileName(shtName));
// wb.write(os);
String savePath = "d:\\";
FileOutputStream fos = new FileOutputStream(new File(savePath,sheetName));
// fos.close();
wb.write(fos);
if (conn != null) {
conn.close();
}
} catch (IOException ex) {
// log.info("Export Excel file error ! " + ex.getMessage());
}
}
/**
*设置Sheet页的列属性
* @param sht
* HSSFSheet 124.
*/
private static void setSheetColumnTitle(HSSFSheet sht) {
HSSFRow row = sht.createRow(0);
for (int i = 0; i < cLabels.length; i++) {
HSSFCell cell = row.createCell((short) (i));
// cell.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);
cell.setCellValue(cLabels[i]);
cell.setCellStyle(style);
}
}
/**
* 获得源数据中的列名称
* @param rsmd
* ResultSetMetaData
* @return String[] 139.
*/
private static String[] getColumnNames(ResultSetMetaData rsmd) {
try {
StringBuffer result = new StringBuffer("");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
result.append(rsmd.getColumnLabel(i)).append(",");
}
if (result.length() > 0) {
return result.substring(0, result.length() - 1).toString().split(",");
}
} catch (Exception e) {
return null;
}
return null;
}
/**
*创建一个Sheet页并返回该对象
* @param wb
* HSSFWorkbook
* @param seq int
* @return HSSFSheet
*/
private static HSSFSheet createSheet(HSSFWorkbook wb, int seq) {
int sup = seq * rpp;
int sub = (seq - 1) * rpp + 1;
if (sub < 1) {
sub = 1;
}
return wb.createSheet(shtName + "(" + sub + "-" + sup + ")");
}
private static String getFileName(String tableName) {
return tableName + new java.util.Date().getTime() + ".xls";
}
}
SQL语句导出excel
最新推荐文章于 2024-06-19 16:30:50 发布