POI测试Excel报表导出（包含导出公式）

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;
&＃64;SuppressWarnings({ "serial", "unused" })
public class Test {//用于测试导出
public static void main(String[] args) throws IOException{
try {
/* 添加
String sqltext &＃61;java.net.URLDecoder.decode(request.getParameter("SQL"), "UTF-8");
sqltext&＃61;sqltext.toLowerCase();
String templateFileName &＃61;java.net.URLDecoder.decode(request.getParameter("templateFileName"), "UTF-8");
String destFileName &＃61; java.net.URLDecoder.decode(request.getParameter("destFileName"), "UTF-8");
*/

String sqltext &＃61;"select * from hc_rlcbyb";
sqltext&＃61;sqltext.toLowerCase();
int index&＃61;sqltext.indexOf("from");
String sqlCount&＃61;"select count(*) as NO "&＃43;sqltext.substring(index);

Connection conn &＃61; new connectionDataBase().connProc();
Statement stmt;
Connection connnum &＃61; new connectionDataBase().connProc();
Statement stmtnum;

stmt &＃61; conn.createStatement();
ResultSet rs &＃61; stmt.executeQuery(sqltext);

stmtnum &＃61; connnum.createStatement();
ResultSet rsnum &＃61; stmtnum.executeQuery(sqlCount);
int dataRow&＃61;0;
while(rsnum.next())
{
dataRow&＃61;rsnum.getInt("NO");//获取查询的记录数
}
dataRow&＃61;17;
Date[][] sqlDataDate;
sqlDataDate&＃61;new Date[dataRow][1];

float[][] sqlDataFloat;
sqlDataFloat&＃61;new float[dataRow][1];

String[][] sqlDataString;
sqlDataString&＃61;new String[dataRow][1];
for(int m&＃61;0;rs.next();m&＃43;&＃43;)
{
if("2".equals(rs.getString("XH")))
{
sqlDataFloat[0][0]&＃61;rs.getFloat("XMZ");
}
if("3".equals(rs.getString("XH")))
{
sqlDataFloat[1][0]&＃61;rs.getFloat("XMZ");
}
if("4".equals(rs.getString("XH")))
{
sqlDataFloat[2][0]&＃61;rs.getFloat("XMZ");
}
if("6".equals(rs.getString("XH")))
{
sqlDataFloat[3][0]&＃61;rs.getFloat("XMZ");
}
if("7".equals(rs.getString("XH")))
{
sqlDataFloat[4][0]&＃61;rs.getFloat("XMZ");
}
if("8".equals(rs.getString("XH")))
{
sqlDataFloat[5][0]&＃61;rs.getFloat("XMZ");
}
if("10".equals(rs.getString("XH")))
{
sqlDataFloat[6][0]&＃61;rs.getFloat("XMZ");
}
if("11".equals(rs.getString("XH")))
{
sqlDataFloat[7][0]&＃61;rs.getFloat("XMZ");
}
if("12".equals(rs.getString("XH")))
{
sqlDataFloat[8][0]&＃61;rs.getFloat("XMZ");
}
if("14".equals(rs.getString("XH")))
{
sqlDataFloat[9][0]&＃61;rs.getFloat("XMZ");
}
if("15".equals(rs.getString("XH")))
{
sqlDataFloat[10][0]&＃61;rs.getFloat("XMZ");
}
if("16".equals(rs.getString("XH")))
{
sqlDataFloat[11][0]&＃61;rs.getFloat("XMZ");
}
if("17".equals(rs.getString("XH")))
{
sqlDataFloat[12][0]&＃61;rs.getFloat("XMZ");
}
if("18".equals(rs.getString("XH")))
{
sqlDataFloat[13][0]&＃61;rs.getFloat("XMZ");
}
if("19".equals(rs.getString("XH")))
{
sqlDataFloat[14][0]&＃61;rs.getFloat("XMZ");
}
if("21".equals(rs.getString("XH")))
{
sqlDataFloat[15][0]&＃61;rs.getFloat("XMZ");
}
if("22".equals(rs.getString("XH")))
{
sqlDataFloat[16][0]&＃61;rs.getFloat("XMZ");
}
}

//需要修改部分
InputStream is &＃61; new FileInputStream("rlrlmcbybss.xls");//模版文件
XLSTransformer transformer &＃61; new XLSTransformer();
Map beans &＃61; new HashMap();
transformer.transformXLS( "rlrlmcbybss.xls", beans,"workbook.xls");
HSSFWorkbook wb &＃61; transformer.transformXLS(is, beans);
FileOutputStream out &＃61; new FileOutputStream("workbook.xls");//输出文件


for(int num&＃61;0;num<1;num&＃43;&＃43;)
{
HSSFSheet sheet &＃61; wb.getSheetAt(num);

HSSFRow row&＃61;sheet.getRow(4);
row.getCell((short)3).setCellValue(sqlDataFloat[0][0]);
row&＃61;sheet.getRow(5);//
row.getCell((short)3).setCellValue(sqlDataFloat[1][0]);
row&＃61;sheet.getRow(6);//
row.getCell((short)3).setCellValue(sqlDataFloat[2][0]);
row&＃61;sheet.getRow(8);//
row.getCell((short)3).setCellValue(sqlDataFloat[3][0]);
row&＃61;sheet.getRow(9);//
row.getCell((short)3).setCellValue(sqlDataFloat[4][0]);
row&＃61;sheet.getRow(10);//
row.getCell((short)3).setCellValue(sqlDataFloat[5][0]);
row&＃61;sheet.getRow(12);//
row.getCell((short)3).setCellValue(sqlDataFloat[6][0]);
row&＃61;sheet.getRow(13);//
row.getCell((short)3).setCellValue(sqlDataFloat[7][0]);
row&＃61;sheet.getRow(14);//
row.getCell((short)3).setCellValue(sqlDataFloat[8][0]);
row&＃61;sheet.getRow(16);//
row.getCell((short)3).setCellValue(sqlDataFloat[9][0]);
row&＃61;sheet.getRow(17);//
row.getCell((short)3).setCellValue(sqlDataFloat[10][0]);
row&＃61;sheet.getRow(18);//
row.getCell((short)3).setCellValue(sqlDataFloat[11][0]);
row&＃61;sheet.getRow(19);//
row.getCell((short)3).setCellValue(sqlDataFloat[12][0]);
row&＃61;sheet.getRow(20);//
row.getCell((short)3).setCellValue(sqlDataFloat[13][0]);
row&＃61;sheet.getRow(21);//
row.getCell((short)3).setCellValue(sqlDataFloat[14][0]);
row&＃61;sheet.getRow(22);//
row.getCell((short)3).setCellValue(sqlDataFloat[15][0]);
row&＃61;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&＃43;D6&＃43;D7&＃43;D7");
sheet.getRow(7).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(7).getCell((short)3).setCellFormula("D9&＃43;D10&＃43;D11");
sheet.getRow(11).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(11).getCell((short)3).setCellFormula("D13&＃43;D14&＃43;D15&＃43;D15");
sheet.getRow(15).getCell((short)3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
sheet.getRow(15).getCell((short)3).setCellFormula("D17&＃43;D18&＃43;D19");
}



wb.write(out);
out.close();
is.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}