/// <summary>
/// 数据格式转换(datatable to arraylist)
/// </summary>
/// <param name="info">导出配置信息</param>
/// <param name="dt">导出数据</param>
/// <returns>格式转换后数据</returns>
private ArrayList GetDataArray(SheetInfo info, DataTable dt)
{
ArrayList array = new ArrayList();
// 添加：扩展支持 表标题显示 李鹏飞 2014-03-31 开始
// 表标题
info.TableCaption = dt.TableName;
// 添加：扩展支持 表标题显示 李鹏飞 2014-03-31 结束
int count = 0;
int rowIndex = 0;
int colIndex = 0;
foreach (DataRow row in dt.Rows)
{
List <CellInfo> list = new List <CellInfo>();
foreach (CellInfo item in info.ColInfos)
{
rowIndex = int.Parse(item.YPosition) + count - 1;
colIndex = ExcelOperationHelper.NameToIndex(item.XPosition);
CellInfo m = new CellInfo();
m.XPosition = colIndex.ToString();
m.YPosition = rowIndex.ToString();
m.ColumnName = item.ColumnName;
m.DataType = item.DataType;
if (dt.Columns.Contains(item.ColumnName) == true)
{
if (row[item.ColumnName] != System.DBNull.Value)
{
m.Value = row[item.ColumnName].ToString();
}
else
{
m.Value = "";
}
}
else
{
m.Value = "";
}
list.Add(m);
}
array.Add(list);
count++;
if (info.RecordCount == "1" && count == 1)
{
break;
}
}
return(array);
}
/// <summary>
/// 将数据生成为CSV文件
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// </summary>
public void WriteWorkbook(SheetInfo info, string fileName)
{
try
{
using (System.IO.FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write))
{
StreamWriter sw = new StreamWriter(fs, Encoding.GetEncoding("UTF-8"));
//输出列头
foreach (CellInfo cell in info.ColInfos)
{
sw.Write("\"" + cell.ColumnTitle + "\"");
sw.Write(",");
}
sw.WriteLine("");
//输出数据
foreach (DataRow row in info.Dt.Rows)
{
foreach (CellInfo cell in info.ColInfos)
{
if (info.Dt.Columns.Contains(cell.ColumnName) == true)
{
if (row[cell.ColumnName] != System.DBNull.Value)
{
sw.Write("\"" + row[cell.ColumnName].ToString() + "\"");
}
else
{
sw.Write("\"\"");
}
}
else
{
sw.Write("\"\"");
}
sw.Write(",");
}
sw.WriteLine("");
}
sw.Close();
sw.Dispose();
fs.Close();
fs.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 数据格式转换(datatable to arraylist)
/// </summary>
/// <param name="info">导出配置信息</param>
/// <param name="dt">导出数据</param>
/// <returns>格式转换后数据</returns>
private ArrayList ConvertDtToList(SheetInfo info, DataTable dt)
{
ArrayList array = new ArrayList();
int count = 0;
int rowIndex = 0;
int colIndex = 0;
foreach (DataRow row in dt.Rows)
{
List <CellInfo> list = new List <CellInfo>();
foreach (CellInfo item in info.ColInfos)
{
rowIndex = int.Parse(item.YPosition) + count - 1;
colIndex = int.Parse(item.XPosition);
CellInfo m = new CellInfo();
m.XPosition = colIndex.ToString();
m.YPosition = rowIndex.ToString();
if (dt.Columns.IndexOf(item.ColumnName) < 0)
{
continue;
}
if (row[item.ColumnName] != System.DBNull.Value)
{
m.Value = row[item.ColumnName].ToString();
m.ColumnTitle = item.ColumnTitle;
m.ColumnLock = item.ColumnLock;
}
else
{
m.Value = "";
m.ColumnTitle = item.ColumnTitle;
m.ColumnLock = item.ColumnLock;
}
list.Add(m);
}
array.Add(list);
count++;
if (info.RecordCount == "1" && count == 1)
{
break;
}
}
return(array);
}
/// <summary>
/// 数据格式转换(arraylist to datatable)
/// </summary>
/// <param name="info">导出配置信息</param>
/// <param name="dt">导出数据</param>
/// <returns>格式转换后数据</returns>
private DataTable GetDataTable(SheetInfo info, DataTable dt, ArrayList array)
{
DataTable infoDt = null;
try
{
if (dt != null)
{
infoDt = dt;
}
else
{
infoDt = new DataTable();
//初始化结构
foreach (CellInfo item in info.ColInfos)
{
infoDt.Columns.Add(new DataColumn(item.ColumnName));
}
}
//填充数据
foreach (List <CellInfo> items in array)
{
DataRow dr = infoDt.NewRow();
foreach (CellInfo item in items)
{
if (infoDt.Columns.IndexOf(item.ColumnName) >= 0)
{
dr[item.ColumnName] = item.Value;
}
}
infoDt.Rows.Add(dr);
}
return(infoDt);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将excel数据导入到datatable
/// </summary>
/// <param name="info">配置信息</param>
/// <param name="dt">带结构的空表</param>
/// <param name="fileName">数据文件</param>
/// <returns>数据表</returns>
public DataTable ImportExcel(SheetInfo info, DataTable dt, string fileName)
{
DataTable infoDt = null;
ArrayList dataArray = null;
try
{
//获取数据
dataArray = new ExcelOperationHelper().ReadWorkbook(info, fileName);
//数据格式转换
infoDt = this.GetDataTable(info, dt, dataArray);
return(infoDt);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将datatable数据生成为excel文件
/// </summary>
/// <param name="info">导出配置信息</param>
/// <param name="dt">导出数据</param>
/// <param name="fileName">文件名</param>
/// <param name="isBuild">是否通过模板生成</param>
public void ExportExcel(SheetInfo info, DataTable dt, string fileName, bool isBuild)
{
if (fileName.LastIndexOf(".") < 0)
{
throw new Exception("文件未设置扩展名");
}
string extensions = fileName.Substring(fileName.LastIndexOf(".") + 1);
switch (extensions.ToLower())
{
case "csv":
info.Dt = dt;
new CSVOperationHelper().WriteWorkbook(info, fileName);
break;
default:
#region 数据转换
info.DataArray = this.GetDataArray(info, dt);
#endregion
//xls、xlsx
if (isBuild == true && string.IsNullOrEmpty(info.TemplateFile) == false)
{
//根据模板生成文件
string templateFile = FilePath + info.TemplateFile;
System.IO.File.Copy(templateFile, fileName);
}
new ExcelOperationHelper().WriteWorkbook(info, fileName);
break;
}
dt.Rows.Clear();
dt.Dispose();
GC.Collect();
}
/// <summary>
/// 获取配置信息
/// </summary>
/// <returns>配置信息</returns>
public SheetInfo GetMainInfoAuto(string strSheetName, List <ColumnInfo> li)
{
SheetInfo info = new SheetInfo();
try
{
#region 读取配置信息
//info.TemplateFile = TemplateFile;
info.SheetName = string.IsNullOrEmpty(strSheetName) ? "sheet" : strSheetName;
info.ColInfos = new List <CellInfo>();
int i = 0;
foreach (ColumnInfo c in li)
{
CellInfo item = new CellInfo()
{
ColumnHidden = c.ColumnHidden,
ColumnValidation = c.ColumnValidation,
ColumnLock = c.ColumnLock,
ColumnWidth = c.ColumnWidth,
ColumnName = c.ColumnName,
ColumnTitle = c.ColumnTitle,
XPosition = i.ToString(),
YPosition = "3",
ColumnRangeValues = c.ColumnRangeValues,
ColValMaxLength = c.ColValMaxLength,
};
info.ColInfos.Add(item);
i++;
}
#endregion
return(info);
}
catch (Exception ex)
{
throw ex;
}
}
// 添加：重载生成excel文件方法，扩展支持多表、表头，单元格样式等 李鹏飞 2014-03-31 结束
#endregion
#region 从excel读取数据
/// <summary>
/// 从excel读取数据
/// </summary>
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// <returns>数据ArrarList(List<DataInfoItem>)</returns>
public ArrayList ReadWorkbook(SheetInfo info, string fileName)
{
ArrayList array = new ArrayList();
ISheet sheet = null;
XSSFWorkbook workbook1 = null;
HSSFWorkbook workbook2 = null;
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
//获取工作表
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1 = new XSSFWorkbook(fs);
sheet = workbook1.GetSheet(info.SheetName);
}
else
{
workbook2 = new HSSFWorkbook(fs);
sheet = workbook2.GetSheet(info.SheetName);
}
if (sheet == null)
{
throw new Exception("数据文件中的sheet页名称与配置文件中的不一致。");
}
if (info.RecordCount == "n")
{
//多条
int startRow = info.ColInfos.Min(p => int.Parse(p.YPosition)) - 1;
int lastRow = sheet.LastRowNum;
for (int i = startRow; i <= lastRow; i++)
{
//行循环
IRow row = sheet.GetRow(i);
//列循环
List <CellInfo> list = new List <CellInfo>();
foreach (CellInfo item in info.ColInfos)
{
CellInfo m = new CellInfo();
m.ColumnName = item.ColumnName;
if (row != null)
{
int colIndex = NameToIndex(item.XPosition);
ICell cell = row.GetCell(colIndex);
if (cell != null)
{
m.Value = cell.ToString();
}
}
else
{
m.Value = "";
}
list.Add(m);
}
int count = list.Count(p => string.IsNullOrEmpty(p.Value) == false);
if (count > 0)
{
array.Add(list);
}
}
}
else
{
//单条
List <CellInfo> list = new List <CellInfo>();
foreach (CellInfo item in info.ColInfos)
{
int rowIndex = int.Parse(item.YPosition) - 1;
int colIndex = NameToIndex(item.XPosition);
CellInfo m = new CellInfo();
m.ColumnName = item.ColumnName;
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
m.Value = "";
list.Add(m);
continue;
}
ICell cell = row.GetCell(colIndex);
if (cell != null)
{
m.Value = cell.ToString();
}
}
array.Add(list);
}
}
return(array);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将数据生成为excel文件
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// </summary>
public void WriteWorkbook(SheetInfo info, string fileName)
{
ISheet sheet = null;
XSSFWorkbook workbook1 = null;
HSSFWorkbook workbook2 = null;
try
{
//创建工作簿
if (string.IsNullOrEmpty(info.TemplateFile) == true)
{
//不使用模板
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1 = new XSSFWorkbook();
sheet = workbook1.CreateSheet(info.SheetName);
}
else
{
workbook2 = new HSSFWorkbook();
sheet = workbook2.CreateSheet(info.SheetName);
}
}
else
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
//使用模板
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1 = new XSSFWorkbook(fs);
sheet = workbook1.GetSheet(info.SheetName);
}
else
{
workbook2 = new HSSFWorkbook(fs);
sheet = workbook2.GetSheet(info.SheetName);
}
}
File.Delete(fileName);
}
//写入文件
if (string.IsNullOrEmpty(info.TemplateFile) == true)
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
#region 无模板
//写入列标题
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < info.ColInfos.Count; i++)
{
headRow.CreateCell(i).SetCellValue(info.ColInfos[i].ColumnTitle);
headRow.Cells[i].CellStyle.Alignment = HorizontalAlignment.Center;
if (info.ColInfos[i].ColumnWidth != 0)
{
sheet.SetColumnWidth(i, Convert.ToInt32((info.ColInfos[i].ColumnWidth / 3) * 0.4374 * 256));
}
}
//写入行数据
int rowsNum = 1;
IRow dataRow = null;
foreach (List <CellInfo> items in info.DataArray)
{
dataRow = sheet.CreateRow(rowsNum);
for (int i = 0; i < items.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(items[i].Value);
}
rowsNum++;
}
#endregion
info.DataArray.Clear();
info.Dispose();
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Close();
fs.Dispose();
GC.Collect();
}
}
else
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
#region 模板
int rowIndex = 0;
List <ICell> firstCells = new List <ICell>();
int count = info.DataArray.Count;
for (int k = 0; k < count; k++)
{
List <CellInfo> items = info.DataArray[0] as List <CellInfo>;
rowIndex++;
for (int i = 0; i < items.Count; i++)
{
IRow row = sheet.GetRow(int.Parse(items[i].YPosition));
if (row == null)
{
row = sheet.CreateRow(int.Parse(items[i].YPosition));
}
ICell cell = row.GetCell(int.Parse(items[i].XPosition));
if (rowIndex == 1)
{
firstCells.Add(cell);
}
if (cell == null)
{
cell = row.CreateCell(int.Parse(items[i].XPosition));
}
if (firstCells[i] != null)
{
cell.CellStyle = firstCells[i].CellStyle;
}
if (items[i].Value != "")
{
switch (items[i].DataType.ToLower())
{
case "datetime":
cell.SetCellValue(DateTime.Parse(items[i].Value));
break;
case "number":
cell.SetCellValue(double.Parse(items[i].Value));
break;
default:
cell.SetCellValue(items[i].Value);
break;
}
}
}
items.Clear();
items = null;
info.DataArray.RemoveAt(0);
#region 分段保存
if ((rowIndex % 20000) == 0)
{
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
fs.Close();
fs.Dispose();
GC.Collect();
if (k <= count - 1)
{
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
}
}
#endregion
}
#endregion
info.DataArray.Clear();
info.Dispose();
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (fs != null)
{
fs.Close();
fs.Dispose();
GC.Collect();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <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>
/// 获取配置信息
/// </summary>
/// <returns>配置信息</returns>
public SheetInfo GetMainInfo(string infoName)
{
SheetInfo info = new SheetInfo();
string configFile = FilePath + infoName + ".xml";
try
{
ConfigFile = configFile;
#region 读取配置信息
XElement xel = XElement.Load(configFile);
var datas = from x in xel.Descendants("DataInfoMain")
select x;
var data = datas.First();
info.InfoName = infoName;
info.SheetName = data.Attribute("InfoName").Value;
info.TemplateFile = data.Attribute("TemplateFile").Value;
info.RecordCount = data.Attribute("RecordCount").Value;
// 添加：重载生成excel文件方法，扩展支持多表、表头，单元格样式等 李鹏飞 2014-03-31 开始
// 是否显示表标题
if (data.Attribute("IsShowTableCaption") != null && data.Attribute("IsShowTableCaption").Value == "1")
{
info.IsShowTableCaption = true;
}
// 是否因此列头
if (data.Attribute("IsHideColumnHeader") != null && data.Attribute("IsHideColumnHeader").Value == "1")
{
info.IsHideColumnHeader = true;
}
// 样式
var style = data.Element("Style");
if (style != null)
{
Hashtable hsColors = NPOI.HSSF.Util.HSSFColor.GetIndexHash();
#region 表标题样式
var tableCaptionCellStyleXel = style.Element("TableCaptionCellStyle");
if (tableCaptionCellStyleXel != null)
{
CellStyle tableCaptionCellStyle = new CellStyle();
#region 单元格
if (tableCaptionCellStyleXel.Attribute("FillForegroundColor") != null &&
!string.IsNullOrEmpty(tableCaptionCellStyleXel.Attribute("FillForegroundColor").Value))
{
tableCaptionCellStyle.FillForegroundColor = GetColorIndex(hsColors, tableCaptionCellStyleXel.Attribute("FillForegroundColor").Value);
}
if (tableCaptionCellStyleXel.Attribute("BorderStyle") != null &&
!string.IsNullOrEmpty(tableCaptionCellStyleXel.Attribute("BorderStyle").Value))
{
tableCaptionCellStyle.BorderStyle = (BorderStyle)Enum.Parse(typeof(BorderStyle), tableCaptionCellStyleXel.Attribute("BorderStyle").Value);
}
#endregion
var tableCaptionFontXel = tableCaptionCellStyleXel.Element("Font");
if (tableCaptionFontXel != null)
{
FontStyle tableCaptionFontStyle = new FontStyle();
#region 字体
if (tableCaptionFontXel.Attribute("FontName") != null && !string.IsNullOrEmpty(tableCaptionFontXel.Attribute("FontName").Value))
{
tableCaptionFontStyle.FontName = tableCaptionFontXel.Attribute("FontName").Value;
}
if (tableCaptionFontXel.Attribute("FontHeightInPoints") != null && !string.IsNullOrEmpty(tableCaptionFontXel.Attribute("FontHeightInPoints").Value))
{
tableCaptionFontStyle.FontHeightInPoints = (short)Convert.ToInt32((tableCaptionFontXel.Attribute("FontHeightInPoints").Value));
}
if (tableCaptionFontXel.Attribute("Boldweight") != null && !string.IsNullOrEmpty(tableCaptionFontXel.Attribute("Boldweight").Value))
{
tableCaptionFontStyle.Boldweight = (short)Convert.ToInt32((tableCaptionFontXel.Attribute("Boldweight").Value));
}
if (tableCaptionFontXel.Attribute("Color") != null && !string.IsNullOrEmpty(tableCaptionFontXel.Attribute("Color").Value))
{
tableCaptionFontStyle.Color = GetColorIndex(hsColors, tableCaptionFontXel.Attribute("Color").Value);
}
#endregion
tableCaptionCellStyle.Font = tableCaptionFontStyle;
}
info.TableCaptionStyle = tableCaptionCellStyle;
}
#endregion
#region 列头样式
var columnHeaderCellStyleXel = style.Element("ColumnHeaderCellStyle");
if (columnHeaderCellStyleXel != null)
{
CellStyle columnHeaderCellStyle = new CellStyle();
//设置样式
this.SetCellStyle(columnHeaderCellStyle, columnHeaderCellStyleXel);
var columnHeaderFontXel = columnHeaderCellStyleXel.Element("Font");
if (columnHeaderFontXel != null)
{
FontStyle columnHeaderFontStyle = new FontStyle();
//设置字体
this.SetCellFont(columnHeaderFontStyle, columnHeaderFontXel);
columnHeaderCellStyle.Font = columnHeaderFontStyle;
}
info.ColumnHeaderStyle = columnHeaderCellStyle;
}
#endregion
#region 内容单元格样式
var contentCellStyleXel = style.Element("ContentCellStyle");
if (contentCellStyleXel != null)
{
CellStyle contentCellStyle = new CellStyle();
//设置样式
this.SetCellStyle(contentCellStyle, contentCellStyleXel);
var contentCellFontXel = contentCellStyleXel.Element("Font");
if (contentCellFontXel != null)
{
FontStyle contentCellFontStyle = new FontStyle();
//设置字体
this.SetCellFont(contentCellFontStyle, contentCellFontXel);
contentCellStyle.Font = contentCellFontStyle;
}
info.ContentCellStyle = contentCellStyle;
}
#endregion
}
// 添加：重载生成excel文件方法，扩展支持多表、表头，单元格样式等 李鹏飞 2014-03-31 结束
info.ColInfos = new List <CellInfo>();
foreach (XElement c in data.Element("DataInfoItems").Descendants("DataInfoItem"))
{
CellInfo item = new CellInfo();
item.ColumnName = c.Attribute("ColumnName").Value;
item.ColumnTitle = c.Attribute("ColumnTitle").Value;
item.XPosition = c.Attribute("XPosition").Value;
item.YPosition = c.Attribute("YPosition").Value;
item.DataType = c.Attribute("DataType") != null?c.Attribute("DataType").Value : "";
// 添加：重载生成excel文件方法，扩展支持多表、表头，单元格样式等 李鹏飞 2014-03-31 开始
// 列宽
if (c.Attribute("ColumnWidth") != null && !string.IsNullOrEmpty(c.Attribute("ColumnWidth").Value))
{
item.ColumnWidth = Convert.ToInt32(c.Attribute("ColumnWidth").Value);
}
// 添加：重载生成excel文件方法，扩展支持多表、表头，单元格样式等 李鹏飞 2014-03-31 结束
info.ColInfos.Add(item);
}
#endregion
return(info);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 从CSV读取数据
/// </summary>
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// <returns>数据ArrarList(List<DataInfoItem>)</returns>
public ArrayList ReadWorkbook(SheetInfo info, string fileName)
{
throw new Exception("功能未实现");
}
/// <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;
}
}