OK Roy, got that. I am at work now so will not be able to try anything out
until I get home - we are an MS Office free zone now thank goodness.
Have you tried simplifying the code at all to see what may cause the error
to be flagged by Excel?
Roy Xiao wrote:
>
> 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]
>> >
>> >
>> >
>>
>
>
--
View this message in context:
http://www.nabble.com/the-data-maybe-lost%21-tp22177059p22205702.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
