https://issues.apache.org/bugzilla/show_bug.cgi?id=51083
Summary: Issue with VBA Macro in Excel 2003/2007
Product: POI
Version: 3.6
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: [email protected]
ReportedBy: [email protected]
Created an attachment (id=26903)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26903)
Input excel sheet
Results -
Excel2002/2003 ： 0, 0, -
Excel2007 : 0, 0, -
(If you save the spreadsheet after open it, the data is saved
correctly.[Year,principal,Year-principal])
Excel2007 SP2 : Year,principal,Year-principal
import org.apache.poi.hssf.usermodel.HSSFOptimiser;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
public class ExcelTest {
public static void main(String[] args) throws Exception
{
try{
InputStream inp = new FileInputStream("C:\\Guesstimate.xls");
Workbook workbook = WorkbookFactory.create(inp);
int sheetnumber = workbook.getSheetIndex("data");
Sheet sheet = workbook.getSheetAt(sheetnumber);
setCellValue(workbook,sheet,"Year", 1, 1);
writeExcel("C:\\guesstimateSup.xls",workbook,sheetnumber,"data",null,true);
}
catch(Exception e)
{
throw e;
}
}
public static void setCellValue(Workbook workbook,Sheet sheet1,String value,int
row,int column) throws Exception
{
try
{
Workbook book = workbook;
CreationHelper createHelper = book.getCreationHelper();
Sheet sheet = sheet1;
Row hssfrow = sheet.getRow(row-1);
if (hssfrow == null)
{
hssfrow = sheet.createRow(row-1);
}
Cell c = hssfrow.getCell(column-1);
CellStyle style= null;
if (c != null)
{
style = c.getCellStyle()!=null?c.getCellStyle():null;
hssfrow.removeCell(c);
}
c = hssfrow.createCell(column-1);
if (style !=null )
c.setCellStyle(style);
try
{
if (style!=null &&
style.getDataFormatString().equalsIgnoreCase("@"))
setCellStringValue(sheet,c,column,value,createHelper);
else
{
double i = Double.parseDouble(value);
c.setCellType(Cell.CELL_TYPE_NUMERIC);
c.setCellValue(i);
}
}catch(NumberFormatException e)
{
setCellStringValue(sheet,c,column,value,createHelper);
}
// Add back the modified excel object.
}catch(Exception e)
{
throw e;
}
}
private static void setCellStringValue(Sheet sheet,Cell c,int column,String
value,CreationHelper createHelper)
{
if (!value.equalsIgnoreCase(""))
{
c.setCellType(Cell.CELL_TYPE_STRING);
RichTextString str = createHelper.createRichTextString(value);
c.setCellValue(str);
int colwidth = sheet.getColumnWidth(column-1);
short len = (short)value.length();
len = (short)((len * 8) / (( double ) 1 / 20));
if (colwidth < len)
{
sheet.setColumnWidth(column - 1,len+1);
}
} else
{
c.setCellType(Cell.CELL_TYPE_BLANK);
c.setCellValue(createHelper.createRichTextString(""));
}
}
public static void writeExcel(String xlsfile,Workbook book1,int
sheetnumber1,String sheetname1,String password,boolean update) throws Exception
{
Workbook book = book1;
String sheetname = sheetname1;
int sheetnumber = sheetnumber1;
cleanStyles(book);
String action="write";
if (book != null)
{
try
{
// If we are adding to an existing workbook, Copy all
sheets from the file and
// add to the workbook created
FileOutputStream out = null;
try
{
if
(!book.getSheetName(sheetnumber).equalsIgnoreCase(sheetname))
{
book.setSheetName(sheetnumber,sheetname);
}
// Set password if provided
if (password != null)
{
Sheet sheet = book.getSheet(sheetname);
if (book instanceof HSSFWorkbook)
{
((HSSFSheet)sheet).protectSheet(password);
}
}
out = new FileOutputStream(xlsfile);
book.write(out);
}catch(IOException e)
{
throw e;
}finally
{
if (out != null)
{
try
{
out.close();
}catch(Exception e)
{
//ignore
}
}
}
}catch(Exception e)
{
throw e;
}
}
}
private static void cleanStyles(Workbook book)
{
if (book instanceof HSSFWorkbook && (book.getNumCellStyles() >=
Short.MAX_VALUE || book.getNumCellStyles()<0))
{
try
{
HSSFOptimiser.optimiseFonts((HSSFWorkbook) book);
HSSFOptimiser.optimiseCellStyles((HSSFWorkbook)book);
}catch(Exception e)
{
//Ignore
}
}
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
