POI测试Excel报表导出（包含导出公式）
直接在eclipse中测试需要导出的Excel表，免去在Flex Buider和eclipse中来回测试。
package tree;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jxls.transformer.XLSTransformer;
import net.sf.jxls.*;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import tree.SheetFunc;
@SuppressWarnings({ "serial", "unused" })
public class Test {//用于测试导出
public static void main(String[] args) throws IOException{
try {
/* 添加
String sqltext =java.net.URLDecoder.decode(request.getParameter("SQL"), "UTF-8");
sqltext=sqltext.toLowerCase();
String templateFileName =java.net.URLDecoder.decode(request.getParameter("templateFileName"), "UTF-8");
String destFileName = java.net.URLDecoder.decode(request.getParameter("destFileName"), "UTF-8");
*/
String sqltext ="select * from hc_rlcbyb";
sqltext=sqltext.toLowerCase();
int index=sqltext.indexOf("from");
String sqlCount="select count(*) as NO "+sqltext.substring(index);
Connection conn = new connectionDataBase().connProc();
Statement stmt;
Connection connnum = new connectionDataBase().connProc();
Statement stmtnum;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sqltext);
stmtnum = connnum.createStatement();
ResultSet rsnum = stmtnum.executeQuery(sqlCount);
int dataRow=0;
while(rsnum.next())
{
dataRow=rsnum.getInt("NO");//获取查询的记录数
}
dataRow=17;
Date[][] sqlDataDate;
sqlDataDate=new Date[dataRow][1];
float[][] sqlDataFloat;
sqlDataFloat=new float[dataRow][1];
String[][] sqlDataString;
sqlDataString=new String[dataRow][1];
for(int m=0;rs.next();m++)
{
if("2".equals(rs.getString("XH")))
{
sqlDataFloat[0][0]=rs.getFloat("XMZ");
}
if("3".equals(rs.getString("XH")))
{
sqlDataFloat[1][0]=rs.getFloat("XMZ");
}
if("4".equals(rs.getString("XH")))
{
sqlDataFloat[2][0]=rs.getFloat("XMZ");
}
if("6".equals(rs.getString("XH")))
{
sqlDataFloat[3][0]=rs.getFloat("XMZ");
}
if("7".equals(rs.getString("XH")))
{
sqlDataFloat[4][0]=rs.getFloat("XMZ");
}
if("8".equals(rs.getString("XH")))
{
sqlDataFloat[5][0]=rs.getFloat("XMZ");
}
if("10".equals(rs.getString("XH")))
{
sqlDataFloat[6][0]=rs.getFloat("XMZ");
}
if("11".equals(rs.getString("XH")))
{
sqlDataFloat[7][0]=rs.getFloat("XMZ");
}
if("12".equals(rs.getString("XH")))
{
sqlDataFloat[8][0]=rs.getFloat("XMZ");
}
if("14".equals(rs.getString("XH")))
{
sqlDataFloat[9][0]=rs.getFloat("XMZ");
}
if("15".equals(rs.getString("XH")))
{
sqlDataFloat[10][0]=rs.getFloat("XMZ");
}
if("16".equals(rs.getString("XH")))
{
sqlDataFloat[11][0]=rs.getFloat("XMZ");
}
if("17".equals(rs.getString("XH")))
{
sqlDataFloat[12][0]=rs.getFloat("XMZ");
}
if("18".equals(rs.getString("XH")))
{
sqlDataFloat[13][0]=rs.getFloat("XMZ");
}
if("19".equals(rs.getString("XH")))
{
sqlDataFloat[14][0]=rs.getFloat("XMZ");
}
if("21".equals(rs.getString("XH")))
{
sqlDataFloat[15][0]=rs.getFloat("XMZ");
}
if("22".equals(rs.getString("XH")))
{
sqlDataFloat[16][0]=rs.getFloat("XMZ");
}
}
//需要修改部分
InputStream is = new FileInputStream("rlrlmcbybss.xls");//模版文件
XLSTransformer transformer = new XLSTransformer();
Map beans = new HashMap();
transformer.transformXLS( "rlrlmcbybss.xls", beans,"workbook.xls");
HSSFWorkbook wb = transformer.transformXLS(is, beans);
FileOutputStream out = new FileOutputStream("workbook.xls");//输出文件
for(int num=0;num<1;num++)
{
HSSFSheet sheet = wb.getSheetAt(num);
HSSFRow row=sheet.getRow(4);
row.getCell((short)3).setCellValue(sqlDataFloat[0][0]);
row=sheet.getRow(5);//
row.getCell((short)3).setCellValue(sqlDataFloat[1][0]);
row=sheet.getRow(6);//
row.getCell((short)3).setCellValue(sqlDataFloat[2][0]);
row=sheet.getRow(8);//
row.getCell((short)3).setCellValue(sqlDataFloat[3][0]);
row=sheet.getRow(9);//
row.getCell((short)3).setCellValue(sqlDataFloat[4][0]);
row=sheet.getRow(10);//
row.getCell((short)3).setCellValue(sqlDataFloat[5][0]);
row=sheet.getRow(12);//
row.getCell((short)3).setCellValue(sqlDataFloat[6][0]);
row=sheet.getRow(13);//
row.getCell((short)3).setCellValue(sqlDataFloat[7][0]);
row=sheet.getRow(14);//
row.getCell((short)3).setCellValue(sqlDataFloat[8][0]);
row=sheet.getRow(16);//
row.getCell((short)3).setCellValue(sqlDataFloat[9][0]);
row=sheet.getRow(17);//
row.getCell((short)3).setCellValue(sqlDataFloat[10][0]);
row=sheet.getRow(18);//
row.getCell((short)3).setCellValue(sqlDataFloat[11][0]);
row=sheet.getRow(19);//
row.getCell((short)3).setCellValue(sqlDataFloat[12][0]);
row=sheet.getRow(20);//
row.getCell((short)3).setCellValue(sqlDataFloat[13][0]);
row=sheet.getRow(21);//
row.getCell((short)3).setCellValue(sqlDataFloat[14][0]);
row=sheet.getRow(22);//
row.getCell((short)3).setCellValue(sqlDataFloat[15][0]);
row=sheet.getRow(23);//
row.getCell((short)3).setCellValue(sqlDataFloat[16][0]);
//设置合计公式
sheet.getRow(3).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(3).getCell((short)3).setCellFormula("D5+D6+D7+D7");
sheet.getRow(7).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(7).getCell((short)3).setCellFormula("D9+D10+D11");
sheet.getRow(11).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(11).getCell((short)3).setCellFormula("D13+D14+D15+D15");
sheet.getRow(15).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(15).getCell((short)3).setCellFormula("D17+D18+D19");
}
wb.write(out);
out.close();
is.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
