/// <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;
}
}
// 添加：重载生成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读取数据
/// </summary>
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// <returns>数据ArrarList(List<DataInfoItem>)</returns>
public Tuple <ArrayList, List <string> > ReadWorkbook(string fileName)
{
ArrayList array = new ArrayList();
ISheet sheet = null;
ISheet sheetEdition = null;
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fs);//使用接口，自动识别excel2003/2007格式
sheet = workbook.GetSheet(workbook.GetSheetName(workbook.ActiveSheetIndex));
sheetEdition = workbook.GetSheet("Property");
if (sheetEdition == null)
{
return(null);
}
if (sheet.LastRowNum > 1)
{
int startRow = 2;
int lastRow = sheet.LastRowNum;
for (int i = startRow; i <= lastRow; i++)
{
//行循环
IRow row = sheet.GetRow(i);
//列名
IRow TitleRow = sheet.GetRow(0);
//列循环
List <CellInfo> list = new List <CellInfo>();
for (int t = 0; t < TitleRow.LastCellNum; t++)
{
CellInfo m = new CellInfo();
//读取列名
ICell CellTitle = TitleRow.GetCell(t);
if (CellTitle != null)
{
m.ColumnName = CellTitle.ToString();
}
ICell CellValue = row.GetCell(t);
if (CellValue != null)
{
m.Value = CellValue.ToString();
}
list.Add(m);
}
array.Add(list);
}
}
}
#region 获取版本号
List <string> li = new List <string>();
for (int y = 0; y <= sheetEdition.LastRowNum; y++)
{
if (sheetEdition.GetRow(y) == null)
{
li.Add("");
}
else
{
li.Add(sheetEdition.GetRow(y).GetCell(0).ToString());
}
}
#endregion
return(new Tuple <ArrayList, List <string> >(array, li));
}
catch (Exception ex)
{
throw ex;
}
}
