第一次用jxl合成excel单个sheet ,从网上找了些资料拼出来,出现好几个奇怪的问题,菜鸟啊,哪位大哥看见了帮忙解决下,合成是能合成,包括图片,格式错乱,实际上这是复制效果
问题是一:数据大,一个2M吧,四个合成的时候就出现溢出错误,
问题二:单元格有背景色提时候,一些常用色合成没问题,但特殊的色合成后变成别的色,很奇怪
问题三:excel 有图片的时候就会出现警告,Warning: no filename property for drawing 这样的,有点晕。。
package com.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.domain.ExcelMergeParamVo;
public class ExcelEmrger {
public static void main(String[] args) {
Map<String, String> subFiles = new HashMap<String, String>();
/*subFiles.put("rate_summary.xls", "rate_summary");
subFiles.put("gsm_rate.xls", "gsm_rate");
subFiles.put("gprs_rate.xls", "gprs_rate");
subFiles.put("sms_rate.xls", "sms_rate");
subFiles.put("ss_rate.xls", "ss_rate");*/
subFiles.put("custuserstat.xls", "custuserstat");
subFiles.put("custuserstat1.xls", "custuserstat1");
ExcelMergeParamVo paramVo = new ExcelMergeParamVo();
paramVo.setFileName("emergerExcel.xls");
paramVo.setSubFiles(subFiles);
paramVo.setDirPath("D:\\report_path\\");
String message = doEmergerExcel(paramVo);
System.out.println(message);
}
@SuppressWarnings("unchecked")
public static String doEmergerExcel(ExcelMergeParamVo paramVo) {
String message = "合成成功";// 定义返回消息
WritableWorkbook wwb = null;
Map<String, String> subFiles = paramVo.getSubFiles();
if (subFiles.size() < 1) {
message = "参数设置不正确";
return message;
}
Iterator keyValuePairs = subFiles.entrySet().iterator();// 文件名
try {
wwb = Workbook.createWorkbook(new File(paramVo.getDirPath()
+ paramVo.getFileName()));
while (keyValuePairs.hasNext()) {
Map.Entry<String, String> entry = (Map.Entry) keyValuePairs.next();
String key = entry.getKey();
String value = entry.getValue();
InputStream is = new FileInputStream(paramVo.getDirPath() + key);
Workbook rwb = Workbook.getWorkbook(is);
Sheet st = rwb.getSheet(0);
wwb.importSheet(value, 0, st);
WritableSheet sheet = wwb.getSheet(0);
for (int row = 0; row < st.getRows(); row++) {
for (int col = 0; col < st.getColumns(); col++) {
Label label = new Label(col, row, st.getCell(col, row)
.getContents());
if (st.getCell(col, row).getCellFormat() != null) {
WritableCellFormat format = new WritableCellFormat(
st.getCell(col, row).getCellFormat());
label.setCellFormat(format);
sheet.addCell(label);
}
}
}
rwb.close();
}
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
message = "合成失败";
return message;
}
return message;
}
}
问题是一:数据大,一个2M吧,四个合成的时候就出现溢出错误,
问题二:单元格有背景色提时候,一些常用色合成没问题,但特殊的色合成后变成别的色,很奇怪
问题三:excel 有图片的时候就会出现警告,Warning: no filename property for drawing 这样的,有点晕。。
package com.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.domain.ExcelMergeParamVo;
public class ExcelEmrger {
public static void main(String[] args) {
Map<String, String> subFiles = new HashMap<String, String>();
/*subFiles.put("rate_summary.xls", "rate_summary");
subFiles.put("gsm_rate.xls", "gsm_rate");
subFiles.put("gprs_rate.xls", "gprs_rate");
subFiles.put("sms_rate.xls", "sms_rate");
subFiles.put("ss_rate.xls", "ss_rate");*/
subFiles.put("custuserstat.xls", "custuserstat");
subFiles.put("custuserstat1.xls", "custuserstat1");
ExcelMergeParamVo paramVo = new ExcelMergeParamVo();
paramVo.setFileName("emergerExcel.xls");
paramVo.setSubFiles(subFiles);
paramVo.setDirPath("D:\\report_path\\");
String message = doEmergerExcel(paramVo);
System.out.println(message);
}
@SuppressWarnings("unchecked")
public static String doEmergerExcel(ExcelMergeParamVo paramVo) {
String message = "合成成功";// 定义返回消息
WritableWorkbook wwb = null;
Map<String, String> subFiles = paramVo.getSubFiles();
if (subFiles.size() < 1) {
message = "参数设置不正确";
return message;
}
Iterator keyValuePairs = subFiles.entrySet().iterator();// 文件名
try {
wwb = Workbook.createWorkbook(new File(paramVo.getDirPath()
+ paramVo.getFileName()));
while (keyValuePairs.hasNext()) {
Map.Entry<String, String> entry = (Map.Entry) keyValuePairs.next();
String key = entry.getKey();
String value = entry.getValue();
InputStream is = new FileInputStream(paramVo.getDirPath() + key);
Workbook rwb = Workbook.getWorkbook(is);
Sheet st = rwb.getSheet(0);
wwb.importSheet(value, 0, st);
WritableSheet sheet = wwb.getSheet(0);
for (int row = 0; row < st.getRows(); row++) {
for (int col = 0; col < st.getColumns(); col++) {
Label label = new Label(col, row, st.getCell(col, row)
.getContents());
if (st.getCell(col, row).getCellFormat() != null) {
WritableCellFormat format = new WritableCellFormat(
st.getCell(col, row).getCellFormat());
label.setCellFormat(format);
sheet.addCell(label);
}
}
}
rwb.close();
}
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
message = "合成失败";
return message;
}
return message;
}
}