将数据导出到多个Excel工作表

本文介绍了将数据导出到多个Excel工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 嗨 我使用带有c＃的asp.net。 我有两个数据表从sql数据库中获取一些数据。 现在我想将这些数据导出到excel表中两张。 任何人都可以帮助我吗？HiI 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(); } } } 请修改此代码并按预期进行操作。紧急。 THankxPlease modify this code and make it as i expect. Its urgent .THankx推荐答案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)? 这篇关于将数据导出到多个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！