CODEWITHSUNDEEP
apache-poiwidth
user8847697
user8847697

Reputation:

How to set cell width in two decimal points in Excel Apache POI

Im trying to get the fixed cell width values with accuracy into two decimal points. But when the excel file is generated, the cell width is rounded into the whole number of the width size i want to achieve. Can this be done? 

  private Sheet setupSheet(XSSFWorkbook workbook) {

    Sheet xssfSheet;
    xssfSheet = workbook.createSheet("report");
    xssfSheet.getPrintSetup().setLandscape(true);
    xssfSheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    xssfSheet.setMargin(Sheet.HeaderMargin, 0.1);
    xssfSheet.setMargin(Sheet.BottomMargin, 0.1);
    xssfSheet.setMargin(Sheet.LeftMargin, 0.1);
    xssfSheet.setMargin(Sheet.RightMargin, 0.1);
    xssfSheet.setFitToPage(true);
    PrintSetup ps = xssfSheet.getPrintSetup();
    ps.setFitWidth((short) 1);
    ps.setFitHeight((short) 0);
    return xssfSheet;
}

Sheet sheet = setupSheet(workbook);

int widthExcel = 10;
int width256 = (int) Math.round((widthExcel * Units.EMU_PER_POINT + 5f) / Units.EMU_PER_POINT * 256f);
            sheet.setColumnWidth(0, (int) (width256 * 1.14));
            sheet.setColumnWidth(1, (int) (width256 * 0.49));
            sheet.setColumnWidth(2, (int) (width256 * 0.85));
            sheet.setColumnWidth(3, (int) (width256 * 1.45));

            sheet.setColumnWidth(4, (int) (width256 * 0.46));
            sheet.setColumnWidth(5, (int) (width256 * 2.85));
            sheet.setColumnWidth(6, (int) (width256 * 2.02));
            sheet.setColumnWidth(7, (int) (width256 * 0.66));

            sheet.setColumnWidth(8, (int) (width256 * 0.72));
            sheet.setColumnWidth(9, (int) (width256 * 0.86));
            sheet.setColumnWidth(10, (int) (width256 * 0.75));
            sheet.setColumnWidth(11, (int) (width256 * 0.90));
 }

Upvotes: 0

Views: 5704

Answers (2)

Akan Tileubergenov
Akan Tileubergenov

Reputation: 1

For my case

Example:

private static int w(float widthExcel) {
  return (int) Math.floor((widthExcel * Units.DEFAULT_CHARACTER_WIDTH + 5.5) / Units.DEFAULT_CHARACTER_WIDTH * 256); 
}


sheet.setColumnWidth(0, w(10.71f))

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61945

The question is not really clear. But if you want set the column width as Excel will show it in it's GUI, then this would must be done usig the formula given in Sheet.setColumnWidth.

Excel stores the column widths as integer values in unit 1/256th of a character width but shows them as how many characters fit into the cell as floating point number.

Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelColumnWidth {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {

   Sheet excelSheet = workbook.createSheet();

   float widthExcel = 10.71f;
   int width256 = (int)Math.floor((widthExcel * Units.DEFAULT_CHARACTER_WIDTH + 5) / Units.DEFAULT_CHARACTER_WIDTH * 256);
   excelSheet.setColumnWidth(0, width256);

   workbook.write(fileout);
  }
 }
}

Result:

enter image description here

Upvotes: 3

Related Questions