private void Test(string fileName)
{
HSSFWorkbook workbook;
workbook = ExcelToHtmlUtils.LoadXls(fileName);
ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();
excelToHtmlConverter.ProcessWorkbook(workbook);
using (var sw = new FileStream(Path.ChangeExtension(fileName, "html"), FileMode.Create))
{
excelToHtmlConverter.Document.Save(sw);
}
}
private void BuildStyle_Border(IWorkbook workbook, StringBuilder style,
String type, BorderStyle xlsBorder, short borderColor)
{
if (xlsBorder == BorderStyle.None)
{
return;
}
StringBuilder borderStyle = new StringBuilder();
borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));
if (workbook is HSSFWorkbook)
{
HSSFColor color = ((HSSFWorkbook)workbook).GetCustomPalette().GetColor(borderColor);
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(borderColor);
if (clr != null)
{
borderStyle.Append(' ');
borderStyle.Append(clr.HexString);
}
else
{
XSSFColor color = ((XSSFWorkbook)workbook).GetStylesSource().GetTheme().GetThemeColor(borderColor);
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
}
style.AppendFormat("border-{0}: {1}; ", type, borderStyle);
}
private void BuildStyle_Font(IWorkbook workbook, StringBuilder style,
IFont font)
{
switch (font.Boldweight)
{
case (short)FontBoldWeight.Bold:
style.Append("font-weight: bold; ");
break;
case (short)FontBoldWeight.Normal:
// by default, not not increase HTML size
// style.Append( "font-weight: normal; " );
break;
}
if (workbook is HSSFWorkbook)
{
HSSFColor fontColor = ((HSSFWorkbook)workbook).GetCustomPalette().GetColor(font.Color);
if (fontColor != null)
{
style.AppendFormat("color:{0}; ", ExcelToHtmlUtils.GetColor(fontColor));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(font.Color);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
XSSFColor fontColor = null;
if (st != null && st.GetTheme() != null)
{
fontColor = st.GetTheme().GetThemeColor(font.Color);
}
else
{
fontColor = ((XSSFFont)font).GetXSSFColor();
}
if (fontColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(fontColor);
}
}
if (hexstring != null)
{
style.AppendFormat("color:{0}; ", hexstring);
}
}
if (font.FontHeightInPoints != 0)
{
style.Append("font-size: " + font.FontHeightInPoints + "pt; ");
}
if (font.IsItalic)
{
style.Append("font-style: italic; ");
}
}
protected String BuildStyle(IWorkbook workbook, ICellStyle cellStyle)
{
StringBuilder style = new StringBuilder();
if (workbook is HSSFWorkbook)
{
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle.
//HSSFColor.
HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
if (foregroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(foregroundColor));
}
}
else
{
HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
if (backgroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(backgroundColor));
}
}
}
else
{
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
ThemesTable tt = st.GetTheme();
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillForegroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor foregroundColor = (XSSFColor)cellStyle.FillForegroundColorColor;
if (foregroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(foregroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillBackgroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor backgroundColor = (XSSFColor)cellStyle.FillBackgroundColorColor;
if (backgroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(backgroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
}
BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);
IFont font = cellStyle.GetFont(workbook);
BuildStyle_Font(workbook, style, font);
return(style.ToString());
}
protected bool ProcessCell(ICell cell, XElement tableCellElement,
int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt)
{
ICellStyle cellStyle = cell.CellStyle as ICellStyle;
string value;
switch (cell.CellType)
{
case CellType.String:
// XXX: enrich
value = cell.RichStringCellValue.String;
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.String:
IRichTextString str = cell.RichStringCellValue;
if (str != null && str.Length > 0)
{
value = (str.String);
}
else
{
value = string.Empty;
}
break;
case CellType.Numeric:
ICellStyle style = cellStyle;
if (style == null)
{
value = cell.NumericCellValue.ToString();
}
else
{
value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
}
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
logger.Log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.CachedFormulaResultType.ToString() + ")");
value = string.Empty;
break;
}
break;
case CellType.Blank:
value = string.Empty;
break;
case CellType.Numeric:
value = _formatter.FormatCellValue(cell);
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
logger.Log(POILogger.WARN, "Unexpected cell type (" + cell.CellType.ToString() + ")");
return(true);
}
bool noText = string.IsNullOrEmpty(value);
bool wrapInDivs = !noText && UseDivsToSpan && !cellStyle.WrapText;
short cellStyleIndex = cellStyle.Index;
if (cellStyleIndex != 0)
{
IWorkbook workbook = cell.Row.Sheet.Workbook as IWorkbook;
string mainCssClass = GetStyleClassName(workbook, cellStyle);
if (wrapInDivs)
{
tableCellElement.SetAttributeValue("class", mainCssClass + " "
+ cssClassContainerCell);
}
else
{
tableCellElement.SetAttributeValue("class", mainCssClass);
}
if (noText)
{
/*
* if cell style is defined (like borders, etc.) but cell text
* is empty, add " " to output, so browser won't collapse
* and ignore cell
*/
value = "\u00A0"; //“ ”全角空格
}
}
if (OutputLeadingSpacesAsNonBreaking && value.StartsWith(" "))
{
StringBuilder builder = new StringBuilder();
for (int c = 0; c < value.Length; c++)
{
if (value[c] != ' ')
{
break;
}
builder.Append('\u00a0');
}
if (value.Length != builder.Length)
{
builder.Append(value.Substring(builder.Length));
}
value = builder.ToString();
}
XText text = htmlDocumentFacade.CreateText(value);
if (wrapInDivs)
{
XElement outerDiv = htmlDocumentFacade.CreateBlock();
outerDiv.SetAttributeValue("class", this.cssClassContainerDiv);
XElement innerDiv = htmlDocumentFacade.CreateBlock();
StringBuilder innerDivStyle = new StringBuilder();
innerDivStyle.Append("position:absolute;min-width:");
innerDivStyle.Append(normalWidthPx);
innerDivStyle.Append("px;");
if (maxSpannedWidthPx != int.MaxValue)
{
innerDivStyle.Append("max-width:");
innerDivStyle.Append(maxSpannedWidthPx);
innerDivStyle.Append("px;");
}
innerDivStyle.Append("overflow:hidden;max-height:");
innerDivStyle.Append(normalHeightPt);
innerDivStyle.Append("pt;white-space:nowrap;");
ExcelToHtmlUtils.AppendAlign(innerDivStyle, cellStyle.Alignment);
htmlDocumentFacade.AddStyleClass(outerDiv, "d", innerDivStyle.ToString());
innerDiv.AppendChild(text);
outerDiv.AppendChild(innerDiv);
tableCellElement.AppendChild(outerDiv);
}
else
{
tableCellElement.AppendChild(text);
}
return(string.IsNullOrEmpty(value) && cellStyleIndex == 0);
}
protected static int GetColumnWidth(ISheet sheet, int columnIndex)
{
return(ExcelToHtmlUtils.GetColumnWidthInPx(sheet.GetColumnWidth(columnIndex)));
}
/**
* @return maximum 1-base index of column that were rendered, zero if none
*/
protected int ProcessRow(CellRangeAddress[][] mergedRanges, IRow row,
XElement tableRowElement)
{
ISheet sheet = (ISheet)row.Sheet;
int maxColIx = row.LastCellNum;
if (maxColIx <= 0)
{
return(0);
}
List <XElement> emptyCells = new List <XElement>(maxColIx);
if (OutputRowNumbers)
{
XElement tableRowNumberCellElement = htmlDocumentFacade.CreateTableHeaderCell();
ProcessRowNumber(row, tableRowNumberCellElement);
emptyCells.Add(tableRowNumberCellElement);
}
int maxRenderedColumn = 0;
for (int colIx = 0; colIx < maxColIx; colIx++)
{
if (!OutputHiddenColumns && sheet.IsColumnHidden(colIx))
{
continue;
}
CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange(
mergedRanges, row.RowNum, colIx);
if (range != null && (range.FirstColumn != colIx || range.FirstRow != row.RowNum))
{
continue;
}
ICell cell = (ICell)row.GetCell(colIx);
int divWidthPx = 0;
if (UseDivsToSpan)
{
divWidthPx = GetColumnWidth(sheet, colIx);
bool hasBreaks = false;
for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++)
{
if (!OutputHiddenColumns && sheet.IsColumnHidden(nextColumnIndex))
{
continue;
}
if (row.GetCell(nextColumnIndex) != null && !IsTextEmpty((ICell)row.GetCell(nextColumnIndex)))
{
hasBreaks = true;
break;
}
divWidthPx += GetColumnWidth(sheet, nextColumnIndex);
}
if (!hasBreaks)
{
divWidthPx = int.MaxValue;
}
}
XElement tableCellElement = htmlDocumentFacade.CreateTableCell();
if (range != null)
{
if (range.FirstColumn != range.LastColumn)
{
tableCellElement.SetAttributeValue("colspan", (range.LastColumn - range.FirstColumn + 1).ToString());
}
if (range.FirstRow != range.LastRow)
{
tableCellElement.SetAttributeValue("rowspan", (range.LastRow - range.FirstRow + 1).ToString());
}
}
bool emptyCell;
if (cell != null)
{
emptyCell = ProcessCell(cell, tableCellElement, GetColumnWidth(sheet, colIx), divWidthPx, row.Height / 20f);
}
else
{
emptyCell = true;
}
if (emptyCell)
{
emptyCells.Add(tableCellElement);
}
else
{
foreach (XElement emptyCellElement in emptyCells)
{
tableRowElement.AppendChild(emptyCellElement);
}
emptyCells.Clear();
tableRowElement.AppendChild(tableCellElement);
maxRenderedColumn = colIx;
}
}
return(maxRenderedColumn + 1);
}
protected void ProcessSheet(ISheet sheet)
{
ProcessSheetHeader(htmlDocumentFacade.Body, sheet);
int physicalNumberOfRows = sheet.PhysicalNumberOfRows;
if (physicalNumberOfRows <= 0)
{
return;
}
XElement table = htmlDocumentFacade.CreateTable();
table.SetAttributeValue("class", cssClassTable);
XElement tableBody = htmlDocumentFacade.CreateTableBody();
CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.BuildMergedRangesMap(sheet);
List <XElement> emptyRowElements = new List <XElement>(physicalNumberOfRows);
int maxSheetColumns = 1;
for (int r = 0; r < physicalNumberOfRows; r++)
{
IRow row = sheet.GetRow(r);
if (row == null)
{
continue;
}
if (!OutputHiddenRows && row.ZeroHeight)
{
continue;
}
XElement tableRowElement = htmlDocumentFacade.CreateTableRow();
htmlDocumentFacade.AddStyleClass(tableRowElement, "r", "height:"
+ (row.Height / 20f) + "pt;");
int maxRowColumnNumber = ProcessRow(mergedRanges, row,
tableRowElement);
if (maxRowColumnNumber == 0)
{
emptyRowElements.Add(tableRowElement);
}
else
{
if (emptyRowElements.Count > 0)
{
foreach (XElement emptyRowElement in emptyRowElements)
{
tableBody.AppendChild(emptyRowElement);
}
emptyRowElements.Clear();
}
tableBody.AppendChild(tableRowElement);
}
maxSheetColumns = Math.Max(maxSheetColumns, maxRowColumnNumber);
}
ProcessColumnWidths(sheet, maxSheetColumns, table);
if (OutputColumnHeaders)
{
ProcessColumnHeaders(sheet, maxSheetColumns, table);
}
table.AppendChild(tableBody);
htmlDocumentFacade.Body.AppendChild(table);
}