*I am facing problem in setting scale value for "printSetup.setScale((short)
scaleValue)". I am using following code but not working properly :-*
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.log4j.Logger;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
private static final Logger myLogger = Logger.getLogger(Test.class);
/*
* 72 POINTS = 1 INCH
* 1 POINT = 1/72 INCHES
* */
private static final int POINTS_TO_INCHES_FACTOR = 72;
public static final int DEFAULT_SCALE = 100;
/*
* Font metric for the "0" zero character is 556/1000 x 10 POINTS = 5.56
POINTS per CHARACTER.
* 5.56 POINTS = 1 CHARACTER.
* 256 WIDTH UNITS = 1 CHARACTER
* 256 WIDTH UNITS = 5.56 points
* 1 WIDTH UNITS = 1 / 256 CHARACTER
* = 5.56 / 256 POINTS
* = 0.0217 POINTS
* = 0.0217 / 72 INCHES
* = 0.0003 INCHES
*
*/
private static final float WIDTH_UNIT_TO_INCHES_MULTIPLYER = (float)
0.0003;
private static final float A4_WIDTH_IN_INCHES = (float) 8.27;
private static final float A4_HEIGHT_IN_INCHES = (float) 11.69;
private static final String FILE_PATH_TO_READ = "C://Book1.xlsx";
private static final String FILE_PATH_TO_SAVE = "C://Book2.xlsx";
private Workbook workbook;
public static void main(String[] args) {
Test test = new Test();
Sheet sheet = null;
int sheetCount = -1;
int lastRowIndex = -1;
int lastCellIndex = -1;
Map<Integer, Integer> pageSizeMap = new HashMap<Integer, Integer>();
pageSizeMap.put(0, 73);
pageSizeMap.put(1, 50);
Row row = null;
float pageWidth = -1;
float pageHeight = -1;
test.readWorkbook(FILE_PATH_TO_READ);
sheetCount = test.workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
sheet = (Sheet) test.workbook.getSheetAt(sheetIndex);
String sheetName = ((Sheet) sheet).getSheetName();
pageWidth = 0;
pageHeight = 0;
lastRowIndex = sheet.getLastRowNum();
for ( int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++ )
{
row = sheet.getRow(rowIndex);
if ( row != null )
{
pageHeight +=
test.convertPointsToInches(row.getHeightInPoints());
if(row.getLastCellNum() > lastCellIndex){
lastCellIndex = row.getLastCellNum();
}
if(rowIndex == lastRowIndex){
for ( int columnIndex = 0; columnIndex <=
lastCellIndex; columnIndex++ )
{
pageWidth +=
test.convertWidthUnitToInches(sheet.getColumnWidth(columnIndex));
}
}
}
}
myLogger.info("Sheet Height : " + pageHeight +" Page Width : " +
pageWidth);
/*
* pageHeight variable contains height of whole sheet.
* Setting the pageHeight to hold for a single page.
*/
pageHeight = (pageHeight *
pageSizeMap.get(sheetIndex))/lastRowIndex;
myLogger.info("Page Height : " + pageHeight +" Page Width : " +
pageWidth);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setScale((short) test.getScale(pageHeight, pageWidth,
A4_HEIGHT_IN_INCHES, A4_WIDTH_IN_INCHES));
sheet.setAutobreaks(false);
sheet.setColumnBreak(lastCellIndex);
sheet.setRowBreak(pageSizeMap.get(sheetIndex)-1);
test.workbook.setPrintArea(sheetIndex, 0, lastCellIndex - 1, 0,
lastRowIndex );
myLogger.info("Sheet Name : " + sheetName +" Row Break : " +
pageSizeMap.get(sheetIndex) + " Column Break : " + lastCellIndex);
}
test.saveWorkbook(FILE_PATH_TO_SAVE);
}
public void readWorkbook( String filePath )
{
InputStream inputStream = null;
try
{
inputStream = new FileInputStream( new File( filePath ) );
workbook = new XSSFWorkbook( inputStream );
}
catch ( FileNotFoundException e1 )
{
myLogger.error( "Exception occurred while reading workbook file
: ", e1 );
}
catch ( IOException e )
{
myLogger.error( "Exception occurred while reading workbook file
: ", e );
}
finally
{
try
{
inputStream.close();
}
catch ( IOException e )
{
myLogger.error( "Exception occurred while closing
inputStream : ", e );
}
}
}
public void saveWorkbook( String filePath )
{
try
{
File outputFile = new File( filePath );
OutputStream outputStream = new FileOutputStream( outputFile );
workbook.write( outputStream );
outputStream.close();
myLogger.info( "Output excel file is written successfully on
disk at location : " + filePath );
}
catch ( Exception e )
{
myLogger.error( "Exception occurred while writing excel file on
disk : ", e );
}
}
public float convertPointsToInches( float points )
{
float inches = 0;
inches = (float)points/POINTS_TO_INCHES_FACTOR;
//myLogger.info("points : " + points + " inches : "+inches);
return inches;
}
public float convertWidthUnitToInches( float widthUnit )
{
float inches = 0;
inches =( widthUnit * WIDTH_UNIT_TO_INCHES_MULTIPLYER);
myLogger.info("widthUnit : " + widthUnit + " inches : "+inches);
return inches;
}
public int getScale( float actualHeight, float actualWidth, float
reqHeight, float reqWidth )
{
int scale = DEFAULT_SCALE;
if(actualHeight < reqHeight && actualWidth < reqWidth){
//Scale not required in this case.
myLogger.info("Actual Height : " + actualHeight + " Actual
Width : "+
actualWidth + " Required Height : " + reqHeight + " Required Width : "+
reqWidth + " Scale : " + scale);
return scale;
}
else if(actualHeight < reqHeight){
scale = (int) Math.ceil( (( actualHeight * reqWidth) /
(actualHeight*actualWidth)) * DEFAULT_SCALE);
}
else if(actualWidth < reqWidth){
scale = (int) Math.ceil( (( reqHeight * actualWidth) /
(actualHeight*actualWidth)) * DEFAULT_SCALE);
}
else if((actualHeight > reqHeight) || (actualWidth > reqWidth))
{
scale = (int) Math.ceil( (( reqHeight * reqWidth) /
(actualHeight*actualWidth)) * DEFAULT_SCALE);
}
scale -= 12; //random correction need to improve
myLogger.info("Actual Height : " + actualHeight + " Actual Width : "+
actualWidth + " Required Height : " + reqHeight + " Required Width : "+
reqWidth + " Scale : " + scale);
return scale;
}
}
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/How-to-find-scale-value-to-fit-the-page-in-A4-Page-Size-for-larger-rows-an-columns-tp5714879.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
