You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mamatha Kodigehalli Venkatesh <Ma...@ness.com> on 2011/02/03 10:58:13 UTC
Apache POI -Protected sheet disables the Row/Column resize
Hello,
While working with Apache POI ( XSSFWorkbook / HSSFWorkbook )
Also I have set sheet.autoSizeColumn(i);
I am using sheet.protectSheet(""), after applying this the excel columns or a rows cannot be resized in terms of their width.
Please let me know how to solve this problem.
Thanks
Mamatha
RE: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
It does not allow resizing of any columns those that may be read only and also those that are not, since we are protecting the sheet.
Even I think it is better to accept excel behavior.
Thanks again Mark
Mamatha
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Friday, February 11, 2011 2:19 PM
To: user@poi.apache.org
Subject: RE: Apache POI -Protected sheet disables the Row/Column resize
Is that allow the re-sizing of selected columns and prevent the re-sizing of
others if the workbook is password protected though? The reason I ask is
that POI cannot create files that provoke Excel to exhibit 'unusual'
behaviour. By this, I mean that you cannot do anything with POI that you
could not also do with Excel.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3380809.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
Re: Problem with OpenOffice General/GENERAL format
Posted by John Francis <jf...@his.co.uk>.
Ooops forgot to add brief workaround for those encountering the same
problem... code was taken from examples and DataFormat itself
package test;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.Format;
import java.util.Locale;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class Foo
{
private FormulaEvaluator evaluator;
private DataFormatter formatter;
private final Format generalWholeNumFormat;
public Foo()
{
generalWholeNumFormat = new DecimalFormat("#",
new DecimalFormatSymbols(Locale.getDefault()));
;
}
private void openWorkbook(InputStream in) throws ConversionException
{
try
{
// Open the workbook and then create the FormulaEvaluator and
// DataFormatter instances that will be needed to, respectively,
// force evaluation of forumlae found in cells and create a
// formatted String encapsulating the cells contents.
Workbook workbook = WorkbookFactory.create(in);
evaluator = workbook.getCreationHelper().createFormulaEvaluator();
formatter = new DataFormatter();
}
catch (IllegalArgumentException e)
{
throw new ConversionException("Source Excel file did not have
valid format", e);
}
catch (InvalidFormatException e)
{
throw new ConversionException("Source Excel file did not have
valid format", e);
}
catch (IOException e)
{
throw new ConversionException("Problem reading Excel input
stream (Possibly because InputStream should support mark/reset or is it
a PushbackInputStream)",
e);
}
finally
{
try
{
in.close();
}
catch (IOException e)
{
} // Fail silently
}
}
private String getCellValue(Cell cell)
{
if(cell != null)
{
if(cell.getCellType() != Cell.CELL_TYPE_FORMULA)
{
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
String dataFormatString =
cell.getCellStyle().getDataFormatString();
// OpenOffice sometimes saves General format as
'GENERAL' - POI does a case sensitive
// compare on this and it it is not true formats the
number up with decimal places regardless
if("GENERAL".equalsIgnoreCase(dataFormatString))
{
if(!"General".equals(dataFormatString))
{
double cellValue = cell.getNumericCellValue();
// org.apache.poi.ss.usermodel.DataFormatter
assumes it will be named "General"
// so if it is not we must workaround - this
seems to be a bug with OpenOffice
if(cellValue == Math.floor(cellValue))
{
// Its a whole number
return
this.generalWholeNumFormat.format(cellValue);
}
}
}
}
return this.formatter.formatCellValue(cell);
}
else
{
return this.formatter.formatCellValue(cell, this.evaluator);
}
}
return "";
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Is that allow the re-sizing of selected columns and prevent the re-sizing of
others if the workbook is password protected though? The reason I ask is
that POI cannot create files that provoke Excel to exhibit 'unusual'
behaviour. By this, I mean that you cannot do anything with POI that you
could not also do with Excel.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3380809.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: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Hello Mark,
Microsoft excel also does not allow to resize the columns if the workbook is password protected.
I was wondering if there is any way in POI just makes the column alone read-only without making the sheet protected.
Thanks
Mamatha
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Thursday, February 10, 2011 10:34 PM
To: user@poi.apache.org
Subject: RE: Apache POI -Protected sheet disables the Row/Column resize
That's puzzing to say the least and I am completly out of ideas at this
point.
Can I ask, do you know for certain that it is possible to do this using
Excel? If it is what are the steps that have to be followed?
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3379678.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: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
That's puzzing to say the least and I am completly out of ideas at this
point.
Can I ask, do you know for certain that it is possible to do this using
Excel? If it is what are the steps that have to be followed?
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3379678.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: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
I tried lockFormatColumns() and
enableLocking() methods on the XSSFSheet object, after setting the cellstyle as the code that I had posted last, but I still I find that unlocked columns cannot be resized.
-Mamatha
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Wednesday, February 09, 2011 11:15 PM
To: user@poi.apache.org
Subject: RE: Apache POI -Protected sheet disables the Row/Column resize
Using HSSF to build binary files, you will not be able to access this
functionality at all as there are no methods defined that support it. The
reason may be - and I am guessing here - that HSSF targets a fairly early
iteration of the binary file format and it could be that the option to lock
column widths was simply not available at the time. You have two options
here, one using macros and one - possibly - using a template file.
There is a Workbook_Change event, triggered whenever the user tries to make
a change to the workbook. You can write VBA code here to set the widths of
the columns and effectively prevent the user from changing them. The
drawback with this is that as the workbook contains macros, the user will be
notified when they open the file and offered the chance to disable the code'
that is unless you can sign macros in binary workbooks and this, I do not
know. Your second option is to try wuth a template file that has all cells
in certain columns unloced and all cells in other columns locked. When you
populate the workbook using POI, it should result in a workbook where the
user is unable to modify the column widths but I am not yet convinced this
will work and have not been able to test it.
XSSF on the other hand might - and that is might - allow you to do this. As
far as I have been able to determine from chatting to an experienced Excel
user, you protect the sheet and deselect the format columns option on that
dialog. Now, any column in which the cells are locked will have a fixed
width and the user will not be able to adjust it. I cannot play with any POI
code at the moment but I think that what you need to do is create a
CellStyle which is locked, apply this to all of the cells in the column
whose width cannot be adjusted, then call the lockFormatColumns() and
enableLocking() methods on the XSSFSheet object. As far as I can remember,
you have to do it in that order - and, onbiously, after you have populated
the sheet and set column widths. Sorry to say that I will not have the
chance to play with any code before the weekend so if you have the
opportunity to experiment can you please let us know the outcome?
Yours
Mark B
PS You will also need to create unlocked CellStyle objects to apply to cells
in those columns which you do want the user to be able to adjust.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3377984.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
RE: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Using HSSF to build binary files, you will not be able to access this
functionality at all as there are no methods defined that support it. The
reason may be - and I am guessing here - that HSSF targets a fairly early
iteration of the binary file format and it could be that the option to lock
column widths was simply not available at the time. You have two options
here, one using macros and one - possibly - using a template file.
There is a Workbook_Change event, triggered whenever the user tries to make
a change to the workbook. You can write VBA code here to set the widths of
the columns and effectively prevent the user from changing them. The
drawback with this is that as the workbook contains macros, the user will be
notified when they open the file and offered the chance to disable the code'
that is unless you can sign macros in binary workbooks and this, I do not
know. Your second option is to try wuth a template file that has all cells
in certain columns unloced and all cells in other columns locked. When you
populate the workbook using POI, it should result in a workbook where the
user is unable to modify the column widths but I am not yet convinced this
will work and have not been able to test it.
XSSF on the other hand might - and that is might - allow you to do this. As
far as I have been able to determine from chatting to an experienced Excel
user, you protect the sheet and deselect the format columns option on that
dialog. Now, any column in which the cells are locked will have a fixed
width and the user will not be able to adjust it. I cannot play with any POI
code at the moment but I think that what you need to do is create a
CellStyle which is locked, apply this to all of the cells in the column
whose width cannot be adjusted, then call the lockFormatColumns() and
enableLocking() methods on the XSSFSheet object. As far as I can remember,
you have to do it in that order - and, onbiously, after you have populated
the sheet and set column widths. Sorry to say that I will not have the
chance to play with any code before the weekend so if you have the
opportunity to experiment can you please let us know the outcome?
Yours
Mark B
PS You will also need to create unlocked CellStyle objects to apply to cells
in those columns which you do want the user to be able to adjust.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3377984.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: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
I tried searching previous posts specifically on Readonly behavior... I found the same... that it gets binded with sheet.protectSheet(""); to express the behavior.
My below code sample shows that the columns that gets locked... and it does not allow me to resize the column width...
Appreciate any suggestions/help on this.
-----Original Message-----
From: Mamatha Kodigehalli Venkatesh [mailto:Mamatha.Venkatesh@ness.com]
Sent: Friday, February 04, 2011 3:29 PM
To: POI Users List
Subject: RE: Apache POI -Protected sheet disables the Row/Column resize
Basically my requirement is I need to create few read-only columns and at the same allow user to resize the columns/rows to allow usability of excel.
I have pasted my code below:
****************************************************************************
package com.ventyx.ltk.report;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.CellReference;
/**
*
* @author mvenkate
*/
public class CreateRepReport {
private static final String[] titles = {
"LOCKED REPOSITORY", "BASE LANGUAGE TERM","TRANSLATED TERM 79","MAX TERM SIZE" , "TRANS TERM SIZE","TRANSLATION SOURCE","SOURCE TYPE","SOURCE SUB TYPE","ELEM NBR",
"PANEL ID 7","TABLE KEY","FILE NAME","COMMENTS","REPOSITORY KEY"};
private static final String[][] data = {
{"Y", "Marketing Research Tactical Plan", "J. Dow", "70", "50", "TIDROADP","sub type1",
"DAT", "TILN", "TIX2010", "my tablekey", "abcd.dat", "This is will comments", "P170012377"},
{"Y", " Research Tactical 22", "uuuDow", "74", "20", "TIDROADG", "sub type2",
"DAT", "TILN", "TIX2010", "Hellotablekey", "tidfg.dat", "comments written", "P170034347"},};
public static void createRepSheet(){
//HSSFWorkbook wb = new HSSFWorkbook();
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("Repository");
//turn off gridlines
sheet.setDisplayGridlines(true);
sheet.setPrintGridlines(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
//the following three statements are required only for HSSF
sheet.setAutobreaks(true);
printSetup.setFitHeight((short)1);
printSetup.setFitWidth((short)1);
sheet.protectSheet("");
sheet.setAutoFilter(CellRangeAddress.valueOf("A1:G20"));
//the header row: centered text in 48pt font
Row headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(17f);
for (int i = 0; i < titles.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(styles.get("header"));
sheet.autoSizeColumn(i);
}
Row row;
Cell cell;
int rownum = 1;
for (int i = 0; i < data.length; i++, rownum++) {
row = sheet.createRow(rownum);
if(data[i] == null) continue;
for (int j = 0; j < data[i].length; j++) {
cell = row.createCell(j);
if (j==1 || j==3 || j ==13)
cell.setCellStyle(styles.get("cell_locked"));
else if (j==4){
// CellReference cellRef = new CellReference(row.getRowNum(), 4);
//System.out.print(cellRef.formatAsString());
Cell TTCell = sheet.getRow(i+1).getCell(j-2);
System.out.println(TTCell.getStringCellValue().length()+"the ttcell value "+TTCell.getStringCellValue());
int len = TTCell.getStringCellValue().length();
cell.setCellFormula(String.valueOf(len));
// cell.setCellFormula("IF(Values_Entered,MAX TERM SIZE,\"\")");
// cell.setCellComment(new Comment("Exceeds "));
// $I$2:$I$6
}
else
cell.setCellStyle(styles.get("cell_normal_centered"));
cell.setCellValue(data[i][j]);
}
}
// Write the output to a file
String file = "Repository.xls";
// if(!(wb instanceof HSSFWorkbook)) file += "x";
if((wb instanceof XSSFWorkbook)) file += "x";
FileOutputStream out;
try {
out = new FileOutputStream(file);
try {
wb.write(out);
out.close();
} catch (IOException ex) {
Logger.getLogger(CreateRepReport.class.getName()).log(Level.SEVERE, null, ex);
}
} catch (FileNotFoundException ex) {
Logger.getLogger(CreateRepReport.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb){
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
DataFormat df = wb.createDataFormat();
CellStyle style;
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
styles.put("header", style);
//Mamatha styles
style = createLockedBorderedStyle(wb);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
styles.put("cell_locked", style);
return styles;
}
private static CellStyle createBorderedStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setLocked(false);
return style;
}
private static CellStyle createLockedBorderedStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setLocked(true);
return style;
}
public static void main(String[] args) {
createRepSheet();
}
}
****************************************************************************
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Thursday, February 03, 2011 10:42 PM
To: user@poi.apache.org
Subject: Re: Apache POI -Protected sheet disables the Row/Column resize
With regard to the HSSF model, you cannot, directly. It may be possible to
create a template that has the options set exactly as you want them and then
use POI to open and populate this template.
XSSF on the other hand does offer the ability to exercise this level of
control over protection. I do not have access to the code I need here but
know that if you search back through the messages on the list, protection
has been discussed already and there are examples there. I suspect if you
start about four or six months ago and work backwards from there, you will
find the relevant post.
Yours
Mark B
PS The template tricl will work for both file formats but is obviously only
useful if you are creating/populating new workbooks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3369596.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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Basically my requirement is I need to create few read-only columns and at the same allow user to resize the columns/rows to allow usability of excel.
I have pasted my code below:
****************************************************************************
package com.ventyx.ltk.report;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.CellReference;
/**
*
* @author mvenkate
*/
public class CreateRepReport {
private static final String[] titles = {
"LOCKED REPOSITORY", "BASE LANGUAGE TERM","TRANSLATED TERM 79","MAX TERM SIZE" , "TRANS TERM SIZE","TRANSLATION SOURCE","SOURCE TYPE","SOURCE SUB TYPE","ELEM NBR",
"PANEL ID 7","TABLE KEY","FILE NAME","COMMENTS","REPOSITORY KEY"};
private static final String[][] data = {
{"Y", "Marketing Research Tactical Plan", "J. Dow", "70", "50", "TIDROADP","sub type1",
"DAT", "TILN", "TIX2010", "my tablekey", "abcd.dat", "This is will comments", "P170012377"},
{"Y", " Research Tactical 22", "uuuDow", "74", "20", "TIDROADG", "sub type2",
"DAT", "TILN", "TIX2010", "Hellotablekey", "tidfg.dat", "comments written", "P170034347"},};
public static void createRepSheet(){
//HSSFWorkbook wb = new HSSFWorkbook();
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("Repository");
//turn off gridlines
sheet.setDisplayGridlines(true);
sheet.setPrintGridlines(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
//the following three statements are required only for HSSF
sheet.setAutobreaks(true);
printSetup.setFitHeight((short)1);
printSetup.setFitWidth((short)1);
sheet.protectSheet("");
sheet.setAutoFilter(CellRangeAddress.valueOf("A1:G20"));
//the header row: centered text in 48pt font
Row headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(17f);
for (int i = 0; i < titles.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(styles.get("header"));
sheet.autoSizeColumn(i);
}
Row row;
Cell cell;
int rownum = 1;
for (int i = 0; i < data.length; i++, rownum++) {
row = sheet.createRow(rownum);
if(data[i] == null) continue;
for (int j = 0; j < data[i].length; j++) {
cell = row.createCell(j);
if (j==1 || j==3 || j ==13)
cell.setCellStyle(styles.get("cell_locked"));
else if (j==4){
// CellReference cellRef = new CellReference(row.getRowNum(), 4);
//System.out.print(cellRef.formatAsString());
Cell TTCell = sheet.getRow(i+1).getCell(j-2);
System.out.println(TTCell.getStringCellValue().length()+"the ttcell value "+TTCell.getStringCellValue());
int len = TTCell.getStringCellValue().length();
cell.setCellFormula(String.valueOf(len));
// cell.setCellFormula("IF(Values_Entered,MAX TERM SIZE,\"\")");
// cell.setCellComment(new Comment("Exceeds "));
// $I$2:$I$6
}
else
cell.setCellStyle(styles.get("cell_normal_centered"));
cell.setCellValue(data[i][j]);
}
}
// Write the output to a file
String file = "Repository.xls";
// if(!(wb instanceof HSSFWorkbook)) file += "x";
if((wb instanceof XSSFWorkbook)) file += "x";
FileOutputStream out;
try {
out = new FileOutputStream(file);
try {
wb.write(out);
out.close();
} catch (IOException ex) {
Logger.getLogger(CreateRepReport.class.getName()).log(Level.SEVERE, null, ex);
}
} catch (FileNotFoundException ex) {
Logger.getLogger(CreateRepReport.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb){
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
DataFormat df = wb.createDataFormat();
CellStyle style;
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
styles.put("header", style);
//Mamatha styles
style = createLockedBorderedStyle(wb);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
styles.put("cell_locked", style);
return styles;
}
private static CellStyle createBorderedStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setLocked(false);
return style;
}
private static CellStyle createLockedBorderedStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setLocked(true);
return style;
}
public static void main(String[] args) {
createRepSheet();
}
}
****************************************************************************
-----Original Message-----
From: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Sent: Thursday, February 03, 2011 10:42 PM
To: user@poi.apache.org
Subject: Re: Apache POI -Protected sheet disables the Row/Column resize
With regard to the HSSF model, you cannot, directly. It may be possible to
create a template that has the options set exactly as you want them and then
use POI to open and populate this template.
XSSF on the other hand does offer the ability to exercise this level of
control over protection. I do not have access to the code I need here but
know that if you search back through the messages on the list, protection
has been discussed already and there are examples there. I suspect if you
start about four or six months ago and work backwards from there, you will
find the relevant post.
Yours
Mark B
PS The template tricl will work for both file formats but is obviously only
useful if you are creating/populating new workbooks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3369596.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
Re: Apache POI -Protected sheet disables the Row/Column resize
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
With regard to the HSSF model, you cannot, directly. It may be possible to
create a template that has the options set exactly as you want them and then
use POI to open and populate this template.
XSSF on the other hand does offer the ability to exercise this level of
control over protection. I do not have access to the code I need here but
know that if you search back through the messages on the list, protection
has been discussed already and there are examples there. I suspect if you
start about four or six months ago and work backwards from there, you will
find the relevant post.
Yours
Mark B
PS The template tricl will work for both file formats but is obviously only
useful if you are creating/populating new workbooks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Protected-sheet-disables-the-Row-Column-resize-tp3369042p3369596.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