参见文章 -



如何将sql中的2个数据表与第1列的自动升级合并（类似于注册ID） [ ^ ]





和





导出到多个工作表 [ ^ ]

see article-

how to merge 2 datatable in sql with auto upgradation of 1st column(which is like enrollment id)[^]


and


Export to multiple sheet[^]

您好，



我建议您使用打开XML来创建Excel，你可以使用下面的代码来创建相同的。 Open XML是创建Excel最干净的方法。使用从Response.OutputStream中的CreateExcel返回的Mermory流进行下载。

Hi,

I will suggest you to use open XML to create Excel, you can use the below code to create the same. Open XML is the cleanest way to create Excel. use the Mermory stream that is returned from CreateExcel in Response.OutputStream to download.

string filename = "fileName_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx";
                       //Create a Instance of Class which have below methods
                       //pass Dataset to Create Excel 
                        ms = excel.CreateExcel(ds);
                        ms.WriteTo(Response.OutputStream);
                        Response.Clear();
                        Response.ContentType = "application/force-download";
                        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));
                        Response.BinaryWrite(ms.ToArray());
                        Response.End();

课堂上的Excel操作。

In class for Excel Operations.

 public MemoryStream CreateExcel(DataSet dset)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument Excelfile = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                var workbookpart = Excelfile.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                workbookpart.AddNewPart<sharedstringtablepart>();
                var stringTablePart = workbookpart.GetPartsOfType<sharedstringtablepart>().FirstOrDefault();
                var stringTable = new SharedStringTable();
                stringTablePart.SharedStringTable = stringTable;


                foreach (DataTable dt in dset.Tables)
                {
                    CreateNewExcelSheet(dt, dt.TableName, workbookpart, true);
                }
                workbookpart.Workbook.Save();
            }
            return ms;
        }
 protected void CreateNewExcelSheet(DataTable table, string sheetName, WorkbookPart workbk, bool addHeader)
        {
            var stringTable = workbk.GetPartsOfType<sharedstringtablepart>().FirstOrDefault();
            WorksheetPart sheetPart = workbk.AddNewPart<worksheetpart>();
            sheetPart.Worksheet = new Worksheet(new SheetData());
            var sheets = workbk.Workbook.GetFirstChild<sheets>();
            if (sheets == null)
            {
                sheets = workbk.Workbook.AppendChild(new Sheets());
            }

            string relationshipId = workbk.GetIdOfPart(sheetPart);
            var sheetData = sheetPart.Worksheet.GetFirstChild<sheetdata>();
            //ID for Sheet
            uint sheetId = 1;
            if (sheets != null)
            {
                if (sheets.Elements<sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
            }
            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            uint rowIndex = 0;
            //creates new Row
            var row = new Row { RowIndex = ++rowIndex };
            //Adds Header Row
            if (addHeader)
                AddColumnHeaderRow(table, row, sheetData, stringTable);
            NumberItem nc = new NumberItem();

            foreach (DataRow dr in table.Rows)
            {
                row = new Row { RowIndex = ++rowIndex };
                for (int i = 0; i < dr.ItemArray.Count(); i++)
                {
                    row.AppendChild(GetCellValue(dr[i].ToString(), stringTable.SharedStringTable));
                }
                sheetData.AppendChild(row);

            }
            sheetPart.Worksheet.Save();

        }

   protected Cell GetCellValue(string value, SharedStringTable stringTable)
        {
            Regex decreg = new Regex("^[0-9]([.][0-9]{1,20)?

，RegexOptions.None）;
decimal dummy;
int dummyint;
DateTime dummydt;
var cell = new Cell（）;

if （ decimal .TryParse（ value ， out dummy））
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue（ value ）;
}
else if （DateTime.TryParse（ value ， out dummydt））
{
cell.DataType = CellValues.SharedString;
var item = stringTable.AppendChild（ new SharedStringItem（ new 文本（ value ）））;
cell.CellValue = new CellValue（item.ElementsBefore（）。Count（）。ToString（））;

}
else if （ int .TryParse（ value ， out dummyint））
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue（ value ）;
}
else if （regexName.IsMatch（ value ））
{
cell.DataType = CellValues.SharedString;
var item = stringTable.AppendChild（ new SharedStringItem（ new 文本（ value ）））;
cell.CellValue = new CellValue（item.ElementsBefore（）。Count（）。ToString（））;
}

return cell;


}
受保护 静态 void AddColumnHeaderRow（DataTable table，Row row，SheetData sheetData，SharedStringTablePart stringTable）
{
for （ var i = 0 ; i < table.Columns.Count; i ++）
{

var cell = new Cell {DataType = CellValues.SharedString};
var item = stringTable.SharedStringTable.AppendChild（ new SharedStringItem（ new Text（table.Columns [i] .ColumnName）））;
cell.CellValue = new CellValue（item.ElementsBefore（）。Count（）。ToString（））;
row.AppendChild（cell）;
}

// 将新行附加到工作表数据
sheetData.AppendChild（row）;
}
< / 表格 > < / 表格 &gt ; < / sheetdata > < / 表格 > < / worksheetpart > < / sharedstringtablepart > < / sharedstringtablepart > < / sharedstr ingtablepart >

", RegexOptions.None); decimal dummy; int dummyint; DateTime dummydt; var cell = new Cell(); if (decimal.TryParse(value, out dummy)) { cell.DataType = CellValues.Number; cell.CellValue = new CellValue(value); } else if (DateTime.TryParse(value, out dummydt)) { cell.DataType = CellValues.SharedString; var item = stringTable.AppendChild(new SharedStringItem(new Text(value))); cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString()); } else if (int.TryParse(value, out dummyint)) { cell.DataType = CellValues.Number; cell.CellValue = new CellValue(value); } else if (regexName.IsMatch(value)) { cell.DataType = CellValues.SharedString; var item = stringTable.AppendChild(new SharedStringItem(new Text(value))); cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString()); } return cell; } protected static void AddColumnHeaderRow(DataTable table, Row row, SheetData sheetData, SharedStringTablePart stringTable) { for (var i = 0; i < table.Columns.Count; i++) { var cell = new Cell { DataType = CellValues.SharedString }; var item = stringTable.SharedStringTable.AppendChild(new SharedStringItem(new Text(table.Columns[i].ColumnName))); cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString()); row.AppendChild(cell); } //Append new row to the sheet data sheetData.AppendChild(row); } </sheet></sheet></sheetdata></sheets></worksheetpart></sharedstringtablepart></sharedstringtablepart></sharedstringtablepart>