现在我用poi导出excel,遇到如下问题,下载后打开这个excel文件,说“您尝试打开的文件XX。xls的格式与文件扩展名指定的格式不一致。打开文件前请验证文件没有损坏且来源可信。。”这是什么地方报错?还是我生成excel的时候?
先贴段我生成excel的代码:望各位大侠帮忙!困扰半天了。急!
package com.reportforms.sys.util.export;import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;/**
* 生成导出Excel文件对象
*
*
*
*/public class XLSExport { // 设置cell编码解决中文高位字节截断
private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;// // 定制日期格式
// private static String DATE_FORMAT = " mm/dd/yy "; // "m/d/yy h:mm" // 定制浮点数格式
private static String NUMBER_FORMAT = " #,##0.00 "; private String xlsFileName; private HSSFWorkbook workbook; private HSSFSheet sheet; private HSSFRow row; /**
* 初始化Excel
*
* @param fileName
* 导出文件名
*/
public XLSExport(String fileName) {
this.xlsFileName = fileName;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
} /**
* 导出Excel文件
*
* @throws XLSException
*/
public void exportXLS() {
try {
FileOutputStream fOut = new FileOutputStream(xlsFileName);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
} catch (IOException e) {
} } /**
* 增加一行
*
* @param index
* 行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, String value) {
HSSFCell cell = this.row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellType(XLS_ENCODING);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, Calendar value) {
HSSFCell cell = this.row.createCell(index);
cell.setCellType(XLS_ENCODING);
cell.setCellValue(value.getTime());
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
// cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, int value) {
HSSFCell cell = this.row.createCell( index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, double value) {
HSSFCell cell = this.row.createCell( index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
HSSFCellStyle cellStyle = workbook.createCellStyle(); //建立新的cell样式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); //设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}}
先贴段我生成excel的代码:望各位大侠帮忙!困扰半天了。急!
package com.reportforms.sys.util.export;import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;/**
* 生成导出Excel文件对象
*
*
*
*/public class XLSExport { // 设置cell编码解决中文高位字节截断
private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;// // 定制日期格式
// private static String DATE_FORMAT = " mm/dd/yy "; // "m/d/yy h:mm" // 定制浮点数格式
private static String NUMBER_FORMAT = " #,##0.00 "; private String xlsFileName; private HSSFWorkbook workbook; private HSSFSheet sheet; private HSSFRow row; /**
* 初始化Excel
*
* @param fileName
* 导出文件名
*/
public XLSExport(String fileName) {
this.xlsFileName = fileName;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
} /**
* 导出Excel文件
*
* @throws XLSException
*/
public void exportXLS() {
try {
FileOutputStream fOut = new FileOutputStream(xlsFileName);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
} catch (IOException e) {
} } /**
* 增加一行
*
* @param index
* 行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, String value) {
HSSFCell cell = this.row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellType(XLS_ENCODING);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, Calendar value) {
HSSFCell cell = this.row.createCell(index);
cell.setCellType(XLS_ENCODING);
cell.setCellValue(value.getTime());
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
// cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, int value) {
HSSFCell cell = this.row.createCell( index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
} /**
* 设置单元格
*
* @param index
* 列号
* @param value
* 单元格填充值
*/
public void setCell(int index, double value) {
HSSFCell cell = this.row.createCell( index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
HSSFCellStyle cellStyle = workbook.createCellStyle(); //建立新的cell样式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); //设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}}
解决方案 »
- 控制循环输出问题
- 配置动态ActionForm时没有出现<form-property>时什么原因?
- 关于配置oracle客户端的问题
- spring依赖注入问题~求教~
- 数据类型转换 急 在线等
- 如何将word中的数据导入到jsp页面中?100分
- 绝对菜鸟---调用webservice的HelloWorld问题: 怎样才能输入参数呢?
- 帮帮忙:怎样才能实现下拉框的关联啊?QQ:282877868~ QQ在线等
- weblogic8+jbuild9+mssql2000配置连接池
- 请问运行编译EJB,需要在什么环境下,如何配置,请给出详细的说明,谢谢!在线......
- ssh 中action bean的接口参数总不能传入,why?
- java代码清除IE下面的临时 文件
String font, int fontSize, String encoding, String equName,
String kpiName) {
int delIndex = -1;
XLSExport e = new XLSExport(filePath);
e.createRow(0);
for (int i = 0; i < fields.length; i++) {
e.setCell(i, fields[i]); if (fields[i].endsWith("TIME") || fields[i].endsWith("timestamp")) {
delIndex = i;
}
}
if (null != dataList && dataList.size() > 0) {
for (int i = 0; i < dataList.size(); i++) {
List<String> list = (List<String>) dataList.get(i);
e.createRow(i + 1);
for (int j = 0; j < list.size(); j++) {
if (delIndex >= 0 && delIndex == j) {
e.setCell(j,format(kpiTable, list.get(j)));
} else {
e.setCell(j, list.get(j));
}
}// for end
}// for end
}// end
try {
e.exportXLS();
System.out.println("导出excel文件成功 ");
} catch (Exception e1) {
System.out.println("导出excel文件失败 ");
e1.printStackTrace();
}
}
这段代码有问题 但是我不知道要怎么改
request.setCharacterEncoding("gbk");
response.setContentType("application/x-download;charset=gbk");
String fileName = "Item.xls";//导出文件,默认文件名
response.reset();
response.setContentType("application/ms-excel;charset=gbk");
response.setHeader("Content-disposition", "attachment;filename =" + URLEncoder.encode(fileName.substring(fileName.lastIndexOf("/") + 1), "gbk"));
fos = response.getOutputStream();
wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
// 设置Sheet名
wb.setSheetName(0, "sheet");
javax.sql.RowSet Rs = (javax.sql.RowSet) rs.getRowSet();//查询数据
javax.sql.RowSet RsCount = (javax.sql.RowSet) rsCount.getRowSet();//得到数量
// javax.sql.RowSet RsZhiCardsDown = (javax.sql.RowSet) zhiCardsDown.getRowSet();
javax.sql.RowSet RsallRzMoney = (javax.sql.RowSet) allRzMoney.getRowSet();//直充入账合计
short i = 0;//行数
HSSFRow rofirst = s.createRow(i);
HSSFCell cellfirst = rofirst.createCell(i);
cellfirst.setEncoding(HSSFCell.ENCODING_UTF_16);
cellfirst.setCellValue("自 "+cardsModel.getStartT()+" 到 "+cardsModel.getEndT()+" 直充账务报表(因联通无对账,本报表不包含联通超时和失败记录)");
i = 1 ;
//创建标题
HSSFRow ro = s.createRow(i);
for (short j = 0; j < 12; j++) {
// 创建单元格
HSSFCell cell = ro.createCell(j);
if (j == 0) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("客户名称");
} else if (j == 1) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("隶属");
} else if (j == 2) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("银行卡号");
} else if (j == 3) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("款项类型");
} else if (j == 4) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("期初余额");
} else if (j == 5) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("金额");
} else if (j == 6) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("下载类型");
} else if (j == 7) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("下载面值");
} else if (j == 8) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("成交单价");
} else if (j == 9) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("数量");
} else if (j == 10) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("成交金额合计");
} else if (j == 11) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("期末余额");
}
}
List listName = new ArrayList();
List listRz = new ArrayList();
List listMoney = new ArrayList();//存放起初余额
List listMoney1 = new ArrayList();//存放期末余额
String aCount = "0";
int state = 0;
int rzState = 0;
int qcState = 0;//是否写有起初余额,0--需要合计;1--无需合计
while (Rs.next()) {
// 创建行
if(listName.size()>0){
if(!listName.get(0).equals(StringUtil.convertNullToString(Rs.getString("mchntid"))) && qcState == 0){
HSSFRow row = s.createRow(i + 1);
for (short j = 0; j < 12; j++) {
// 创建单元格
HSSFCell cell = row.createCell(j);
if (j == 0) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("合计");
} else if (j == 1) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 2) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 3) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 4) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if(!"".equals(listMoney.get(0).toString())){
cell.setCellValue(Double.parseDouble(listMoney.get(0).toString())/100);
}else{
cell.setCellValue(0);
}
} else if (j == 5) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
aCount = "0" ;
RsCount.beforeFirst();
while(RsCount.next()){
if((listName.get(0).toString().trim()).equals(RsCount.getString("mchntid").trim())){
aCount = StringUtil.convertNullToString(RsCount.getString("allcount"));
}
}
String sql = "" ;
sql = sql + "SUM(F"+(i+2-listRz.size()-Short.parseShort(aCount))+":F"+(i+1)+")";
cell.setCellFormula(sql);
} else if (j == 6) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 7) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 8) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 9) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("");
} else if (j == 10) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
String sql = "" ;
sql = sql + "SUM(K"+(i+2-listRz.size()-Short.parseShort(aCount))+":K"+(i+1)+")";
cell.setCellFormula(sql);
} else if (j == 11) {
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// String sql = "" ;
// sql = sql + "E"+(i+2)+"+F"+(i+2)+"+K"+(i+2);
// cell.setCellFormula(sql);
// if("".equals(StringUtil.convertNullToString(Rs.getString("endmoney")))){
// cell.setCellValue(0);
// }else{
// cell.setCellValue(Double.parseDouble(StringUtil.convertNullToString(Rs.getString("endmoney")))/100);
// }
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if(!"".equals(listMoney1.get(0).toString())){
cell.setCellValue(Double.parseDouble(listMoney1.get(0).toString())/100);
}else{
cell.setCellValue(0);
}
}
}
i++;
state = 1;
rzState = 0;
}
}
HSSFRow row = s.createRow(i + 1);
for (short j = 0; j < 12; j++) {
// 创建单元格
HSSFCell cell = row.createCell(j);
if (j == 0) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("mchntname")));
listName.clear();
listName.add(StringUtil.convertNullToString(Rs.getString("mchntid")));
} else if (j == 1) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("mchntdirtpna")));
} else if (j == 2) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("accountno")));
} else if (j == 3) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if("移动".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"联通".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"电信".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())){
cell.setCellValue("充值扣款");
qcState = 0 ;
}else if(!"".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())){
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("recvflg")));
qcState = 0 ;
}else{
cell.setCellValue("");
}
} else if (j == 4) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
listMoney.clear();
listMoney.add(StringUtil.convertNullToString(Rs.getString("money")));
if("移动".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"联通".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"电信".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())){
cell.setCellValue("");
}else if(!"".equals(StringUtil.convertNullToString(Rs.getString("recvflg")))){
cell.setCellValue("");
}else{
if("".equals(StringUtil.convertNullToString(Rs.getString("money")))){
cell.setCellValue(0);
}else{
cell.setCellValue(Double.parseDouble(StringUtil.convertNullToString(Rs.getString("money")))/100);
}
qcState = 1 ;
}
} else if (j == 5) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if("移动".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"联通".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"电信".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())){
cell.setCellValue("");
}else if(!"".equals(StringUtil.convertNullToString(Rs.getString("recvflg")))){
if("".equals(StringUtil.convertNullToString(Rs.getString("rzMoney")))){
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("rzMoney")));
}else{
cell.setCellValue(Double.parseDouble(StringUtil.convertNullToString(Rs.getString("rzMoney")))/100);
}
}else{
cell.setCellValue("");
}
} else if (j == 6) {
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if("移动".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"联通".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())
||"电信".equals(StringUtil.convertNullToString(Rs.getString("recvflg")).trim())){
cell.setCellValue(StringUtil.convertNullToString(Rs.getString("recvflg")).trim());
}else if(!"".equals(StringUtil.convertNullToString(Rs.getString("recvflg")))){
cell.setCellValue("");
}else{
cell.setCellValue("");
}
楼主可以参考下