Edit see comment; Apache POI seems to be racist, black not supported? Red is?
Edit 2 : instead of using XSSFColor use constants in Font, they have Red and Black; looking into default Fonts (Just to be clear code now correctly displays color and size now, but the Font name/ actual font still is wrong. So Font.COLOR_NORMAL works for dat black)
For some reason I can't get my XSSFTextBoxs' texts' fonts and font colors to change from their (I assume) default Calibri white (why is white a default?!?). I get their sizes changed, but their font and font color stay the defaults. I have found that this was a previous bug that should be fixed here.
I've been looking at this reference to base how I change the font, and it seems like this is how it's done, but it just doesn't seem to be working; I'd love another set of eyes on this, there are other minor problems I'm still fiddling with, but the font implementation is the biggest thing right now; any criticisms are welcome and wanted!
What really irks me is that I've used XSSFFont with XSSFCellStlyes before, quite extensively, and have never had any problems changing fonts or colors, let alone anything else, so I don't know if it's some strange behavior that I'm not seeing or if I've done something incorrectly here.
package excelhandling;
import java.awt.Desktop;
import java.io.*;
import javax.swing.JOptionPane;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
/**Unit Tests<br>To test random things to figure them out so I can implement them
* in the real code later; ideal is to test kinks here so whole application
* doesn't have to be loaded over and over to figure small issues out (living the dream)
*
* @author Sean Newell
*/
public class UnitTests
{
static String fileName = "TestWorkbook.xlsx";
public static void main(String[] args)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sht = wb.createSheet();
File file = new File(fileName);
int colStart = 5;
XSSFDrawing draw = sht.createDrawingPatriarch();
XSSFShapeGroup group = draw.createGroup(draw.createAnchor(0, 0, 0, 0, colStart, 11, colStart + 6, 11+7));
group.setCoordinates(colStart, 11, colStart + 6, 11+7);
XSSFTextBox tb1 = group.createTextbox(new XSSFChildAnchor(0, 0, 6, 7));
tb1.setShapeType(ShapeTypes.RECT);
tb1.setNoFill(false);
tb1.setFillColor(255, 255, 255); //This causes excel to repair xml - don't know why;
//following message from Excel:
//Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape)
//Log:
// <?xml version="1.0" encoding="UTF-8" standalone="true"?>
// -<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
// <logFileName>error094800_07.xml</logFileName>
// <summary>Errors were detected in file 'H:\My Documents\NetBeansProjects\TemplateBuilder\TestWorkbook.xlsx'</summary>
// -<repairedRecords summary="Following is a list of repairs:">
// <repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape)</repairedRecord>
// </repairedRecords>
// </recoveryLog>
XSSFRichTextString address = new XSSFRichTextString("TextBox string 1\nHas three\nLines to it");
XSSFFont arial10 = wb.createFont();
arial10.setFontName("Arial"); // Doesn't seem to work
arial10.setFontHeight(10);
arial10.setColor(new XSSFColor(java.awt.Color.BLACK)); // Doesn't seem to work
address.applyFont(arial10); // Possible problem?
tb1.setText(address);
tb1.setLineStyleColor(0, 0, 0);
tb1.setLineWidth(2);
XSSFTextBox tb2 = group.createTextbox(new XSSFChildAnchor(0, 7, 10, 13));
tb2.setShapeType(ShapeTypes.RECT);
tb2.setNoFill(false);
tb2.setFillColor(254, 254, 254); //This causes excel to repair xml - don't know why
XSSFRichTextString secret = new XSSFRichTextString("A single-line thing that has like, a lot of text, but can wrap and be smaller, like, totally.");
XSSFFont arial8 = wb.createFont();
arial8.setFontName("Arial"); // Doesn't seem to work
arial8.setFontHeight(8);
arial8.setColor(new XSSFColor(java.awt.Color.BLACK)); // Doesn't seem to work
secret.applyFont(arial8); // Possible problem?
tb2.setText(secret);
tb2.setLineStyleColor(0, 0, 0);
tb2.setLineWidth(2);
try {
FileOutputStream fout;
fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
JOptionPane.showConfirmDialog(null, "Excel sheet written");
Desktop.getDesktop().open(file);
} catch (IOException exc) {
JOptionPane.showInputDialog("Please close other instances of excel that have " + fileName + " open");
}
}
}
This is quite crude, but in poi 3.9 it seems that only the font family but not the font name is copied from the XSSFFont object in XSSFSimpleShape.applyAttributes.
Furthermore I could only set indexed colors via XSSFColor.setColor(HSSFColor.<color>.index) - maybe there is XSSF-pendant to the HSSF custom color palette, but I think the below approach is more straight-forward ...
(Tested with Libre Office 4.0, MS Excel Viewer, MS Excel 2003 with compatibility pack ...)
import java.awt.Color;
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextCharacterProperties;
public class XlsColors {
static String fileName = "TestWorkbook.xlsx";
public static void main(String[] args) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sht = wb.createSheet();
File file = new File(fileName);
int colStart = 5;
XSSFDrawing draw = sht.createDrawingPatriarch();
XSSFShapeGroup group = draw.createGroup(draw.createAnchor(0, 0, 0, 0, colStart, 11, colStart + 6, 11+7));
group.setCoordinates(colStart, 11, colStart + 6, 11+7);
XSSFTextBox tb1 = group.createTextbox(new XSSFChildAnchor(0, 0, 6, 7));
tb1.setLineStyleColor(0, 0, 0);
tb1.setLineWidth(2);
Color col = Color.orange;
tb1.setFillColor(col.getRed(), col.getGreen(), col.getBlue());
XSSFRichTextString address = new XSSFRichTextString("TextBox string 1\nHas three\nLines to it");
tb1.setText(address);
CTTextCharacterProperties rpr = tb1.getCTShape().getTxBody().getPArray(0).getRArray(0).getRPr();
rpr.addNewLatin().setTypeface("Trebuchet MS");
rpr.setSz(900); // 9 pt
col = Color.pink;
rpr.addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)col.getRed(),(byte)col.getGreen(),(byte)col.getBlue()});
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
}
}