我的数据库有俩个表A,B
excle模板book中有俩个Sheet分别对应A,B
现在我想利用book把A,B俩个表的数据写到一个bb.xls中 bb.xls中也是有俩个Sheet
public static void resultSetToExcel(ResultSet rs,int sheetNum) throws Exception
{
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream("E:\\Book1.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(sheetNum);//sheetNum为Sheet的索引值
HSSFRow row ;
HSSFCell cell;
ResultSetMetaData md=rs.getMetaData();
int nColumn=md.getColumnCount();
int iRow=3;
while(rs.next())
{
row= sheet.getRow(iRow);
for(int j=1;j<=nColumn;j++)
{
cell =row.createCell((short)j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getObject(j).toString());
}
iRow++;
}
FileOutputStream fOut = new FileOutputStream("bb.xls");
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null,"报表生成!");
}
请看看我的代码有什么问题??
excle模板book中有俩个Sheet分别对应A,B
现在我想利用book把A,B俩个表的数据写到一个bb.xls中 bb.xls中也是有俩个Sheet
public static void resultSetToExcel(ResultSet rs,int sheetNum) throws Exception
{
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream("E:\\Book1.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(sheetNum);//sheetNum为Sheet的索引值
HSSFRow row ;
HSSFCell cell;
ResultSetMetaData md=rs.getMetaData();
int nColumn=md.getColumnCount();
int iRow=3;
while(rs.next())
{
row= sheet.getRow(iRow);
for(int j=1;j<=nColumn;j++)
{
cell =row.createCell((short)j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getObject(j).toString());
}
iRow++;
}
FileOutputStream fOut = new FileOutputStream("bb.xls");
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null,"报表生成!");
}
请看看我的代码有什么问题??
每次调用,在里面建一个HSSFSheet ,然后去查询rs,然后遍历rs,创建HSSFSheet 和HSSFCell
当你方法走完,就完成了一个HSSFSheet ,多次调用就在同一个HSSFWorkbook 建立了多个HSSFSheet ,然后用POI文件流输出就可以。
public class DataToExcelSheetAt
{
InputStream fs;
HSSFWorkbook workbook;
HSSFSheet sheet;
HSSFRow row;
HSSFCell cell;
public DataToExcelSheetAt() throws IOException
{
fs = new FileInputStream("D:\\Book1.xls");
workbook = new HSSFWorkbook(fs);
}
public void resultSetToExcel(String[] str,int index) throws IOException
{
if (str.length == 0) return;
if (index < 0) return;
if (workbook == null)
{
System.out.println("WorkSheet is null");
return;
}
sheet = workbook.getSheetAt(index);
if (sheet!=null)
{
for (int i=1;i<=str.length;i++)
{
row = sheet.createRow(i);
System.out.println(row);
cell = row.createCell((short)1);
cell.setCellValue(str[i-1]);
}
FileOutputStream fout = new FileOutputStream("bb.xls");
workbook.write(fout);
fout.flush();
fout.close();
JOptionPane.showMessageDialog(null,"报表生成!");
}
}
public static void main(String[] args) throws IOException
{
DataToExcelSheetAt dt = new DataToExcelSheetAt();
String[] str={
"123","12353","35646","536323","46464","6365765"
};
dt.resultSetToExcel(str,0);
dt.resultSetToExcel(str,1);
}
}
row= sheet.getRow(i);
用 row= sheet.getRow(iRow); 时输出的是模板的格式!可是i传0时可以执行,当传1时就有错误了!
用 row = sheet.createRow(i);时没问题,可是格式和模板不般配!
createRow(i)在excel指定位置i创建一行新的存储行,默认是不带任何格式和风格的,所以不会显示你以前excel模板的样式了,不过可以在创建了新行后根据要求自己配置该行的样式。