|
|
Posted on 2005-12-18 00:45 ajie 阅读(1473) 评论(0) 编辑 收藏
用到开源软件POI，详细见以下URL：
http://jakarta.apache.org/poi/index.html
  /** *//**
 * 该类演示了POI的应用
* 版权 本文版权属Java天下
* Created on 2005-7-22
 */
 package bss.servlet;
import javax.servlet.http.*;
import javax.servlet.*;
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;
import java.io.FileOutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;
import java.util.Map;
import java.text.SimpleDateFormat;
import java.util.Calendar;
/** *//**
* @author ljfan
*
* TODO To change the template for this generated type comment go to Window -
* Preferences - Java - Code Style - Code Templates
*/
public class IVR_Excel extends HttpServlet  {
//文档标题数组名
  private final static String sTitleArray[] = { "群发批次", "产品代码", "目的省份", "目的城市" };
//记录集列名
private final static String sColumnArray[] = { "push_id", "pid", "province", "gwcity" };
/**//*
* 按一定的规则生成文件名
* 规则为 IVRExcel_YYYY-MM-dd-mm-SSS.xls
 */
private static String generateFileName () {
String sFileName = "IVRExcel_";
SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd-HH-mm-SSS");
Calendar c = Calendar.getInstance();
sFileName += sdf.format(c.getTime());
sFileName += ".xls";
return sFileName;
}
/** *//**
* 将记录信息导出为xls格式文件.
*
* @param fieldTitles
* 工作表的字段标题数组(首行)
* @param propertyNames
* 每行记录对应的值对象的属性名称数组
* @param records
* 记录集map, 其中key为记录集中的列名, value为对应列名的值（vector）
* @param fileName
* 导出文件的名称
*/
public static void exportXLSFile(String[] fieldTitles,
String[] propertyNames, Map records, String fileName) {
if (fieldTitles.length != propertyNames.length) {
throw new IllegalArgumentException("工作表的字段标题列数必须和值对象的属性数相等！");
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
//设置标题
HSSFRow row = sheet.createRow(0);
for (int k = 0; k < fieldTitles.length; k++) {
HSSFCell cell[] = new HSSFCell[fieldTitles.length];
cell[k] = row.createCell((short) k);
cell[k].setEncoding(HSSFCell.ENCODING_UTF_16);
cell[k].setCellType(HSSFCell.CELL_TYPE_STRING);
cell[k].setCellValue(fieldTitles[k]);
}
//写入数据库中的记录集数据
//取得记录集行数
Iterator iter = records.values().iterator();
Vector v1 = (Vector) iter.next();
int rowNum = v1.size() + 1;
for (int i = 1; i < rowNum; i++) {
HSSFRow _row = sheet.createRow(i);
for (int j = 0; j < propertyNames.length; j++) {
Vector value = (Vector) records.get(propertyNames[j]);
HSSFCell cell[] = new HSSFCell[propertyNames.length];
cell[j] = _row.createCell((short) j);
cell[j].setEncoding(HSSFCell.ENCODING_UTF_16);
cell[j].setCellType(HSSFCell.CELL_TYPE_STRING);
cell[j].setCellValue((String) value.get(i - 1));
}
}
try {
String outputFile = fileName;
FileOutputStream fOut = new FileOutputStream(outputFile);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException {
long start = System.currentTimeMillis();
String sRootDir = this.getServletConfig().getServletContext().getRealPath(
req.getServletPath());
System.out.println(sRootDir);
/**//*
* 得到根路径
* servlet部署名为ExcelServlet
*/
sRootDir = sRootDir.substring(0, sRootDir.indexOf("ExcelServlet"));
//先在服务器根目录下建Excel目录
sRootDir += "Excel\\";
sRootDir = sRootDir.replace("\\", "\\\\");
String sOutputPath = generateFileName();
System.out.println(sRootDir);
sOutputPath = sRootDir + sOutputPath;
System.out.println(sOutputPath);
/**//*
* 获取URL带的参数
* param1 : PushID
* param2 : Province
* param3 : Pid
* param4 : Opp
* param5 : StartDate
* param6 : EndDate
*/
int iID = 0;
int iOpp = 0;
if (req.getParameter("ID") != null) {
try {
iID = Integer.parseInt(req.getParameter("ID"));
} catch (Exception e) {
iID = 0;
}
}
if (req.getParameter("opp") != null) {
try {
iOpp = Integer.parseInt(req.getParameter("opp"));
} catch (Exception e) {
iOpp = 0;
}
}
String sPid = req.getParameter("pid").trim();
String sProvince = req.getParameter("province").trim();
String sStartDate = req.getParameter("StartDate");
if (sStartDate == null)
sStartDate = "";
String sEndDate = req.getParameter("EndDate");
if (sEndDate == null)
sEndDate = "";
//get data from DB
Map DBMap = getFromDB(iID, sProvince, sPid, iOpp, sStartDate, sEndDate );
exportXLSFile(sTitleArray, sColumnArray, DBMap, sOutputPath);
long end = System.currentTimeMillis();
System.out.println("Excel文档已经生成，共花费: " + (end-start) +" ms");
}
public void doPost(HttpServletRequest res, HttpServletResponse resp)
throws ServletException {
doGet(res, resp);
}
public static Map getFromDB(int iID, String sProvince, String sPid, int iOpp, String sStartDate, String sEndDate) {
Map map = new HashMap();
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
String sDBURL = "172.16.18.113";
String sDBName = "DetailQuery";
String sLoginNameUser = "*****";
String sPasswordUser = "*****";
String sTitles[] = { "群发批次", "产品代码", "目的省份", "目的城市" };
String sColumn[] = { "push_id", "pid", "province", "gwcity" };
Vector v[] = new Vector[sColumn.length];
for (int i = 0; i < sColumn.length; i++) {
v[i] = new Vector();
}
//get ResultSet from db
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://"
+ sDBURL + ":1433;Databasename=" + sDBName, sLoginNameUser,
sPasswordUser);
cstmt = con
.prepareCall("{? = call RED_P_SMSPUSH_Query_IVR_main_demo (?, ?, ?, ?, ?, ?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, iID);
cstmt.setString(3, sProvince);
cstmt.setString(4, sPid);
cstmt.setInt(5, iOpp);
cstmt.setString(6, sStartDate);
cstmt.setString(7, sEndDate);
cstmt.execute();
rs = cstmt.getResultSet();
while (rs.next()) {
for (int i = 0; i < sColumn.length; i++) {
v[i].add(rs.getString(sColumn[i]));
}
}
for (int i = 0; i < sColumn.length; i++) {
map.put(sColumn[i], v[i]);
}
rs.close();
cstmt.close();
con.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
return map;
}
// for simple test
/**//*
public static void main (String args[]) {
String sFileName = IVR_Excel.generateFileName();
System.out.println(sFileName);
int iID = 0;
int iOpp = 0;
String sProvince = "all";
String sPid = "all";
String sStartDate = "2005-7-1";
String sEndDate = "2005-7-21";
//get data from DB
Map DBMap = getFromDB(iID, sProvince, sPid, iOpp, sStartDate, sEndDate );
exportXLSFile(sTitleArray, sColumnArray, DBMap, "Test.xls");
System.out.println("已经成功生成Excle文档");
}
*/
}
Java天下社区
http://www.javatx.cn
欢迎大家上来交流Java技术。
|