将数据导出到多个Excel工作表 [英] Export data to multiple excel sheets

问题描述

嗨

我使用带有c＃的asp.net。

我有两个数据表从sql数据库中获取一些数据。

现在我想将这些数据导出到excel表中两张。

任何人都可以帮助我吗？

Hi
I m using asp.net with c#.
I have two data tables which fetch some data from sql data base.
Now I want to export these data to excel sheet in two sheets.
Can any one help me ?

protected void btn_download_Click(object sender, EventArgs e)
    {
        from = (fromCalendar.SelectedDate);
        to = (ToCalendar.SelectedDate);
        client_id = Convert.ToInt32(drp_Client.SelectedValue);
        Compaign = drp_Compaign.SelectedValue;

            try
            {

                SqlCommand cmd_down_tw = new SqlCommand("proc_GetSemDataFromToDate", con);
                cmd_down_tw.CommandType = CommandType.StoredProcedure;

                cmd_down_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
                cmd_down_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
                cmd_down_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
                cmd_down_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

                SqlDataAdapter da_down_tw = new SqlDataAdapter(cmd_down_tw);
                da_down_tw.SelectCommand = cmd_down_tw;
                DataTable dt_down_tw = new DataTable();
                da_down_tw.Fill(dt_down_tw);

                SqlCommand cmd_comp_tw = new SqlCommand("proc_GetAllSemDataFromToDate", con);
                cmd_comp_tw.CommandType = CommandType.StoredProcedure;

                cmd_comp_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
                cmd_comp_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
                cmd_comp_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
                cmd_comp_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

                da_comp_tw = new SqlDataAdapter(cmd_comp_tw);
                da_comp_tw.SelectCommand = cmd_comp_tw;
                dt_comp_tw = new DataTable();
                da_comp_tw.Fill(dt_comp_tw);

                dt_down_tw.Merge(dt_comp_tw);

                ExportDataSetToExcel(dt_down_tw, "Report.xls");
                dt_down_tw.Dispose();
            }
            catch
            {

            }
    }

    public void ExportDataSetToExcel(DataTable dt, string filename)
    {
        HttpResponse response = HttpContext.Current.Response;

        // first let's clean up the response.object   
        response.Clear();
        response.Charset = "";

        // set the response mime type for excel   
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        // create a string writer   
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid   
                DataGrid dg = new DataGrid();
                dg.DataSource = dt;

                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }
    }

请修改此代码并按预期进行操作。紧急。

THankx

Please modify this code and make it as i expect. Its urgent .
THankx

推荐答案

参见文章 -



如何将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）;
}
< / 表格 > < / 表格 > ; < / sheetdata > < / 表格 > < / worksheetpart > < span class =code-keyword>< / 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>

这篇关于将数据导出到多个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！