如何将多个数据表添加到工作簿单页 [英] How to add multiple datatables to work book single sheet
本文介绍了如何将多个数据表添加到工作簿单页的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
SHIFT HANK PROD UPTOPROD亏空ENERGY
SHIFT 1 434.106 1761.373 13020.896 229.627 3755.72
SHIFT 2 444.589 1804.415 13685.928 136.785 3815.104
SHIFT 3 347.878 1407.073 11670.846 191.327 2977.936
SHIFT HANK PROD UPTOPROD亏空ENERGY
SHIFT 1个434.106 1761.373 13020.896 3755.72
SHIFT 2 444.589 1804.415 13685.928 3815.104
SHIFT 3 347.878 1407.073 11670.846 2977.936
SHIFT HANK PROD UPTOPROD亏空ENERGY
SHIFT 1个434.106 1761.372 13020.896 3755.72
SHIFT 2 444.589 1804.415 3815.104 13685.928
SHIFT 3 347.878 1407.073 2977.936 11670.846
这里显示3个数据表数据，我想要单个表格中的3个数据表数据
我尝试过：
< pre> using（wb = new XLWorkbook（））
{
wb.Worksheets.Add（ds_temp_table）;
using（MemoryStream memoryStream = new MemoryStream（））
{
var excelApp = new Microsoft.Office.Interop.Excel.Application（）;
excelApp.Visible = true;
excelApp.Workbooks.Add（）;
Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
workSheet.Cells [1,1] =+ ds_temp_table.DataSetName;
workSheet.Cells [1,1] .Style.Font.Bold = true;
workSheet.Cells [2,1] .Value =客户名称：+ MillName;
workSheet.Cells [2,1] .Style.Font.Bold = true;
workSheet.Cells [3,1] .Value =Shift：+ shift;
workSheet.Cells [3,1] .Style.Font.Bold = true;
if（type == ReportType25）
{
workSheet.Cells [4,1] .Value =Date：+ sPresentStarttime.ToString（dd-MM-yyyy）;
}
else
{
workSheet.Cells [4,1] .Value =Date：+ DateTime.Now.ToString（dd-MM-yyyy）; //sPresentStarttime.ToString(\"dd-MM-yyyy）;
}
workSheet.Cells [4,1] .Style.Font.Bold = true;
int z = 5;
for（int x = 0; x< ds_temp_table.Tables.Count; x ++）
{
for（int i = 0; i< ds_temp_table.Tables [x ] .Columns.Count; i ++）
{
string name = ds_temp_table.Tables [x] .Columns [i] .Caption;
workSheet.Cells [z，i + 1] = ds_temp_table.Tables [x] .Columns [i] .Caption;
}
z ++;
for（int i = 0; i< ds_temp_table.Tables [x] .Rows.Count; i ++）
{
for（int j = 0; j< ds_temp_table.Tables [ x] .Columns.Count; j ++）
{
workSheet.Cells [z，j + 1] = ds_temp_table.Tables [x] .Rows [i] [j] .ToString（）;
}
z ++;
}
// wb.Worksheets.Add（ds_temp_table.Tables [x]）;
}
wb.SaveAs（memoryStream）;
byte [] bytes = memoryStream.ToArray（）;
memoryStream.Position = 0;
memoryStream.Close（）;
if（CC_1！= string.Empty）
{
_obj_CC_list.Add（CC_1）;
}
if（CC_2！= string.Empty）
{
_obj_CC_list.Add（CC_2）;
}
string User_name = _dt_Email_Setting.Rows [0] [User_name]。ToString（）;
string Password = _dt_Email_Setting.Rows [0] [Password]。ToString（）;
string Host_Name = _dt_Email_Setting.Rows [0] [Host_Name]。ToString（）;
string Sender = _dt_Email_Setting.Rows [0] [Sender]。ToString（）;
int Port_No = Convert.ToInt16（_dt_Email_Setting.Rows [0] [Port_No]）;
MailMessage MailMessage = new MailMessage（）;
MailAddress fromAddress = new MailAddress（Sender）;
MailMessage.From = fromAddress;
foreach（_obj_To_list中的字符串电子邮件）
{
MailMessage.To.Add（email）;
}
if（type == ReportType25）
{
MailMessage.Subject = ds_temp_table.DataSetName +_ Report;
}
else
{
MailMessage.Subject = dt_report.DataSetName +_ Report;
}
string Body_Details =+ MillName +< / b>+ Environment.NewLine;
// Body_Details + =+ Dept_Name +，+ Environment.NewLine;
Body_Details + =+ shift +。 + Environment.NewLine;
MailMessage.Body = Body_Details;
foreach（_obj_CC_list中的字符串_CC_list）
{
MailMessage.CC.Add（_CC_list）;
}
MailMessage.Attachments.Add（新附件（新的MemoryStream（字节），sPresentStarttime.ToString（dd-MM-yyyy）+_+ ds_temp_table.DataSetName + _+ shift +。xlsx，application / vnd.openxmlformats-officedocument.spreadsheetml.sheet））;
MailMessage.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient（）;
smtp.Host = Host_Name;
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential（）;
credentials.UserName = User_name;
credentials.Password =密码;
smtp.UseDefaultCredentials = true;
smtp.Credentials =凭证;
smtp.Port = Port_No;
ServicePointManager.ServerCertificateValidationCallback = delegate（对象s，X509Certificate证书，X509Chain链，SslPolicyErrors sslPolicyErrors）
{return true; };
smtp.Send（MailMes
}}
解决方案
尝试使用此
NuGet Gallery | ClosedXML 0.93.1 [ ^ ]
SHIFT HANK PROD UPTOPROD SHORTFALL ENERGY SHIFT 1 434.106 1761.373 13020.896 229.627 3755.72 SHIFT 2 444.589 1804.415 13685.928 136.785 3815.104 SHIFT 3 347.878 1407.073 11670.846 191.327 2977.936 SHIFT HANK PROD UPTOPROD SHORTFALL ENERGY SHIFT 1 434.106 1761.373 13020.896 3755.72 SHIFT 2 444.589 1804.415 13685.928 3815.104 SHIFT 3 347.878 1407.073 11670.846 2977.936 SHIFT HANK PROD UPTOPROD SHORTFALL ENERGY SHIFT 1 434.106 1761.372 13020.896 3755.72 SHIFT 2 444.589 1804.415 13685.928 3815.104 SHIFT 3 347.878 1407.073 11670.846 2977.936
here 3 datatable data displaying, i want above 3 datatable data in Single sheet
What I have tried:
<pre>using (wb = new XLWorkbook())
{
wb.Worksheets.Add(ds_temp_table);
using (MemoryStream memoryStream = new MemoryStream())
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
workSheet.Cells[1, 1] = " " + ds_temp_table.DataSetName;
workSheet.Cells[1, 1].Style.Font.Bold = true;
workSheet.Cells[2, 1].Value = "Customer Name : " + MillName;
workSheet.Cells[2, 1].Style.Font.Bold = true;
workSheet.Cells[3, 1].Value = "Shift : " + shift;
workSheet.Cells[3, 1].Style.Font.Bold = true;
if (type == ReportType25)
{
workSheet.Cells[4, 1].Value = "Date : " + sPresentStarttime.ToString("dd-MM-yyyy");
}
else
{
workSheet.Cells[4, 1].Value = "Date : " + DateTime.Now.ToString("dd-MM-yyyy"); //sPresentStarttime.ToString("dd-MM-yyyy");
}
workSheet.Cells[4, 1].Style.Font.Bold = true;
int z = 5;
for (int x = 0; x < ds_temp_table.Tables.Count; x++)
{
for (int i = 0; i < ds_temp_table.Tables[x].Columns.Count; i++)
{
string name = ds_temp_table.Tables[x].Columns[i].Caption;
workSheet.Cells[z, i + 1] = ds_temp_table.Tables[x].Columns[i].Caption;
}
z++;
for (int i = 0; i < ds_temp_table.Tables[x].Rows.Count; i++)
{
for (int j = 0; j < ds_temp_table.Tables[x].Columns.Count; j++)
{
workSheet.Cells[z, j + 1] = ds_temp_table.Tables[x].Rows[i][j].ToString();
}
z++;
}
// wb.Worksheets.Add(ds_temp_table.Tables[x]);
}
wb.SaveAs(memoryStream);
byte[] bytes = memoryStream.ToArray();
memoryStream.Position = 0;
memoryStream.Close();
if (CC_1 != string.Empty)
{
_obj_CC_list.Add(CC_1);
}
if (CC_2 != string.Empty)
{
_obj_CC_list.Add(CC_2);
}
string User_name = _dt_Email_Setting.Rows[0]["User_name"].ToString();
string Password = _dt_Email_Setting.Rows[0]["Password"].ToString();
string Host_Name = _dt_Email_Setting.Rows[0]["Host_Name"].ToString();
string Sender = _dt_Email_Setting.Rows[0]["Sender"].ToString();
int Port_No = Convert.ToInt16(_dt_Email_Setting.Rows[0]["Port_No"]);
MailMessage MailMessage = new MailMessage();
MailAddress fromAddress = new MailAddress(Sender);
MailMessage.From = fromAddress;
foreach (string email in _obj_To_list)
{
MailMessage.To.Add(email);
}
if (type == ReportType25)
{
MailMessage.Subject = ds_temp_table.DataSetName + "_Report";
}
else
{
MailMessage.Subject = dt_report.DataSetName + "_Report";
}
string Body_Details = "" + MillName + " </ b > " + Environment.NewLine;
//Body_Details += " " + Dept_Name + "," + Environment.NewLine;
Body_Details += " " + shift + "." + Environment.NewLine;
MailMessage.Body = Body_Details;
foreach (string _CC_list in _obj_CC_list)
{
MailMessage.CC.Add(_CC_list);
}
MailMessage.Attachments.Add(new Attachment(new MemoryStream(bytes), sPresentStarttime.ToString("dd-MM-yyyy") + "_" + ds_temp_table.DataSetName + "_" + shift + ".xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
MailMessage.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = Host_Name;
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = User_name;
credentials.Password = Password;
smtp.UseDefaultCredentials = true;
smtp.Credentials = credentials;
smtp.Port = Port_No;
ServicePointManager.ServerCertificateValidationCallback = delegate (object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{ return true; };
smtp.Send(MailMes
}}
解决方案
try using this
NuGet Gallery | ClosedXML 0.93.1[^]
这篇关于如何将多个数据表添加到工作簿单页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
