/// <summary>
/// 将datatable数据生成为excel文件
/// </summary>
/// <param name="info">导出配置信息/param>
/// <param name="dt">导出数据</param>
/// <param name="fileName">文件名</param>
public bool ExportExcelAuto(string tempFileFullPath, SheetInfo info, DataTable dt,
LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition)
{
info.DataArray = this.ConvertDtToList(info, dt);
return(new ExcelOperationHelper().WriteWorkbookAuto(tempFileFullPath, info, dt, dCheck, Edition));
}
/// <summary>
/// 将数据生成为excel文件
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// <param name="info">配置信息</param>
/// <param name="tempFileFullPath">文件名</param>
/// </summary>
public bool WriteWorkbookAuto(string tempFileFullPath, SheetInfo info, DataTable dt,
LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition)
{
try
{
XSSFWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(info.SheetName);
//冻结列
sheet.CreateFreezePane(0, 2, 0, 2);
//工作簿加密
//TODO:暂时不加密 为了标题能够过滤
if (info.Protect)
{
sheet.ProtectSheet(EXCELPWD);
}
#region 主体内容
#region Title样式
IRow rows = sheet.CreateRow(1);
rows.HeightInPoints = 30;
#endregion
#region 隐藏行
IRow rowHidden = sheet.CreateRow(0);
rowHidden.Height = 1;
#endregion
for (int i = 0; i < info.ColInfos.Count; i++)
{
int ColumnWidth = info.ColInfos[i].ColumnWidth == 0 ? 20 : info.ColInfos[i].ColumnWidth;
if (info.ColInfos[i].ColumnHidden)
{
sheet.SetColumnWidth(i, 0);
}
else
{
sheet.SetColumnWidth(i, ColumnWidth * 256);
}
#region Title样式
ICell cell1 = rows.CreateCell(i);
cell1.CellStyle = GetCellStyle(workbook, stylexls.Title);
string titleStr = info.ColInfos[i].ColumnTitle;
//标题*号进行样式设定
IRichTextString rt = workbook.GetCreationHelper().CreateRichTextString(titleStr);
if (titleStr.EndsWith("*"))
{
IFont fontStar = workbook.CreateFont();
fontStar.Boldweight = 600;
fontStar.Color = HSSFColor.OliveGreen.Red.Index;
rt.ApplyFont(titleStr.Length - 1, titleStr.Length, fontStar);
}
cell1.SetCellValue(rt);
#endregion
#region 写入数据KEY
ICell cell = rowHidden.CreateCell(i);
cell.SetCellValue(info.ColInfos[i].ColumnName);
cell.CellStyle.IsLocked = true;
#endregion
#region 添加验证
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList range = getCellRangeAddressList(info.DataArray.Count, i);
if (info.ColInfos[i].ColumnRangeValues != null)
{
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationExplicitList(helper, range, info.ColInfos[i].ColumnRangeValues));
}
if (info.ColInfos[i].ColValMaxLength.HasValue)
{
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationTextLength(helper, range,
1, info.ColInfos[i].ColValMaxLength.GetValueOrDefault()));
}
switch (info.ColInfos[i].ColumnValidation)
{
case EmuExcelCellType.Integer:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
break;
case EmuExcelCellType.Decimal:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
break;
case EmuExcelCellType.YearMonth:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationYearMonth(helper, range));
break;
default:
break;
}
#endregion
#region 筛选
CellRangeAddress CellRange = new CellRangeAddressList(1, 1, 0, i).CellRangeAddresses[0];
sheet.SetAutoFilter(CellRange);
#endregion
}
ICellStyle style = SetCellBorder(workbook, false);
ICellStyle styleLock = SetCellBorder(workbook, true);
foreach (List <CellInfo> items in info.DataArray)
{
foreach (CellInfo item in items)
{
IRow row = sheet.GetRow(int.Parse(item.YPosition));
if (row == null)
{
row = sheet.CreateRow(int.Parse(item.YPosition));
}
ICell cell = row.GetCell(int.Parse(item.XPosition));
if (cell == null)
{
cell = row.CreateCell(int.Parse(item.XPosition));
}
double cellValue = 0;
//如果是数值 把单元格格式设置为数值
if (double.TryParse(item.Value, out cellValue))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(cellValue);
//XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//IDataFormat dataFormat = workbook.CreateDataFormat();
//cellStyle.DataFormat = dataFormat;
//cellStyle.
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(item.Value);
}
cell.CellStyle = item.ColumnLock ? styleLock : style;
}
}
#endregion
#region 数据校验
bool hasErr = false;
if (dCheck != null)
{
//复制dt结构（不复制数据）
DataTable dtTemp = dt.Clone();
DataRow[] rules = dt.Select();
foreach (var dr in rules)
{
//int RowCount = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).LastCellNum;
//for (int i = 2; i < RowCount; i++)
//{
// ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Cells[i];
// Cell.CellComment = null;
// Cell.CellStyle = style;
//}
dtTemp.Rows.Add(dr.ItemArray);
Tuple <List <string[]> > tu = dCheck.Invoke(dtTemp);
if (tu.Item1.Count > 0)
{
hasErr = true;
for (int i = 0; i < tu.Item1.Count; i++)
{
int iConNum = int.Parse(tu.Item1[i][0]);
string strMsg = tu.Item1[i][1];
ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).GetCell(iConNum);
Cell.CellStyle = GetCellBorderException(workbook);
Cell.CellComment = null;
Cell.CellComment = GetCellComment(sheet, strMsg);
}
}
else
{
//隐藏正确行
sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Height = 1;
}
dtTemp.Rows.Clear();
}
}
#endregion
#region 版本号
ISheet sheetEdition = null;
sheetEdition = workbook.CreateSheet("Property");
for (int r = 0; r < Edition.Length; r++)
{
sheetEdition.CreateRow(r).CreateCell(0).SetCellValue(Edition[r]);
}
workbook.SetSheetHidden(1, SheetState.VeryHidden);
#endregion
//写入文件
using (FileStream fs = new FileStream(tempFileFullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
fs.Close();
}
return(hasErr);
}
catch (Exception ex)
{
throw ex;
}
}
