Just to finalise this discussion, below is the ss.usermodel example I
promised. It will work whether or not you are opening a template or creating
a new empty workbook. The key seemed to be explcitly setting the font name
so that both workbooks use that exact same font. Failing to do this left the
XSSFWorkbook using Calibri 11 and the HSSWorkbook Arial 10.
import java.io.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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 org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
/**
*
* @author Mark Beardsley
*/
public class SSUsermodelExample {
SSUsermodelExample(String templateFilename, String outputFilename) {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
CellStyle cellStyle = null;
Font font = null;
CreationHelper creatHelper = null;
try {
if(templateFilename.length() != 0) {
file = new File(templateFilename);
fis = new FileInputStream(file);
workbook = WorkbookFactory.create(fis);
sheet = workbook.getSheetAt(0);
}
else {
if(outputFilename.endsWith(".xls")) {
workbook = new HSSFWorkbook();
}
else {
workbook = new XSSFWorkbook();
}
sheet = workbook.createSheet();
}
creatHelper = workbook.getCreationHelper();
cellStyle = workbook.createCellStyle();
font = workbook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short)11);
font.setColor(IndexedColors.AQUA.getIndex());
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
row = sheet.createRow(2);
cell = row.createCell(2);
String data = "aaa bbb ccc dd ee fff g hhh iii jjj kkk lll mmm
nnn ooo ppp qqq rrr sss ttt uuu vvv www xxx yyy z";
cell.setCellValue(creatHelper.createRichTextString(data));
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue("111\n222\n333\n444\n444\n444\n444\n444\n444\n444\n444\n444\n444\n444\nzzz");
cell.setCellStyle(cellStyle);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
System.out.println(sheet.getDefaultRowHeight());
System.out.println(sheet.getDefaultRowHeightInPoints());
file = new File(outputFilename);
fos = new FileOutputStream(file);
workbook.write(fos);
}
catch(Exception ex) {
SSUsermodelExample.showException(ex);
}
finally {
try {
if(fis != null) {
fis.close();
fis = null;
}
}
catch(IOException ioEx) {
SSUsermodelExample.showException(ioEx);
}
try {
if(fos != null) {
fos.close();
fos = null;
}
}
catch(IOException ioEx) {
SSUsermodelExample.showException(ioEx);
}
}
}
private static final void showException(Throwable thrown) {
System.out.println("Caught an: " + thrown.getClass().getName());
System.out.println("Message: " + thrown.getMessage());
System.out.println("Stacktrace follows:.....");
thrown.printStackTrace(System.out);
}
public static void main(String[] args) {
if(args.length != 2) {
System.out.println("Usage: java SSUsermodelExample(new
String[]{\"template file name\", \"output file name\"})");
}
else {
new SSUsermodelExample(args[0], args[1]);
}
}
}
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Can-not-see-the-entire-cell-content-in-HSSF-tp5709814p5709883.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
