private void ApplyCellBorderFromStyle(IWorksheetCellFormat cellFormat, CssStyle cssStyle)
{
if (this.IsValidBorder(cssStyle.Border.Left) && cellFormat.LeftBorderColor.IsEmpty)
cellFormat.LeftBorderColor = cssStyle.Border.Left.Color;
if (this.IsValidBorder(cssStyle.Border.Right) && cellFormat.RightBorderColor.IsEmpty)
cellFormat.RightBorderColor = cssStyle.Border.Right.Color;
if (this.IsValidBorder(cssStyle.Border.Top) && cellFormat.TopBorderColor.IsEmpty)
cellFormat.TopBorderColor = cssStyle.Border.Top.Color;
if (this.IsValidBorder(cssStyle.Border.Bottom) && cellFormat.BottomBorderColor.IsEmpty)
cellFormat.BottomBorderColor = cssStyle.Border.Bottom.Color;
cellFormat.LeftBorderStyle = this.ConvertWebToExcelBorderStyle(cellFormat.LeftBorderStyle, cssStyle.Border.Left.Style, cssStyle.Border.Left.Width);
cellFormat.TopBorderStyle = this.ConvertWebToExcelBorderStyle(cellFormat.TopBorderStyle, cssStyle.Border.Top.Style, cssStyle.Border.Top.Width);
cellFormat.RightBorderStyle = this.ConvertWebToExcelBorderStyle(cellFormat.RightBorderStyle, cssStyle.Border.Right.Style, cssStyle.Border.Right.Width);
cellFormat.BottomBorderStyle = this.ConvertWebToExcelBorderStyle(cellFormat.BottomBorderStyle, cssStyle.Border.Bottom.Style, cssStyle.Border.Bottom.Width);
}
/// <summary>
/// 将数据导入到Excel
/// </summary>
public static void ExportExcel(string excelPath, IList <Hashtable> list, string sheetName, string[] strValue)
{
Workbook wb = new Workbook();
wb.Worksheets.Add(sheetName);
WindowOptions options = wb.WindowOptions;
Worksheet ws = options.SelectedWorksheet;
//设置标题的格式
IWorksheetCellFormat cellFormatHead = wb.CreateNewWorksheetCellFormat();
cellFormatHead.Alignment = HorizontalCellAlignment.Center;
cellFormatHead.BottomBorderStyle = CellBorderLineStyle.Thin;
cellFormatHead.LeftBorderStyle = CellBorderLineStyle.Thin;
cellFormatHead.RightBorderStyle = CellBorderLineStyle.Thin;
cellFormatHead.TopBorderStyle = CellBorderLineStyle.Thin;
cellFormatHead.Font.Bold = ExcelDefaultableBoolean.True;
cellFormatHead.Font.Name = "Calibri";
cellFormatHead.Font.Height = 220;
ws.Rows[0].CellFormat.SetFormatting(cellFormatHead);
//设置数据行的格式
IWorksheetCellFormat cellFormatContent = wb.CreateNewWorksheetCellFormat();
cellFormatContent.Alignment = HorizontalCellAlignment.Left;
cellFormatContent.BottomBorderStyle = CellBorderLineStyle.Thin;
cellFormatContent.LeftBorderStyle = CellBorderLineStyle.Thin;
cellFormatContent.RightBorderStyle = CellBorderLineStyle.Thin;
cellFormatContent.TopBorderStyle = CellBorderLineStyle.Thin;
cellFormatContent.Font.Name = "Calibri";
cellFormatContent.Font.Height = 220;
//添加标题行
//Hashtable headHashTable = list[0];
int i = 0;
foreach (string headValue in strValue)
{
ws.Rows[0].Cells[i].Value = headValue;
ws.Columns[i].Width = 5000;
i++;
}
//添加数据行
for (int k = 0; k < list.Count; k++)
{
Hashtable dataHashTable = list[k];
int j = 0;
foreach (string dataValue in strValue)
{
ws.Rows[k + 1].Cells[j].Value = dataHashTable[dataValue];
ws.Rows[k + 1].Cells[j].CellFormat.SetFormatting(cellFormatContent);
j++;
}
}
wb.Save(excelPath);
}
private IWorksheetCellFormat ApplyCellFormatFromStyle(Workbook workbook, IWorksheetCellFormat cellFormat, CssStyle cssStyle, WebDataGrid grid)
{
if (Color.Empty != cssStyle.Background.Color && cssStyle.Background.Color.ToArgb() != Color.White.ToArgb())
{
cellFormat.FillPatternForegroundColor = cssStyle.Background.Color;
cellFormat.FillPattern = FillPatternStyle.Solid;
}
IWorkbookFont newWorkbookFont = workbook.CreateNewWorkbookFont();
newWorkbookFont.Color = grid.ForeColor.IsEmpty ? cssStyle.Color : grid.ForeColor;
newWorkbookFont.Name = string.IsNullOrEmpty(grid.Font.Name) ? cssStyle.Font.FamilyName : grid.Font.Name;
switch (cssStyle.Font.SizeEnum)
{
case Infragistics.Documents.Reports.FontSize.NotSet:
newWorkbookFont.Height = cssStyle.Font.SizeUnit.Type != UnitType.Point || cssStyle.Font.SizeUnit.Value < 1.0 ? (cssStyle.Font.SizeUnit.Type != UnitType.Pixel || cssStyle.Font.SizeUnit.Value < 1.0 ? 240 : (int)(cssStyle.Font.SizeUnit.Value * 20.0)) : (int)(cssStyle.Font.SizeUnit.Value * 20.0);
break;
case Infragistics.Documents.Reports.FontSize.Large:
case Infragistics.Documents.Reports.FontSize.Larger:
newWorkbookFont.Height = 280;
break;
case Infragistics.Documents.Reports.FontSize.Small:
case Infragistics.Documents.Reports.FontSize.Smaller:
newWorkbookFont.Height = 200;
break;
case Infragistics.Documents.Reports.FontSize.X_Large:
newWorkbookFont.Height = 360;
break;
case Infragistics.Documents.Reports.FontSize.X_Small:
newWorkbookFont.Height = 180;
break;
case Infragistics.Documents.Reports.FontSize.XX_Large:
newWorkbookFont.Height = 480;
break;
case Infragistics.Documents.Reports.FontSize.XX_Small:
newWorkbookFont.Height = 160;
break;
default:
newWorkbookFont.Height = 240;
break;
}
if (cssStyle.Font.IsBold || cssStyle.Font.Style == Infragistics.Documents.Reports.FontStyle.Oblique || grid.Font.Bold)
newWorkbookFont.Bold = ExcelDefaultableBoolean.True;
if (cssStyle.Font.IsItalic || cssStyle.Font.Style == Infragistics.Documents.Reports.FontStyle.Italic || grid.Font.Italic)
newWorkbookFont.Italic = ExcelDefaultableBoolean.True;
if (cssStyle.TextDecoration == TextDecoration.Line_Through || grid.Font.Strikeout)
newWorkbookFont.Strikeout = ExcelDefaultableBoolean.True;
if (cssStyle.TextDecoration == TextDecoration.Underline || grid.Font.Underline)
newWorkbookFont.UnderlineStyle = FontUnderlineStyle.Single;
cellFormat.Font.SetFontFormatting(newWorkbookFont);
switch (cssStyle.TextAlign)
{
case Infragistics.Documents.Reports.TextAlign.Center:
cellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case Infragistics.Documents.Reports.TextAlign.Justify:
cellFormat.Alignment = HorizontalCellAlignment.Justify;
break;
case Infragistics.Documents.Reports.TextAlign.Left:
cellFormat.Alignment = HorizontalCellAlignment.Left;
break;
case Infragistics.Documents.Reports.TextAlign.Right:
cellFormat.Alignment = HorizontalCellAlignment.Right;
break;
default:
cellFormat.Alignment = HorizontalCellAlignment.General;
break;
}
switch (cssStyle.VerticalAlign)
{
case Infragistics.Documents.Reports.VerticalAlign.Bottom:
cellFormat.VerticalAlignment = VerticalCellAlignment.Bottom;
break;
case Infragistics.Documents.Reports.VerticalAlign.Middle:
cellFormat.VerticalAlignment = VerticalCellAlignment.Center;
break;
case Infragistics.Documents.Reports.VerticalAlign.Top:
cellFormat.VerticalAlignment = VerticalCellAlignment.Top;
break;
default:
cellFormat.VerticalAlignment = VerticalCellAlignment.Default;
break;
}
return cellFormat;
}