You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by shankarrao <sh...@gmail.com> on 2011/01/07 15:58:48 UTC
Equivalent methods in Apache POI
Hi,
We have used jExcel API in our application for reading and writing of Excel
files. Now we want to switch to Apache POI to provide users with support for
Excel 2007/2010 formats. As part of this, we want to learn on determining
the equivalent methods of jExcel api in Apache POI. Below is one such method
in our code.
jxl.WorkbookSettings ws = new jxl.WorkbookSettings.WorkbookSettings();
What would be the equivalent method for the above in Apache POI?
Could you please point me to a tutorial where i can determine the
equivalents for methods used in jExcel API to be used in Apache POI.
Regards,
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3331946.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Looks like I was more than a little mistaken - at least from the point of
performing basic worksheet protection in the manner that you require. It is
possible to accomplish everything you wish using the SS model and so only
one codebase will need to be maintained irrespective of the workbooks type.
Pleas have a look at the class below - code that demonstartes how the class
works can be found in the main() method.
package workbookprotection;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Random;
/**
*
* @author win user
*/
public class Main {
protected static void protectWorkbook(String filename, boolean openXML)
{
File file = null;
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
String cellContents = null;
CellStyle lockedNumericStyle = null;
CellStyle unlockedTextStyle = null;
Random randomNumberGen = null;
try {
// Random number generator for the numeric cell
randomNumberGen = new Random(System.currentTimeMillis());
// This just allows me to use one method to demonstrate that both
// binary and SpreadsheetlML files can be handled with the exact
// same code. Such an approch will not be necessary in a real
// application.
if(openXML) {
workbook = new XSSFWorkbook();
}
else {
workbook = new HSSFWorkbook();
}
// Cell styles. Note the setLocked(true) method call here.
lockedNumericStyle = workbook.createCellStyle();
lockedNumericStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
lockedNumericStyle.setLocked(true);
unlockedTextStyle = workbook.createCellStyle();
unlockedTextStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// and the false call here. Evn after the worksheet is protected any
cells
// to which the unloked style is applied will be editable.
unlockedTextStyle.setLocked(false);
sheet = workbook.createSheet("Protection Test");
for(int i = 0; i < 10; i++) {
row = sheet.createRow(i);
cell = row.createCell(0);
cellContents = ("First Cell in row " + (i + 1));
cell.setCellValue(cellContents);
// Apply the unlocked style to all of the cells in column A. Even
after
// the worksheet is pritected, the user will be able to edit any
and
// all of the cells this style was applied to.
cell.setCellStyle(unlockedTextStyle);
cell = row.createCell(1);
cell.setCellValue(randomNumberGen.nextDouble() * 1000);
// Apply the locked style to all of the cells in column B. One the
worksheet
// is protceted, the user will not be able to edit any of the cells
in this
// column unless they know the correct password.
cell.setCellStyle(lockedNumericStyle);
}
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
// Calling this method means that any cells a locked style was applied
to will
// be protected, i.e. the user will not be able to change their
contents. If the
// method is not called then even those cells a locked style was
applied to will
// be editable.
sheet.protectSheet("Password");
file = new File(filename);
fos = new FileOutputStream(file);
workbook.write(fos);
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows............");
ex.printStackTrace(System.out);
}
finally {
if(fos != null) {
try {
fos.close();
fos = null;
}
catch(IOException ioEx) {
// I G N O R E
}
}
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// The second parameter allows thge same code to create binary
workbooks - pass
// false as the second parameter - and SpearsheetML ones - pass true
as the
// second parameter.
Main.protectWorkbook("C:/temp/Multi Write Test.xls", false);
Main.protectWorkbook("C:/temp/Multi Write Test.xlsx", true);
}
}
My confusion arose because it is possible to exercise finer grained control
over the cells proection using XSSF. For example, it is possible to allow
the user to, or prevent them from, modifying the format applied to the cell.
That sort of control is not possible in the earlier format - HSSF - and this
was the cause of my confusion.
Finally, sorry but I do not know anything about freeze panes and would
suggest you open another thread to ask about this specifically.
Yours
Mark B
PS If you have lots of questions to ask, then it is best to create a new
thread for each different topic; i.e. one for the worksheet protection
question, another for the freeze pane one. That way. lots of list members
will be able to see what you are asking and make a contribution to the
discussion; as an example, a casual glance at the list would suggest that we
are discussing conversion from JExcel to POI, not protecting worksheets. In
addition, I will be away for the next few days and so you will need to
ensure that your questions are looked at my as many different people as
possible.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3339741.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by shankarrao <sh...@gmail.com>.
Mark,
Thanks so much for your response.
Will wait for your answer on how to set password for a worksheet.
Also wanted to know if this is the way to set horizontalFreeze on a sheet:
sheet.createFreezePane(0, 1);
Thanks!
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3338476.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Hello again,
1) Will my new worksheet be created as the first sheet always? Can i specify
the index of the sheet when creating it? Say workbook.createSheet("sheet1",
0) etc. Please clarify.
The worksheet wil always be created as the sheet at the next available index
number. So, when you create a new workbook, the first call to the
createSheet(String) method will create the equivalent of 'Sheet 1' in Excel
terms, the next call will create 'Sheet 2' and so on. There are only two
watys to reate sheets using the SS usermodel and they are createSheet() and
createSheet(String) and currently, there is no method to allow you to
specify the index number of the sheet. What you cold do if it is imprtant to
be able to determine where in the workbook the sheet appears is to use the
setSheetOrder(String, int) to specify this. The two parameters to this
method are the name of the sheet and the postion you want it to appear in
the workbook. So, imagine you wanted to create a new sheet and insert it
into an existing workbook as the second sheet, you would do something like
this;
Sheet newSheet = workbook.createSheet("New Sheet");
workbook.setSheetOrder("New Sheet", 1);
Remember that sheets are indexed from zero and not from one.
2) How do i password protect the entire worksheet? is there a method defined
for achieving this feature? Please let me know.
There is no easy answer to this question as the technique will differ
depenind upon whether you are dealing with the older binary format (HSSF) or
the more recent SpreadsheetML file format (XSSF). Off of the top of my head,
I cannot remember the exact steps for both and will need time to look over
some olde code before answering. I do know that HSSF requires you to call
the setLocked(boolean) method for each cell style but I cannot remember off
hand the second step in the process. I do not think it currently support
locking at the worksheet level but rather at the cell level also. XSSF
supports finer grained locking that I have explained elsewhere so I know I
have the code for this and can post it later. The drawbacks are obvious
though; you will need to maintain two different codebases to support the
different types of protection.
Will post again later.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3338391.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by shankarrao <sh...@gmail.com>.
Thanks Mark.
We are using SS user model for reading & writing the Excel files. As part of
this, we have created the workbook using:
workbook = WorkbookFactory.create(new FileInputStream(filename));
The next step is to create a sheet:
Sheet s1 = workbook.createSheet("MySheet");
Below are my questions on the above.
1) Will my new worksheet be created as the first sheet always? Can i specify
the index of the sheet when creating it? Say workbook.createSheet("sheet1",
0) etc. Please clarify.
2) How do i password protect the entire worksheet? is there a method defined
for achieving this feature? Please let me know.
Thanks in advance!
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3337913.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Whilst I am happy to help by answering specific questions relating to POI, I
do not have the time to devote to this sort of project, sorry. Further, I am
not an expert in using JExcel.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3336369.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by shankarrao <sh...@gmail.com>.
Thanks Paul and Mark for your responses.
Please find the attached file
http://apache-poi.1045710.n5.nabble.com/file/n3336022/ExcelWrite.java
ExcelWrite.java for the code snippet we are using in our application. This
is coded using jExcel API. Now i want to rewrite the same code block using
Apache POI. Could you please help me in this regard. This will help me in
moving forward and resolving the other methods in our code.
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3336022.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Further to Paul's reply, also take a look at the Examples menu option that is
available from that same page. That takes you to many complete examples of
using POI to build - for example - an invoice.
With reyard to the WorkbookSettings clas, I do not think there is a direct
analogue in POI. Instead, you will need to use the javadocs for the api to
serach for similar methods; some you will find and some you will not as the
two apis are not simple equivalents.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3332212.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Equivalent methods in Apache POI
Posted by Paul Spencer <pa...@apache.org>.
Shankar,
I suggest you review the Excel Component API part of the POI website, http://poi.apache.org/spreadsheet/index.html. The Quick Guide and HOWTO pages will answer many of your question.
Paul Spencer
On Jan 7, 2011, at 9:58 AM, shankarrao wrote:
>
> Hi,
>
> We have used jExcel API in our application for reading and writing of Excel
> files. Now we want to switch to Apache POI to provide users with support for
> Excel 2007/2010 formats. As part of this, we want to learn on determining
> the equivalent methods of jExcel api in Apache POI. Below is one such method
> in our code.
>
> jxl.WorkbookSettings ws = new jxl.WorkbookSettings.WorkbookSettings();
>
> What would be the equivalent method for the above in Apache POI?
> Could you please point me to a tutorial where i can determine the
> equivalents for methods used in jExcel API to be used in Apache POI.
>
> Regards,
> Shankar
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Equivalent-methods-in-Apache-POI-tp3331946p3331946.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org