/// <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); }