com.sta.cts.hssf.HSSFHandlerXLS2XML Maven / Gradle / Ivy
package com.sta.cts.hssf;
import java.io.IOException;
import java.io.FileInputStream;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.Region;
import com.sta.mlogger.MLogger;
import com.sta.cts.XMLGenerator;
import com.sta.cts.UniTypeConv;
/**
* Name: HSSFHandlerXLS2XML
* Description: Spezieller Handler.
*
* Copyright: Copyright (c) 2003, 2016, 2017, 2019
* Company: >StA-Soft<
* @author StA
* @version 1.0
*/
public class HSSFHandlerXLS2XML
{
/**
* Input-Stream.
*/
private FileInputStream in;
/**
* HSSF-Workbook.
*/
private HSSFWorkbook wb;
/**
* XML-Generator.
*/
private XMLGenerator xg;
/**
* Bereiche.
*/
private Vector myMergedRegions = new Vector();
//===========================================================================
/**
* Merged-Regions vorbereiten.
* @param sheet Sheet
*/
private void prepareMergedRegions(HSSFSheet sheet)
{
myMergedRegions.clear();
try
{
int cnt = sheet.getNumMergedRegions();
for (int i = 0; i < cnt; i++)
{
Region mr = sheet.getMergedRegionAt(i);
if (mr != null)
{
myMergedRegions.add(mr);
}
}
}
catch (Exception e)
{
}
}
/**
* Merged-Region finden.
* @param line Zeile
* @param col Spalte
* @return Region
*/
private Region findMergedRegion(int line, short col)
{
int cnt = myMergedRegions.size();
for (int i = 0; i < cnt; i++)
{
Region mr = (Region) myMergedRegions.get(i);
if (mr.contains(line, col))
{
return mr;
}
}
return null;
}
//---------------------------------------------------------------------------
/**
* Hex-Darstellung (2stellig) f?r Integer.
* @param i Integer
* @return Hex-Darstellung
*/
private String hex(int i)
{
String s = Integer.toHexString(i);
if (s.length() < 2)
{
s = "0" + s;
}
return s;
}
/**
* Zur?ck-Konvertierung f?r Farbe.
* @param name Name
* @param color Farbe
* @throws IOException im Fehlerfall
*/
private void reconvColor(String name, short color) throws IOException
{
for (int i = 0; i < HSSF.COLORS.length; i++)
{
short[] ci = HSSF.COLORS[i];
if (color == ci[0])
{
xg.putAttr(name, "$" + hex(ci[HSSF.RED]) + hex(ci[HSSF.GREEN]) + hex(ci[HSSF.BLUE]));
break;
}
}
}
/**
* Zur?ck-Konvertierung f?r Short-Wert.
* @param name Name
* @param sa Werte-Feld
* @param v Index in Werte-Feld
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvShort(String name, String[] sa, short v, short std) throws IOException
{
if (v != std)
{
xg.putAttr(name, sa[v]);
}
}
/**
* Zur?ck-Konvertierung f?r Short-Wert.
* @param name Name
* @param sa Werte-Feld
* @param va Index-Feld
* @param v Index aus Index-Feld
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvShort(String name, String[] sa, short[] va, short v, short std) throws IOException
{
for (int i = 0; i < va.length; i++)
{
if (v == va[i])
{
if (v != std)
{
xg.putAttr(name, sa[i]);
}
break;
}
}
}
/**
* Zur?ck-Konvertierung f?r Boolean-Wert.
* @param name Name
* @param v Wert
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvBoolean(String name, boolean v, boolean std) throws IOException
{
if (v != std)
{
xg.putAttr(name, HSSF.BOOLEANS[v ? 1 : 0]);
}
}
/**
* Zur?ck-Konvertierung f?r Integer-Wert.
* @param name Name
* @param v Wert
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvInteger(String name, int v, int std) throws IOException
{
if (v != std)
{
xg.putAttr(name, "" + v);
}
}
/**
* Zur?ck-Konvertierung f?r Float-Wert.
* @param name Name
* @param v Wert
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvFloat(String name, float v, float std) throws IOException
{
if (v != std)
{
xg.putAttr(name, "" + v);
}
}
/**
* Zur?ck-Konvertierung f?r String-Wert.
* @param name Name
* @param v Wert
* @param std Standard-Wert
* @throws IOException im Fehlerfall
*/
private void reconvString(String name, String v, String std) throws IOException
{
if (!v.equals(std))
{
xg.putAttr(name, v);
}
}
//---------------------------------------------------------------------------
/**
* Zelle verarbeiten.
* @param cell Zelle
* @param cellspan Cell-Span
* @param rowspan Row-Span
* @throws IOException im Fehlerfall
*/
private void runCell(HSSFCell cell, int cellspan, int rowspan) throws IOException
{
int i;
xg.openTag(HSSF.TAG_CELL);
if (cell != null)
{
HSSFCellStyle cs = cell.getCellStyle();
if (cs != null)
{
HSSFFont font = wb.getFontAt(cs.getFontIndex());
if (font != null)
{
reconvString(HSSF.ATTR_FONT_NAME, font.getFontName(), HSSF.STD_FONT_NAME);
reconvInteger(HSSF.ATTR_FONT_SIZE, font.getFontHeightInPoints(), HSSF.STD_FONT_SIZE);
reconvColor(HSSF.ATTR_COLOR, font.getColor());
i = font.getBoldweight();
if (i > (HSSFFont.BOLDWEIGHT_BOLD + HSSFFont.BOLDWEIGHT_NORMAL) / 2)
{
xg.putAttr(HSSF.ATTR_FONT_WEIGHT, HSSF.FONT_WEIGHTS[1]);
}
if (font.getItalic())
{
xg.putAttr(HSSF.ATTR_FONT_STYLE, HSSF.FONT_STYLES[1]);
}
if (font.getStrikeout())
{
xg.putAttr(HSSF.ATTR_TEXT_DECO, HSSF.TEXT_DECOS[1]);
}
reconvShort(HSSF.ATTR_UNDERLINE, HSSF.UNDERLINES, HSSF.UNDERLINE_VALUES, font.getUnderline(), HSSF.STD_UNDERLINE);
reconvShort(HSSF.ATTR_TYPE_OFFSET, HSSF.TYPE_OFFSETS, font.getTypeOffset(), HSSF.STD_TYPE_OFFSET);
}
reconvInteger(HSSF.ATTR_ROTATION, cs.getRotation(), HSSF.STD_ROTATION);
reconvInteger(HSSF.ATTR_INDENT, cs.getIndention(), HSSF.STD_INDENT);
i = cs.getFillPattern();
if (i != HSSFCellStyle.NO_FILL)
{
if (i == HSSFCellStyle.SOLID_FOREGROUND)
{
reconvColor(HSSF.ATTR_BG_COLOR, cs.getFillForegroundColor());
}
else
{
reconvColor(HSSF.ATTR_FILL_BACKGROUND_COLOR, cs.getFillBackgroundColor());
reconvColor(HSSF.ATTR_FILL_FOREGROUND_COLOR, cs.getFillForegroundColor());
xg.putAttr(HSSF.ATTR_FILL_PATTERN, HSSF.FILL_PATTERNS[i]);
}
}
reconvShort(HSSF.ATTR_BORDER_BOTTOM, HSSF.BORDERS, cs.getBorderBottom(), HSSFCellStyle.BORDER_NONE);
reconvShort(HSSF.ATTR_BORDER_LEFT, HSSF.BORDERS, cs.getBorderLeft(), HSSFCellStyle.BORDER_NONE);
reconvShort(HSSF.ATTR_BORDER_RIGHT, HSSF.BORDERS, cs.getBorderRight(), HSSFCellStyle.BORDER_NONE);
reconvShort(HSSF.ATTR_BORDER_TOP, HSSF.BORDERS, cs.getBorderTop(), HSSFCellStyle.BORDER_NONE);
reconvColor(HSSF.ATTR_BORDER_BOTTOM_COLOR, cs.getBottomBorderColor());
reconvColor(HSSF.ATTR_BORDER_LEFT_COLOR, cs.getLeftBorderColor());
reconvColor(HSSF.ATTR_BORDER_RIGHT_COLOR, cs.getRightBorderColor());
reconvColor(HSSF.ATTR_BORDER_TOP_COLOR, cs.getTopBorderColor());
reconvShort(HSSF.ATTR_ALIGN, HSSF.ALIGNMENTS, cs.getAlignment(), HSSFCellStyle.ALIGN_GENERAL);
reconvShort(HSSF.ATTR_V_ALIGN, HSSF.VERTICALS, cs.getVerticalAlignment(), HSSFCellStyle.VERTICAL_TOP);
reconvBoolean(HSSF.ATTR_WRAP, cs.getWrapText(), HSSF.STD_WRAP);
reconvBoolean(HSSF.ATTR_HIDDEN, cs.getHidden(), HSSF.STD_HIDDEN);
reconvBoolean(HSSF.ATTR_LOCKED, cs.getLocked(), HSSF.STD_LOCKED);
}
if (cellspan > 1)
{
xg.putAttr(HSSF.ATTR_CELL_SPAN, "" + cellspan);
}
if (rowspan > 1)
{
xg.putAttr(HSSF.ATTR_ROW_SPAN, "" + rowspan);
}
int ct = cell.getCellType();
switch (ct)
{
case HSSFCell.CELL_TYPE_STRING:
{
xg.putContent(cell.getStringCellValue());
break;
}
case HSSFCell.CELL_TYPE_NUMERIC:
{
// HSSF.println("numeric!");
xg.putAttr(HSSF.ATTR_CELL_TYPE, HSSF.CELL_TYPES[HSSFCell.CELL_TYPE_NUMERIC]);
xg.putContent(UniTypeConv.convDouble2String(new Double(cell.getNumericCellValue())));
break;
}
default:
{
xg.putContent(cell.getStringCellValue());
}
}
}
xg.closeTag(HSSF.TAG_CELL);
}
/**
* Zeile verarbeiten.
* @param row Zeile
* @param line Zeilennummer
* @throws IOException im Fehlerfall
*/
private void runRow(HSSFRow row, int line) throws IOException
{
xg.openTag(HSSF.TAG_ROW);
if (row != null)
{
// "emptycells"...
reconvFloat(HSSF.ATTR_ROW_HEIGHT, row.getHeightInPoints(), HSSF.STD_ROW_HEIGHT);
// cells...
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
for (int col = 0; col < firstCell; col++)
{
xg.openTag(HSSF.TAG_CELL);
xg.closeTag(HSSF.TAG_CELL);
}
for (int col = firstCell; col <= lastCell; col++)
{
// Pr?fen, ob die Zelle in einer MergedRegion liegt...
Region mr = findMergedRegion(line, (short) col);
// Handelt es sich um die erste Zelle in einer Zeile der Region, wird
// cellspan entsprechend gesetzt und die folgenden Zellen ?bergangen
int cellspan = 1;
int rowspan = 1;
if (mr != null)
{
if (mr.getColumnFrom() == col)
{
cellspan = mr.getColumnTo() - mr.getColumnFrom() + 1;
}
if (mr.getRowFrom() == line)
{
rowspan = mr.getRowTo() - mr.getRowFrom() + 1;
}
}
runCell(row.getCell((short) col), cellspan, rowspan);
col += cellspan - 1;
}
}
xg.closeTag(HSSF.TAG_ROW);
}
/**
* Sheet verarbeiten.
* @param sheet Sheet
* @param name Name
* @throws IOException im Fehlerfall
*/
private void runSheet(HSSFSheet sheet, String name) throws IOException
{
prepareMergedRegions(sheet);
xg.openTag(HSSF.TAG_SHEET);
xg.putAttr(HSSF.ATTR_NAME, name);
reconvBoolean(HSSF.ATTR_ALTERNATIVE_EXPRESSION, sheet.getAlternateExpression(), HSSF.STD_ALTERNATIVE_EXPRESSION);
reconvBoolean(HSSF.ATTR_ALTERNATIVE_FORMULA, sheet.getAlternateFormula(), HSSF.STD_ALTERNATIVE_FORMULA);
reconvBoolean(HSSF.ATTR_AUTO_BREAKS, sheet.getAutobreaks(), HSSF.STD_AUTO_BREAKS);
reconvInteger(HSSF.ATTR_DEFAULT_COLUMN_WIDTH, sheet.getDefaultColumnWidth(), HSSF.STD_DEFAULT_COLUMN_WIDTH);
reconvFloat(HSSF.ATTR_DEFAULT_ROW_HEIGHT, sheet.getDefaultRowHeightInPoints(), HSSF.STD_DEFAULT_ROW_HEIGHT);
reconvBoolean(HSSF.ATTR_DIALOG, sheet.getDialog(), HSSF.STD_DIALOG);
reconvBoolean(HSSF.ATTR_DISPLAY_GUTS, sheet.getDisplayGuts(), HSSF.STD_DISPLAY_GUTS);
reconvBoolean(HSSF.ATTR_FIT_TO_PAGE, sheet.getFitToPage(), HSSF.STD_FIT_TO_PAGE);
try
{
xg.putAttr(HSSF.ATTR_GRIDS_PRINTED, HSSF.BOOLEANS[sheet.isGridsPrinted() ? 1 : 0]);
}
catch (Exception e)
{
}
reconvBoolean(HSSF.ATTR_ROW_SUMS_BELOW, sheet.getRowSumsBelow(), HSSF.STD_ROW_SUMS_BELOW);
reconvBoolean(HSSF.ATTR_ROW_SUMS_RIGHT, sheet.getRowSumsRight(), HSSF.STD_ROW_SUMS_RIGHT);
// "emptyrows" ...
// cols...
int curcol = 0;
// Leider scheint es keine M?glichkeit zu geben, die maximale Anzahl von
// Spalten auf einem Arbeitsblatt zu ermitteln ohne alle Zeilen abzufragen.
// Zur Ermittlung der Spaltenbreite wird (HSSF-intern) ein Feld benutzt,
// das leider "private" ist und ?ber keine Zugriffsmethode verf?gt.
// Daher werden einfach mal die ersten 256 Spalten durchgegangen und eine
// angegebene Spaltenbreite ?bernommen...
int dcw = sheet.getDefaultColumnWidth();
for (int col = 0; col < 256; col++)
{
int colwidth = sheet.getColumnWidth((short) col);
if (colwidth != dcw)
{
while (curcol < col)
{
xg.openTag(HSSF.TAG_COLUMN);
xg.closeTag(HSSF.TAG_COLUMN);
curcol++;
}
xg.openTag(HSSF.TAG_COLUMN);
xg.putAttr(HSSF.ATTR_COLUMN_WIDTH, "" + colwidth / 256);
xg.closeTag(HSSF.TAG_COLUMN);
curcol++;
}
}
// rows...
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for (int line = 0; line < firstRow; line++)
{
xg.openTag(HSSF.TAG_ROW);
xg.closeTag(HSSF.TAG_ROW);
}
for (int line = firstRow; line <= lastRow; line++)
{
runRow(sheet.getRow(line), line);
}
xg.closeTag(HSSF.TAG_SHEET);
}
/**
* Workbook verarbeiten.
* @param pDstFileName Zieldateiname
* @throws IOException im Fehlerfall
*/
public void run(String pDstFileName) throws IOException
{
wb = new HSSFWorkbook(in);
xg = new XMLGenerator();
xg.createXML(pDstFileName);
xg.openTag(HSSF.TAG_WORKBOOK);
int sheets = wb.getNumberOfSheets();
for (int i = 0; i < sheets; i++)
{
runSheet(wb.getSheetAt(i), wb.getSheetName(i));
}
xg.closeTag(HSSF.TAG_WORKBOOK);
xg.closeXML();
}
//===========================================================================
/**
* Zentraler Einstiegspunkt.
* @param pSrcFileName Quelldateiname
* @param pDstFileName Zieldateiname
*/
public static void main(String pSrcFileName, String pDstFileName)
{
try
{
HSSFHandlerXLS2XML aHSSFHandlerInstance = new HSSFHandlerXLS2XML();
aHSSFHandlerInstance.in = new FileInputStream(pSrcFileName);
aHSSFHandlerInstance.run(pDstFileName);
aHSSFHandlerInstance.in.close();
}
catch (Exception ex)
{
MLogger.err("", ex);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy