hi MSB
Sorry , it's my fault!
I am developing a soft by eclipse RAP, the follow is my code that
generate xls file by POI:
try {
result =
File.createTempFile(String.valueOf(System.currentTimeMillis()),".xls");
HSSFWorkbook wb = new HSSFWorkbook();
// wb.setPrintArea(0, "$A$1:$h$31");
FileOutputStream fileOut = new FileOutputStream(result);
sheet = wb.createSheet();
sheet.createFreezePane(0, 0);
// HSSFPrintSetup ps = sheet.getPrintSetup();
// sheet.setAutobreaks(true);
// ps.setFitHeight((short)1);
// ps.setFitWidth((short)1);
sheet.getPrintSetup().setLandscape(true);
//
sheet.setColumnWidth( 0, 4000);
sheet.setColumnWidth( 1, 5500);
sheet.setColumnWidth( 2, 4500);
sheet.setColumnWidth( 3, 4000);
sheet.setColumnWidth( 4, 4000);
sheet.setColumnWidth( 5, 5000);
sheet.setColumnWidth( 6, 5000);
sheet.setColumnWidth( 7, 4500);
ExcelUtil.generateExcelCompanyInfo(sheet, wb, (short)
7);
int rownum = 4;
rownum++;
ExcelUtil.createRowByHeight(sheet, wb, (short)
(rownum++), (short)
14, "JOB SUMMARY INVOICE",
HSSFCellStyle.ALIGN_CENTER,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 450,
(short) 7);
sheet.addMergedRegion(new CellRangeAddress(rownum,
rownum, 0, 7));
rownum++;
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 9, "Client
Name:" + clientName,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRowWithTwoCell(sheet, wb, (short)
(rownum++), (short)
9,
HSSFCellStyle.ALIGN_LEFT, serviceType,
printDate);
/
generTitle(sheet, wb, rownum, bean);
rownum += 2;
generValue(sheet, wb, rownum, values);
rownum = values.size() + rownum;
ExcelUtil.createRowBytotal(sheet, wb, (short)
(rownum++), (short) 8,
HSSFCellStyle.ALIGN_LEFT,
bean);
rownum++;
ExcelUtil
.createRow(
sheet,
wb,
(short) (rownum++),
(short) 10,
"Remarks: All dates
specified in this invoice refer to the date
at client's (Merrill Brink International) site.",
HSSFCellStyle.ALIGN_LEFT, HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_NORMAL,
(short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 14,
legalPerson,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_NORMAL, (short) 7);
rownum++;
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
"Payee’s Bank Information: ",
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
payeeBankInformation,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
rownum++;
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
"Account Name: ",
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
accountName,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_NORMAL, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
"Account Number: ",
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
accountNumber,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10, "Swift
Code: ",
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
ExcelUtil.createRow(sheet, wb, (short) (rownum++),
(short) 10,
swiftCode,
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.BLACK.index,
HSSFFont.BOLDWEIGHT_BOLD, (short) 7);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
result = null;
e.printStackTrace();
} catch (IOException e) {
result = null;
e.printStackTrace();
}
return result;
ExcelUtil.java
package net.heartsome.ils.projectmanagement.service.report.excel.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import net.heartsome.gms.filemanagement.util.Configuration;
import
net.heartsome.ils.projectmanagement.service.report.excel.formbean.InvoiceExcelBean;
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.HSSFRichTextString;
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 org.apache.poi.hssf.util.Region;
public class ExcelUtil {
public ExcelUtil() {
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
}
public static String generateFilePath(File file) {
String result = "";
if (file != null) {
String domain = Configuration.getInstance().getDomain();
String target =
Configuration.getInstance().getDownloadFileLocation();
String separator = System.getProperty("file.separator");
target += separator + file.getName();
BufferedOutputStream bos = null;
BufferedInputStream bis = null;
try {
bos = new BufferedOutputStream(new
FileOutputStream(target));
bis = new BufferedInputStream(new
FileInputStream(file));
byte[] buffer = new byte[1024];
int index = -1;
while ((index = bis.read(buffer)) != -1) {
bos.write(buffer, 0, index);
}
result = domain + separator + file.getName();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
file.delete();
bis.close();
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return result;
}
/**
* 生成特定的单元格
*
* @param style
* @param row
* @param index
* @return
*/
public static HSSFCell getHSSFCell(HSSFCellStyle style, HSSFRow row,
short index) {
HSSFCell cellText = row.createCell((short) index++);
cellText.setCellStyle(style);
cellText.setCellType(HSSFCell.CELL_TYPE_STRING);
return cellText;
}
/**
* 提供Excel报表的列头样式
*
* @param wb
* @return HSSFCellStyle
*/
public static HSSFCellStyle generateTilteStyle(HSSFWorkbook wb) {
HSSFCellStyle styleTitle = wb.createCellStyle();
styleTitle.setFillPattern(HSSFCellStyle.DIAMONDS);
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
// styleTitle.setFillBackgroundColor(HSSFColor.RED.index);
styleTitle.setFillForegroundColor(HSSFColor.BLACK.index);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleTitle.setFont(font);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
return styleTitle;
}
/**
* 生成公用的value的样式
*
* @param wb
* @return
*/
public static HSSFCellStyle generateValueStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
return style;
}
public static HSSFCellStyle generateValueStyleCenter(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
return style;
}
/**
* 生成列表的title
*
* @param sheet
* @param wb
* @param rownum
* @param lstTitle
*/
public static void generTitle(HSSFSheet sheet, HSSFWorkbook wb, int
rownum, String[] arrSelectField) {
int cellIndex =0;
HSSFCellStyle styleTitle =ExcelUtil.generateTilteStyle(wb);
HSSFRow row = sheet.createRow(rownum);
for (int i=0;i<arrSelectField.length;i++) {
ExcelUtil.getHSSFCell(styleTitle, row, (short) cellIndex
++).setCellValue(new HSSFRichTextString(arrSelectField[i]));
}
// // 固定的列表title
// List<String> lstTitle = new ArrayList<String>();
// for (int i = 0; i < arrSelect.length; i++) {
// lstTitle.add(arrSelect[i].toString());
// }
//
// // 设置title
// for (int i = 0; i < lstTitle.size(); i++) {
//
// // 设置title的样式 灰色
// HSSFCellStyle styleTitle = generateTilteStyle(wb);
// HSSFRow row = sheet.createRow(rownum);
// row.setHeightInPoints(15);
// HSSFCell cellTitle = row.createCell((short) i);
// cellTitle.setCellStyle(styleTitle);
// cellTitle.setCellValue(new
HSSFRichTextString(lstTitle.get(i).toString()));
// }
}
/**
* 生成报表excel文件的公司信息部分
*
* @param sheet
* @param wb
*/
public static void generateExcelCompanyInfo(HSSFSheet sheet,
HSSFWorkbook wb, short widthNum) {
// 公司信息
ReportCompanyInfo companyInfo = ReportCompanyInfo.getInstance();
// 定义一个常量，用来保存行数
int rownum = 0;
// 开始构建固定行
createRow(sheet, wb, (short) (rownum++), (short) 14,
companyInfo.getName(), HSSFCellStyle.ALIGN_LEFT,
HSSFColor.GREY_50_PERCENT.index,
HSSFFont.BOLDWEIGHT_BOLD,
widthNum);
createRow(sheet, wb, (short) (rownum++), (short) 8,
companyInfo.getAddress(), HSSFCellStyle.ALIGN_LEFT,
HSSFColor.GREY_25_PERCENT.index,
HSSFFont.BOLDWEIGHT_NORMAL,
widthNum);
int lenPhone = companyInfo.getPhone().length();
int lenFax = companyInfo.getFax().length();
createRowWithDistinctFont(sheet, wb, (short) (rownum++),
(short) 8,
"Tel:" + companyInfo.getPhone() +" Fax:"
+ companyInfo.getFax(),
HSSFCellStyle.ALIGN_LEFT,
HSSFColor.GREY_25_PERCENT.index,
HSSFFont.BOLDWEIGHT_NORMAL, new
int[] { 3,
3 + lenPhone+4, 3 + lenPhone +
4+4,
3 + lenPhone + 4 +4+ lenFax+1
}, widthNum);
int lenSite = companyInfo.getWebSite().length();
int lenMail = companyInfo.getEmail().length();
createRowWithDistinctFont(sheet, wb, (short) (rownum++),
(short) 8,
"Web Site:" + companyInfo.getWebSite()
+ " E-Mail:" + companyInfo.getEmail(),
HSSFCellStyle.ALIGN_LEFT,
// "Web Site: www.i-len.com E-Mail: [email protected]"
HSSFColor.GREY_25_PERCENT.index,
HSSFFont.BOLDWEIGHT_NORMAL, new
int[] { 8,
8+4 +lenSite, 8+4+7+lenSite ,
8+4+7+lenSite+ lenMail+1 },
widthNum);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum,
(short)
widthNum));
// 这里要空一行
}
/**
* @author Roy
* @param sheet
* HSSFSheet对象
* @param wb
* HSSFWorkbook对象
* @param rownum
* 第几行
* @param fontheight
* 字体大小
* @param title
* 文本内容
* @param alignment
* 对齐方式
* @param color
* 字体颜色
* @param boldweight
* 是否粗体
*/
public static void createRow(HSSFSheet sheet, HSSFWorkbook wb, short
rownum, short fontheight, String title,
short alignment, short color, short boldweight, short
widthNum) {
HSSFRow row = sheet.createRow(rownum);
HSSFFont font = wb.createFont();
HSSFCellStyle style = wb.createCellStyle();
font.setFontHeightInPoints(fontheight);
font.setFontName("Verdana");
font.setColor(color);
font.setBoldweight(boldweight);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(new HSSFRichTextString(title));
style.setFont(font);
style.setWrapText(true);
style.setAlignment(alignment);
cell.setCellStyle(style);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum,
widthNum));
}
/**
* @author Roy
* @param sheet
* HSSFSheet对象
* @param wb
* HSSFWorkbook对象
* @param rownum
* 第几行
* @param fontheight
* 字体大小
* @param title
* 文本内容
* @param alignment
* 对齐方式
* @param color
* 字体颜色
* @param boldweight
* 是否粗体
*/
public static void createRowByHeight(HSSFSheet sheet, HSSFWorkbook wb,
short rownum, short fontheight,
String title, short alignment, short color, short
boldweight, short
height, short widthNum) {
HSSFRow row = sheet.createRow(rownum);
row.setHeight(height);
HSSFFont font = wb.createFont();
HSSFCellStyle style = wb.createCellStyle();
font.setFontHeightInPoints(fontheight);
font.setFontName("Verdana");
font.setColor(color);
font.setBoldweight(boldweight);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(new HSSFRichTextString(title));
style.setFont(font);
style.setWrapText(true);
style.setAlignment(alignment);
cell.setCellStyle(style);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum,
widthNum));
}
/**
* 一行中有两个单元格
*
* @param sheet
* HSSFSheet对象
* @param wb
* HSSFWorkbook对象
* @param rownum
* 第几行
* @param fontheight
* 字体大小
* @param alignment
* 对齐方式
* @param serviceType
* @param date
*/
public static void createRowWithTwoCell(HSSFSheet sheet, HSSFWorkbook
wb, short rownum, short fontheight,
short alignment, String serviceType, String date) {
HSSFRow row = sheet.createRow(rownum);
HSSFFont font = wb.createFont();
HSSFCellStyle style = wb.createCellStyle();
font.setFontHeightInPoints(fontheight);
font.setFontName("Verdana");
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCell cell1 = row.createCell((short) 0);
HSSFCell cell2 = row.createCell((short) 6);
cell1.setCellValue(new HSSFRichTextString("Service type:" +
serviceType));
style.setFont(font);
style.setAlignment(alignment);
cell1.setCellStyle(style);
cell2.setCellValue(new HSSFRichTextString("Invoice Issue Date:"
+
date));
cell2.setCellStyle(style);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum,
(short)
5));
sheet.addMergedRegion(new Region(rownum, (short) 6, rownum,
(short)
7));
}
/**
* 创建总计行的方法
*
* @param sheet
* HSSFSheet对象
* @param wb
* HSSFWorkbook对象
* @param rownum
* 第几行
* @param fontheight
* 字体大小
* @param alignment
* 对齐方式
* @param bean
*/
public static void createRowBytotal(HSSFSheet sheet, HSSFWorkbook wb,
short rownum, short fontheight,
short alignment, InvoiceExcelBean bean) {
HSSFRow row = sheet.createRow(rownum);
HSSFFont font = wb.createFont();
HSSFCellStyle style = wb.createCellStyle();
// style.setFillPattern(HSSFCellStyle.DIAMONDS);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// style.setFillBackgroundColor(HSSFColor.WHITE.index);
font.setFontHeightInPoints(fontheight);
font.setFontName("Verdana");
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// style.setFont(font);
// style.setAlignment(alignment);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setFont(font);
style.setWrapText(true);
style.setAlignment(alignment);
HSSFCell cell1 = row.createCell((short) 0);
HSSFCell cell2 = row.createCell((short) 7);
cell1.setCellValue(new HSSFRichTextString("Subtotal(" +
bean.getCurrencyDesc() + ") "));
cell1.setCellStyle(style);
cell2.setCellValue(new HSSFRichTextString(bean.getSubTotal()));
cell2.setCellStyle(style);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum + 1,
(short) 6));
sheet.addMergedRegion(new Region(rownum, (short) 7, rownum + 1,
(short) 7));
row = sheet.createRow(rownum + 1);
row.createCell((short) 0).setCellStyle(style);
row.createCell((short) 7).setCellStyle(style);
}
/**
* 该方法是用来在一个cell中设置两种字体
*
* @param sheet
* @param wb
* @param rownum
* @param fontheight
* @param title
* @param alignment
* @param color
* @param boldweight
* @param arg
* 索引数组
*/
public static void createRowWithDistinctFont(HSSFSheet sheet,
HSSFWorkbook wb, short rownum, short fontheight,
String title, short alignment, short color, short
boldweight, int[]
arg, short widthNum) {
HSSFRow row = sheet.createRow(rownum);
HSSFFont font = wb.createFont();
HSSFCellStyle style = wb.createCellStyle();
font.setFontHeightInPoints(fontheight);
font.setFontName("Verdana");
font.setColor(color);
// font.setBoldweight(boldweight);
HSSFFont fontBlod = wb.createFont();
fontBlod.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBlod.setColor(HSSFColor.GREY_50_PERCENT.index);
// int[] arg ={8,24,32,48}
HSSFRichTextString hssftitle = new HSSFRichTextString(title);
hssftitle.applyFont(0, arg[0], fontBlod);
hssftitle.applyFont(arg[0], arg[1], font);
hssftitle.applyFont(arg[1], arg[2], fontBlod);
hssftitle.applyFont(arg[2], arg[3], font);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(hssftitle);
style.setFont(font);
style.setWrapText(true);
style.setAlignment(alignment);
cell.setCellStyle(style);
sheet.addMergedRegion(new Region(rownum, (short) 0, rownum,
widthNum));
}
}
On Tue, 2009-02-24 at 06:56 -0800, MSB wrote:
> Hello Roy
>
> Sorry to say this but unless you give us some more information, I doubt
> anyone is going to be able to help you.
>
> Can you post your code please? If the program is long and complex, can you
> re-produce the problem from just a few lines of code? Can you tell me how
> you are generating the file; is it a stand alone application, are you using
> a web server, etc?
>
> The more you can tell us, the better.
>
>
> Roy Xiao wrote:
> >
> > Hi all:
> >
> > I generate a xls file by POI 3.5 in Linux, when i open this file in the
> > window xp, the system popup a message "file error: the data maybe lost
> > ", why ? thanks!
> >
> >
> > Best Regards!
> >
> > Roy
> >
> > 2009-02-24
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [email protected]
> > For additional commands, e-mail: [email protected]
> >
> >
> >
>
