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:markbrd...@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-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
