You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2011/04/19 08:41:20 UTC
DO NOT REPLY [Bug 51083] New: Issue with VBA Macro in Excel
2003/2007
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: dev@poi.apache.org
ReportedBy: kasala.asha@gmail.com
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 51083] Issue with VBA Macro in Excel 2003/2007
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51083
Asha K S <ka...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |kasala.asha@gmail.com
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 51083] Issue with VBA Macro in Excel 2003/2007
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51083
--- Comment #3 from Asha K S <ka...@gmail.com> 2011-04-25 03:22:59 EDT ---
We are setting the cell in "data" sheet to "Year" and cell in "deputation"
sheet to "principal" ."guesstimate" sheet has macro defined which should show
Year,principal,Year-principal but it doesnt show in Excel versions
2002/2003,2007 but works fine in Excel 2007 SP2
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 51083] Issue with VBA Macro in Excel 2003/2007
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51083
--- Comment #2 from Asha K S <ka...@gmail.com> 2011-04-25 02:32:18 EDT ---
The following is the behaviour in different versions of Excel -
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
Want to know if there is some issue with Excel or in POI because the same code
works fine and shows Year,principal,Year-principal in excel2007 SP2(Macro works
fine)
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 51083] Issue with VBA Macro in Excel 2003/2007
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51083
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Nick Burch <ni...@alfresco.com> 2011-04-19 07:24:31 EDT ---
What are you seeing, and what are you expecting to see?
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org