public override void SetBackgroudColor(System.Drawing.Color color)
{
IWorkbook workbook = _npoiWorksheet.Workbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum));
if (workbook is HSSFWorkbook)
{
HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = hssFColor.Indexed;
}
else
{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = hssFColor.Indexed;
//No way!
}
_npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle);
}
public virtual void TestDefaultColumnStyle()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICellStyle style = wb.CreateCellStyle();
ISheet sheet = wb.CreateSheet();
sheet.SetDefaultColumnStyle(/*setter*/ 0, style);
Assert.IsNotNull(sheet.GetColumnStyle(0));
Assert.AreEqual(style.Index, sheet.GetColumnStyle(0).Index);
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
ICellStyle style2 = cell.CellStyle;
Assert.IsNotNull(style2);
Assert.AreEqual(style.Index, style2.Index, "style should match");
}
private void SetUpHeader(ISheet sheet, ClassMapping classMapping)
{
var workbook = sheet.Workbook;
var row = sheet.GetRow(0) ?? sheet.CreateRow(0);
InitMappingColumns(classMapping);
CacheColumnIndices(classMapping);
//freeze header row
sheet.CreateFreezePane(0, 1);
//create header and column style for every mapped column
foreach (var mapping in classMapping.PropertyMappings)
{
var cellIndex = CellReference.ConvertColStringToIndex(mapping.Column);
var cell = row.GetCell(cellIndex) ?? row.CreateCell(cellIndex);
cell.SetCellType(CellType.String);
cell.SetCellValue(mapping.Header);
cell.CellStyle = GetStyle(DataStatus.Header, workbook);
//set default column style if not defined but available
var style = GetStyle(mapping.Status, workbook);
if (mapping.Status == DataStatus.None)
{
continue;
}
var existStyle = sheet.GetColumnStyle(cellIndex);
if (
existStyle != null &&
existStyle.FillForegroundColor == style.FillForegroundColor &&
existStyle.BorderTop == style.BorderTop &&
existStyle.TopBorderColor == style.TopBorderColor
)
{
continue;
}
//create new style
sheet.SetDefaultColumnStyle(cellIndex, style);
sheet.SetColumnHidden(cellIndex, false);
//set default width
sheet.SetColumnWidth(cellIndex, 256 * 15);
//hide if defined
if (mapping.Hidden)
{
sheet.SetColumnHidden(cellIndex, true);
}
}
//set up filter
var lastPropMap = classMapping.PropertyMappings.OrderBy(p => p.ColumnIndex).LastOrDefault();
if (lastPropMap != null)
{
sheet.SetAutoFilter(new CellRangeAddress(0, 1, 0, lastPropMap.ColumnIndex));
}
}
public static Stream DataTableToExcel(string fileName, DataTable data,
List <ExcelColumnFormat> colFmt,
string cols,
Func <object, string, object> tranfunc = null,
bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null)
{
if (data.Rows.Count == 0)
{
return(null);
} //data为空，直接退出
int i = 0;
int j = 0;
int count = 0;
IWorkbook workbook = null;
ISheet sheet = null;
NPOIMemoryStream ms = null;
try
{
//转换对齐
Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment =>
{
switch (alignment)
{
case System.Drawing.ContentAlignment.BottomRight:
case System.Drawing.ContentAlignment.MiddleRight:
case System.Drawing.ContentAlignment.TopRight:
return(HorizontalAlignment.Right);
case System.Drawing.ContentAlignment.BottomCenter:
case System.Drawing.ContentAlignment.MiddleCenter:
case System.Drawing.ContentAlignment.TopCenter:
return(HorizontalAlignment.Center);
}
return(HorizontalAlignment.Left);
};
//设置值的格式
Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) =>
{
string rlt = "";
string vstr = Ass.P.PStr(v);
string tv = null;
if (tranfunc != null)
{
tv = Ass.P.PStr(tranfunc(v, colCode));
}
ExcelColumnFormat fmt = null;
if (tv != null && tv != vstr)
{
rlt = tv;
}
else if (colFmt.Any(m => m.ColumnNameCode == colCode))
{
rlt = vstr;
string v0 = "";
fmt = colFmt.Find(m => m.ColumnNameCode == colCode);
if (v != null && fmt.ToStringFormat.IsNotEmpty())
{
v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v);
if (v0 != vstr)
{
rlt = v0;
}
}
}
else
{
rlt = vstr;
}
//设置值
var cell = row.CreateCell(j);
var t = rlt.GetStringType();
double dbval = 0; DateTime dtval;
if (double.TryParse(rlt, out dbval))
{
cell.SetCellValue(dbval);
}
else if (DateTime.TryParse(rlt, out dtval))
{
cell.SetCellValue(rlt);
}
else
{
cell.SetCellValue(rlt);
}
};
using (ms = new NPOIMemoryStream())
{
if (!GetWorkbook(fileName, out workbook))
{
return(null);
}
List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合
sheet = workbook.CreateSheet(sheetName);
//表标题
if (tableTitle.IsNotEmpty())
{
IRow row = sheet.CreateRow(count++);
ICell cell = row.CreateCell(0);
XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle();
IFont ztFont = workbook.CreateFont();
ztFont.FontHeightInPoints = 14;
ztFont.Underline = FontUnderlineType.DoubleAccounting;
ztStyle.SetFont(ztFont);
ztStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = ztStyle;
cell.SetCellValue(tableTitle);
var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1)); //合并单元格
}
Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>();
//写入DataTable的列名
if (isColumnWritten == true)
{
IRow row = sheet.CreateRow(count++);
if (colList.Count > 0) //设定了输出的列
{
for (j = 0; j < colList.Count(); j++)
{
var key = colList[j];
var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key;
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(keyname);
//设置对齐与数据格式
var cStyle = workbook.CreateCellStyle(); bool bfmt = false;
if (cellfmt.Alignment != null)
{
bfmt = true;
cStyle.Alignment = _transHAlignment(cellfmt.Alignment);
}
if (cellfmt.DataFormat.IsNotEmpty())
{
bfmt = true;
short?dfmt = null; IDataFormat format = workbook.CreateDataFormat();
switch (cellfmt.DataFormat)
{
case "Price": dfmt = format.GetFormat("#,##0.00"); break;
case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break;
case "DateTime": dfmt = 0x16; break; // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break;
case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break;
}
cStyle.DataFormat = dfmt.Value;
}
if (bfmt)
{
_columeCellStyles.Add(j, cStyle);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256));
}
}
}
else
{
for (j = 0; j < data.Columns.Count; j++)
{
string key = data.Columns[j].ColumnName;
if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文
{
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(cellfmt.ColumnName);
//设置对齐
if (cellfmt.Alignment != null)
{
sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value);
}
}
else
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
}
}
}
//写入数据
if (colList.Count > 0)
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < colList.Count; j++)
{
_val(row, j, data.Rows[i][colList[j]], colList[j]);
}
}
}
else
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; j++)
{
_val(row, j, data.Rows[i][j], data.Columns[j].ColumnName);
}
}
}
//设置列格式
foreach (int colIndex in _columeCellStyles.Keys)
{
sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]);
}
workbook.Write(ms); //写入到excel
ms.Flush();
ms.Position = 0;
return(ms);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message);
return(null);
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource">数据表</param>
/// <param name="drSource">来源数据行</param>
/// <param name="currentExcelRow">当前数据行</param>
/// <param name="excelSheet">sheet集合</param>
/// <param name="excelWorkBook">数据表</param>
/// <param name="execlCellStyleList">表头样式</param>
/// <param name="rowCount">execl当前行</param>
private static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook, List <ExeclCellStyle> execlCellStyleList, int rowCount)
{
int cellIndex = 0;
foreach (var item in ListColumnsName)
{
//列名称
string columnsName = item.ToString();
//根据列名称设置列样式
ExeclCellStyle execlCellStyle = execlCellStyleList?.FirstOrDefault(it => it.ColumnsName == columnsName) ?? null;
ICellStyle columnStyle = excelSheet.GetColumnStyle(cellIndex);
//设置列样式
//excelSheet.SetDefaultColumnStyle(cellIndex, SetExeclColumnStyle(columnStyle, excelWorkBook, execlCellStyle));
//设置列宽
if ((execlCellStyle?.Width ?? 0) > 0)
{
excelSheet.SetColumnWidth(cellIndex, execlCellStyle.Width);
}
if (execlCellStyle?.IsHidden ?? false)
{
excelSheet.SetColumnHidden(cellIndex, true);
}
ICell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
//错误消息列
string errColumn = columnsName + "ErrMsg";
string errMsg = string.Empty;
if (dtSource.Columns.Contains(errColumn))
{
errMsg = drSource[errColumn].ToString().Trim();
}
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.Guid": //GUID类型
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
IDataFormat format = excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
//设置单元格样式
SetExeclCellStyle(newCell, execlCellStyle);
//设置批注
if (!string.IsNullOrWhiteSpace(errMsg))
{
SetComment(errMsg, currentExcelRow, newCell, excelWorkBook.GetCreationHelper(), (HSSFPatriarch)excelSheet.CreateDrawingPatriarch());
}
//设置计算表达式
if (!string.IsNullOrEmpty(execlCellStyle.SetCellFormula))
{
int rowIndex = rowCount + 1;
newCell.SetCellFormula(string.Format(execlCellStyle.SetCellFormula, rowIndex));
if (excelSheet.ForceFormulaRecalculation == false)
{
excelSheet.ForceFormulaRecalculation = true;//没有此句，则不会刷新出计算结果
}
}
cellIndex++;
}
bool shouldLock = execlCellStyleList?.FirstOrDefault(it => it.IsLock == true)?.IsLock ?? false;
if (shouldLock == true)
{
excelSheet.ProtectSheet("password");//设置密码保护
}
}
/// <summary>
/// 设置列样式，默认带边框
/// </summary>
/// <param name="sheet"></param>
/// <param name="columnIndex"></param>
/// <param name="columnName"></param>
/// <param name="columnType"></param>
public void SetColumnDefaultStyle(ISheet sheet, int columnIndex, Type columnType = null, HorizontalAlignment?align = null)
{
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
if (columnIndex < 0)
{
throw new IndexOutOfRangeException("columnIndex");
}
var previousColumnStyle = sheet.GetColumnStyle(columnIndex);
var styleKey = Tuple.Create(columnType, align);
ICellStyle defaultColumnStyle;
if (!styles.TryGetValue(styleKey, out defaultColumnStyle))
{
defaultColumnStyle = CreateCellStyle();
defaultColumnStyle.CloneStyleFrom(defaultCellStyle);
if (columnType != null && columnType.IsValueType)
{
if (columnType == typeof(DateTime))
{
defaultColumnStyle.DataFormat = (short)Workbook.CreateFormat("yyyy-MM-dd");
}
defaultColumnStyle.Alignment = HorizontalAlignment.Right;
}
else if (columnType == typeof(string))
{
defaultColumnStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
}
if (align.HasValue)
{
switch (align)
{
case HorizontalAlignment.Center:
defaultColumnStyle.Alignment = HorizontalAlignment.Center;
break;
case HorizontalAlignment.Left:
defaultColumnStyle.Alignment = HorizontalAlignment.Left;
break;
case HorizontalAlignment.Right:
defaultColumnStyle.Alignment = HorizontalAlignment.Right;
break;
}
}
styles[styleKey] = defaultColumnStyle;
}
sheet.SetDefaultColumnStyle(columnIndex, defaultColumnStyle);
// 刷新已知样式
if (previousColumnStyle != null)
{
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
var cell = sheet.GetRow(i).GetCell(columnIndex);
if (cell != null && Equals(cell.CellStyle, previousColumnStyle))
{
cell.CellStyle = defaultColumnStyle;
}
}
}
}
public ICellStyle GetColumnStyle(int column)
{
return(_sheet.GetColumnStyle(column));
}