/// <summary>
/// 获取导出列的列表
/// </summary>
/// <returns></returns>
public List <ExportColumn> GetExportColumnList()
{
if (DataTableSchema == null)
{
return(null);
}
XmlNode tableNode = DataTableSchema.SelectSingleNode("DataTableSchema/Columns");
List <ExportColumn> list = new List <ExportColumn>();
if (tableNode != null)
{
ExportColumn mExportColumn = null;
foreach (XmlNode node in tableNode.ChildNodes)
{
mExportColumn = new ExportColumn();
mExportColumn.ColumnName = node.Attributes["Name"] == null ? String.Empty : node.Attributes["Name"].Value;
mExportColumn.Description = node.Attributes["Description"] == null ? String.Empty : node.Attributes["Description"].Value;
mExportColumn.DataType = node.Attributes["Type"] == null ? String.Empty : node.Attributes["Type"].Value;
mExportColumn.ColumnNumber = node.Attributes["ColumnNumber"] == null ? 0 : Convert.ToInt32(node.Attributes["ColumnNumber"].Value);
mExportColumn.ColumnWidth = node.Attributes["ColumnWidth"] == null ? 100 : Convert.ToInt32(node.Attributes["ColumnWidth"].Value);
mExportColumn.Category = node.Attributes["Category"] == null ? String.Empty : node.Attributes["Category"].Value;
list.Add(mExportColumn);
}
}
return(list);
}
/// <summary>
/// 获取xml配置定义的结构
/// </summary>
/// <param name="schemaPath">xml配置路径</param>
/// <returns></returns>
public static List <ExportColumn> GetTableSchemaToExportColumn(string schemaPath)
{
XmlDocument schemaDoc = new XmlDocument();
schemaDoc.Load(schemaPath);
XmlNode tableNode = schemaDoc.SelectSingleNode("DataTableSchema/Columns");
List <ExportColumn> list = new List <ExportColumn>();
if (tableNode != null)
{
ExportColumn mExportColumn = null;
foreach (XmlNode node in tableNode.ChildNodes)
{
mExportColumn = new ExportColumn();
mExportColumn.ColumnName = node.Attributes["Name"] == null ? String.Empty : node.Attributes["Name"].Value;
mExportColumn.Description = node.Attributes["Description"] == null ? String.Empty : node.Attributes["Description"].Value;
mExportColumn.DataType = node.Attributes["Type"] == null ? String.Empty : node.Attributes["Type"].Value;
mExportColumn.ColumnNumber = node.Attributes["ColumnNumber"] == null ? 0 : Convert.ToInt32(node.Attributes["ColumnNumber"].Value);
mExportColumn.ColumnWidth = node.Attributes["ColumnWidth"] == null ? 100 : Convert.ToInt32(node.Attributes["ColumnWidth"].Value);
mExportColumn.Category = node.Attributes["Category"] == null ? String.Empty : node.Attributes["Category"].Value;
list.Add(mExportColumn);
}
}
return(list);
}
/// <summary>
/// 获取要导出列的列表
/// </summary>
/// <returns></returns>
public Dictionary <string, List <ExportColumn> > GetExportColumnDictionary()
{
if (DataTableSchema == null)
{
return(null);
}
XmlNode tableNode = DataTableSchema.SelectSingleNode("DataTableSchema/Columns");
Dictionary <string, List <ExportColumn> > dicColumns = new Dictionary <string, List <ExportColumn> >();
if (tableNode != null)
{
ExportColumn mExportColumn = null;
string categoryTemp = "";
foreach (XmlNode node in tableNode.ChildNodes)
{
categoryTemp = node.Attributes["Category"] == null ? String.Empty : node.Attributes["Category"].Value;
if (!dicColumns.ContainsKey(categoryTemp))
{
dicColumns.Add(categoryTemp, new List <ExportColumn>());
}
mExportColumn = new ExportColumn();
mExportColumn.ColumnName = node.Attributes["Name"] == null ? String.Empty : node.Attributes["Name"].Value;
mExportColumn.Description = node.Attributes["Description"] == null ? String.Empty : node.Attributes["Description"].Value;
mExportColumn.DataType = node.Attributes["Type"] == null ? String.Empty : node.Attributes["Type"].Value;
mExportColumn.ColumnNumber = node.Attributes["ColumnNumber"] == null ? 0 : Convert.ToInt32(node.Attributes["ColumnNumber"].Value);
mExportColumn.ColumnWidth = node.Attributes["ColumnWidth"] == null ? 100 : Convert.ToInt32(node.Attributes["ColumnWidth"].Value);
mExportColumn.Category = categoryTemp;
dicColumns[categoryTemp].Add(mExportColumn);
}
}
return(dicColumns);
}
/// <summary>
/// 将ListRow的数据导出到Excel(在客户端获取数据)
/// </summary>
/// <param name="columns">要导出的列集合(如果为空默认导出所有已配置的列)</param>
/// <param name="rows">要导出的数据集</param>
/// <param name="sheetName">要导出的工作簿名称</param>
/// <param name="fileName">导出的文件名称(包括路径)</param>
/// <returns></returns>
public virtual bool ExportListRowToExcel(List <ExportColumn> columns, List <ExportRow> rows, string sheetName, string fileName)
{
//获取要导出的列并排序
columns = GetExportColumns(columns);
if (columns == null || columns.Count <= 0)
{
return(false);
}
//创建生成Excel对象
HSSFWorkbook workbook = LoadWorkbook("");
ISheet sheet = workbook.CreateSheet(sheetName);
IRow row = null;
ICell cell = null;
int index = 0;
#region 标题行
//标题行
row = sheet.CreateRow(0);
//表头样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Indigo.Index;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//加粗
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(font);
index = 0;
foreach (ExportColumn col in columns)
{
cell = row.CreateCell(index);
cell.CellStyle = cellStyle;
cell.SetCellValue(col.Description);
//设置列宽
sheet.SetColumnWidth(index, col.ColumnWidth * 256);
index++;
}
#endregion
#region 数据行
ExportColumn dataColumn = null;
//数据行
for (int i = 0; i < rows.Count; i++)//所有数据行
{
row = sheet.CreateRow(i + 1);
index = 0;
dataColumn = null;
foreach (ExportColumn col in columns)//所有要导出的列
{
dataColumn = rows[i].Columns.Where(d => d.ColumnName == col.ColumnName).FirstOrDefault();
if (dataColumn != null)
{
row.CreateCell(index).SetCellValue(dataColumn.DataValue + "");
}
else
{
row.CreateCell(index).SetCellValue("");
}
index++;
}
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] bs = ms.ToArray();
fs.Write(bs, 0, bs.Length);
}
}
return(true);
}
