(Für mein eigenes Memo ... aber ich hoffe es hilft jemandem ...)
Excel-Ausgabe, die gelegentlich in Geschäftsanwendungen auftritt. Hmm. Ich mag CSV.
damit, Da Maven verwendet wird, ist es in pom.xml definiert.
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
Definieren und Maven installieren.
Maven-Abhängigkeit
poi-ooxml-3.17.jar
poi-3.17.jar
commons-codec-1.10.jar
commons-collections4-4.1.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
curvesapi-1.04.jar
Wird mit inbegriffen sein. (Wenn es sich um eine geschlossene Umgebung handelt, müssen Sie sie von Grund auf neu sammeln ...)
Die Quelle ist ↓
Excel-Ausgabe
private static void outputExcel() throws Exception {
String outputFilePath = "[Ausgabeziel]\\tmp.xlsx";
Workbook book = null;
FileOutputStream fout = null;
try {
book = new SXSSFWorkbook();
Font font = book.createFont();
font.setFontName("MS Gothic");
font.setFontHeightInPoints((short) 9);
DataFormat format = book.createDataFormat();
//Stil für Header-String
CellStyle style_header = book.createCellStyle();
style_header.setBorderBottom(BorderStyle.THIN);
App.setBorder(style_header, BorderStyle.THIN);
style_header.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
style_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style_header.setVerticalAlignment(VerticalAlignment.TOP);
style_header.setFont(font);
//Stil für Saiten
CellStyle style_string = book.createCellStyle();
App.setBorder(style_string, BorderStyle.THIN);
style_string.setVerticalAlignment(VerticalAlignment.TOP);
style_string.setFont(font);
//Stil für Zeichenfolgen mit Zeilenumbrüchen
CellStyle style_string_wrap = book.createCellStyle();
App.setBorder(style_string_wrap, BorderStyle.THIN);
style_string_wrap.setVerticalAlignment(VerticalAlignment.TOP);
style_string_wrap.setWrapText(true);
style_string_wrap.setFont(font);
//Stil für ganze Zahlen
CellStyle style_int = book.createCellStyle();
App.setBorder(style_int, BorderStyle.THIN);
style_int.setDataFormat(format.getFormat("#,##0;-#,##0"));
style_int.setVerticalAlignment(VerticalAlignment.TOP);
style_int.setFont(font);
//Stil für Brüche
CellStyle style_double = book.createCellStyle();
App.setBorder(style_double, BorderStyle.THIN);
style_double.setDataFormat(format.getFormat("#,##0.0;-#,##0.0"));
style_double.setVerticalAlignment(VerticalAlignment.TOP);
style_double.setFont(font);
//Stil für die Kreisanzeige
CellStyle style_yen = book.createCellStyle();
App.setBorder(style_yen, BorderStyle.THIN);
style_yen.setDataFormat(format.getFormat("\"\\\"#,##0;\"\\\"-#,##0"));
style_yen.setVerticalAlignment(VerticalAlignment.TOP);
style_yen.setFont(font);
//Stil für die prozentuale Anzeige
CellStyle style_percent = book.createCellStyle();
App.setBorder(style_percent, BorderStyle.THIN);
style_percent.setDataFormat(format.getFormat("0.0%"));
style_percent.setVerticalAlignment(VerticalAlignment.TOP);
style_percent.setFont(font);
//Stil für Datums- und Uhrzeitanzeige
CellStyle style_datetime = book.createCellStyle();
App.setBorder(style_datetime, BorderStyle.THIN);
style_datetime.setDataFormat(format.getFormat("yyyy/mm/dd hh:mm:ss"));
style_datetime.setVerticalAlignment(VerticalAlignment.TOP);
style_datetime.setFont(font);
Row row;
int rowNumber;
Cell cell;
int colNumber;
//Ein Blatt erstellen(Versuchen Sie 3 Blätter zu machen)
Sheet sheet;
for (int i = 0; i < 3; i++) {
sheet = book.createSheet();
if (sheet instanceof SXSSFSheet) {
((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
}
//Einstellung des Blattnamens
book.setSheetName(i, "Blatt" + (i + 1));
//Erstellen einer Kopfzeile
rowNumber = 0;
colNumber = 0;
row = sheet.createRow(rowNumber);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("No.");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("String");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Zeichenkette mit Zeilenumbrüchen");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("ganze Zahl");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Kreis");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Prozent");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Datum (und Uhrzeit");
cell = row.createCell(colNumber);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Kreis(8%Steuern inklusive)");
//Fester Fensterrahmen
sheet.createFreezePane(1, 1);
//Autofilter-Einstellungen in der Kopfzeile
sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, colNumber));
//Automatische Anpassung der Spaltenbreite
for (int j = 0; j <= colNumber; j++) {
sheet.autoSizeColumn(j, true);
}
//Datenzeilengenerierung(Versuche 10 Zeilen zu machen)
for (int j = 0; j < 10; j++) {
rowNumber++;
colNumber = 0;
row = sheet.createRow(rowNumber);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_int);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(j + 1);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_string);
cell.setCellType(CellType.STRING);
cell.setCellValue("das ist" + (j + 1) + "Die Daten in der Zeile.");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_string_wrap);
cell.setCellType(CellType.STRING);
cell.setCellValue("das ist\n" + (j + 1) + "Linie\");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_int);
cell.setCellType(CellType.STRING);
cell.setCellValue((j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_double);
cell.setCellType(CellType.STRING);
cell.setCellValue((double) (j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_yen);
cell.setCellType(CellType.STRING);
cell.setCellValue((j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_percent);
cell.setCellType(CellType.STRING);
cell.setCellValue((double) (j + 1));
cell = row.createCell(colNumber++);
cell.setCellStyle(style_datetime);
cell.setCellType(CellType.STRING);
cell.setCellValue(new Date());
cell = row.createCell(colNumber);
cell.setCellStyle(style_yen);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("ROUND(" + App.getExcelColumnString(colNumber - 3) + (rowNumber + 1) + "*1.08, 0)");
//Automatische Anpassung der Spaltenbreite
for (int k = 0; k <= colNumber; k++) {
sheet.autoSizeColumn(k, true);
}
}
}
//Versuchen Sie, Blatt 3 zu löschen
book.removeSheetAt(2);
//Dateiausgabe
fout = new FileOutputStream(outputFilePath);
book.write(fout);
}
finally {
if (fout != null) {
try {
fout.close();
}
catch (IOException e) {
}
}
if (book != null) {
try {
/*
Weil die SXSSF-Arbeitsmappe eine große Anzahl temporärer Dateien auf Kosten der Speicherplatzersparnis erstellt
Sie müssen temporäre Dateien entsorgen und löschen, wenn Sie sie nicht mehr benötigen
*/
((SXSSFWorkbook) book).dispose();
}
catch (Exception e) {
}
}
}
}
private static void setBorder(CellStyle style, BorderStyle border) {
style.setBorderBottom(border);
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
}
private final static String[] LIST_ALPHA = {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
};
private static String getExcelColumnString(int column) {
String result = "";
if (column >= 0) {
if (column / App.LIST_ALPHA.length > 0) {
result += getExcelColumnString(column / App.LIST_ALPHA.length - 1);
}
result += App.LIST_ALPHA[column % App.LIST_ALPHA.length];
}
return result;
}
Es sieht aus wie VBA. Trotzdem muss ich mir die Seiten ansehen, die hilfreich sind.
Recommended Posts