Question

java写入excel的的代码，我想在每次写入之前清空我之前写入的数据，其它不数据部要清空？？在线等

public File creatBook(String whereCla, Object[] whereValue)throws SQLException, IOException, RowsExceededException,WriteException {
String[] columns = new String[] { "*" };
ResultSet rs = ex.query(tableName, columns, whereCla, whereValue, null);
try {
File file = new File(fileName);
Workbook rwb = Workbook.getWorkbook(file);
WritableWorkbook wwb = Workbook.createWorkbook(file, rwb);
WritableSheet ws = wwb.getSheet(0);
int i = 4;
while (rs.next()) {
if(rs.getMetaData().getColumnCount()!=0){

for (int k = 2; k <= rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k - 1, i, rs.getString(k)));
}
}
i++;
}
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
return new File(fileName);
}
fileName
是我写入数据的模板

Answer 1

package com.ewin.test;

import java.io.File;
import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ewin.common.DBException;
import com.ewin.util.ExcelUtil;
import com.ewin.util.StringUtil;

public class TestExl {
@SuppressWarnings("unchecked")
public static void main(String[] args)throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.90:1521:a1", "bill", "bill");
Statement state = conn.createStatement();
try {
// 导入时模板头部信息
String[] headNames = new String[] { "运单号", "运单ID"};
// 头部列对应的字段名
String[] fieldNames = new String[] { "BILL_NO", "BILL_ID"};
Map<String, String> columnMap = new HashMap<String, String>();
columnMap.put("BILL_NO", "运单号");
List<Object> returnList = ExcelUtil.getExcelToList(columnMap,""
+ "D:\\Documents\\Desktop\\exs.xls", 0);
List<Object> list = new ArrayList<Object>();
for (int i = 0; i < returnList.size(); i++) {
Map<Object,Object> map = (Map<Object,Object>) returnList.get(i);
// 如果必填项为空，则提示信息，不能导入
ResultSet result = state.executeQuery("select ser_bill_no bill_id from op_bill where bill_no = '"+map.get("BILL_NO").toString()+"'");
Map map1 = new HashMap();
map1.put("BILL_NO",map.get("BILL_NO"));
String bill_id = "";
while(result.next()){
bill_id = result.getString(1);
}
map1.put("BILL_ID", bill_id);
list.add(map1);
}
Map m = new HashMap();
m.put("LIST", list);
m.put("HEAD", headNames);
m.put("FIELD", fieldNames);
createExcel(m);
} catch (DBException e) {
throw e;
}
state.close();
conn.close();
System.out.println("生成完毕");
}
@SuppressWarnings("unchecked")
public static void createExcel(Map map){
try{
// 获取要导出的数据集
List list = (List) map.get("LIST");
// excel头部
String[] headNames = (String[]) map.get("HEAD");
// excel头部对应的表字段
String[] fieldNames = (String[]) map.get("FIELD");
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 页数
int pages = 1;
// 获取总页数
if (list.size() > 50000) {
if (list.size() % 50000 == 0)
pages = list.size() / 50000;
else
pages = list.size() / 50000 + 1;
}
// 数据下标
int b = 0;
// 循环页数
for (int k = 1; k <= pages; k++) {
/***************** 创建页 *****************/
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("第" + k + "页");
// 设置excel每列宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建字体样式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
/*********** 创建页 ****************/
/***************** 创建标题和表头 *****************/
// 创建行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
// 循环创建标题列
for (int i = 0; i < headNames.length; i++) {
// 创建一个Excel的单元格
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(headNames[i]);
}
/*********** 创建标题和表头 ****************/
/*********** 添加内容 ****************/
// 行
int i = 0;
for (; b < list.size(); b++) {
// 起始为第2行
i++;
// 创建行
row = sheet.createRow((i));
row.setHeight((short) 500);// 设定行的高度
// 获取该行应输出的对象map
Map tempMap = (Map) list.get(b);
for (int j = 0; j < fieldNames.length; j++) {
// 创建一个Excel的单元格
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
// 取该列对应的值
Object temp = tempMap.get(fieldNames[j]);
if (StringUtil.isNull(temp))
cell.setCellValue("");
else {
if (fieldNames[j].equals("BILL_NO")) {
cell.setCellValue(temp.toString());
} else {
try {
cell.setCellValue(Double.parseDouble(temp
.toString()));
} catch (Exception e) {
cell.setCellValue(temp.toString());
}
}
}
}
// 一页写完
if (b % 50000 == 0 && b != 0) {
b++;
break;
}
}
/*********** 添加内容 ****************/
}
wb.write(new FileOutputStream(new File("D:\\Documents\\Desktop\\exscop.xls")));
}catch(Exception e){
e.printStackTrace();
}
}
}

我这个案例你可以看下、

追问

• 不能用poi包，只能用jxl包，硬性规定？？？？
  

追答

你可以尝试下、、