创建Excel报告 [英] Create Excel Report

问题描述

大家好.我有2张桌子.一个由另一个填充.换句话说，电子邮件地址.另一个拥有包括电子邮件地址在内的所有信息.现在，我想根据第一个表中的电子邮件创建多个Excel报告.因此，我需要程序根据电子邮件的值循环遍历第一个表.将其与第二个表进行比较，并根据第二个表中存储的与程序忙的电子邮件地址有关的所有信息创建报告.如果你明白我的意思.

谁能帮我这个忙.

提前谢谢您.
:)

Good day all. I have 2 tables. One is filled by the other. In other words the e-mail addresses. And the other one has all the information including e-mail addresses. Now, i want to create multiple excel reports as per e-mail from the first table. So i need the program to loop through the first table and according to the value of the e-mail. Compare it to the second table and create a report according to all the information that is stored in the second table concerning the email address that the program is busy with. If you understand how i mean.

Could any one help me with this.

Thank you in advance.
:)

推荐答案

您到底被困在什么地方?
连接到数据库并检索与电子邮件关联的数据?
还是将数据导出到Excel?

满足这两种需求的文章很多.

只需在CP中搜索导出到Excel"，我确定您会
找到您所需要的.

或用于数据库访问". :)

What exactly is that you are stuck with?
Connecting to the db and retrieving data associated with an e-mail?
Or exporting the data to Excel?

There are plenty of articles for both needs.

Just search for "Export To Excel" inside CP, and I''m sure you''ll
find what you need.

Or for "Database Access". :)

尝试
{

String MyConString =服务器= south-srv01;数据库= jmds; Uid = root; Pwd = F1sh1ng;";

MySqlConnection sqlConn =新的MySqlConnection(MyConString);

MySqlCommand cmd = sqlConn.CreateCommand();
MySqlCommand cmd1 = sqlConn.CreateCommand();

DataSet ds = new DataSet();
DataTable dt = new DataTable();

cmd.CommandText ="SELECT * FROM jmds.email";
sqlConn.Open();

MySqlDataAdapter adptr =新的MySqlDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);



int rowCount = 1;

foreach(dt.Rows中的DataRow行)
{
rowCount + = 1;
//获取数据
var1 =(string)row ["DBEmail"];
//var1 =(string)row ["DBEmail"];

Console.WriteLine(var1);

Excel.Application objApp;
Excel._Workbook objBook;

Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;

objApp =新的Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet =(Excel._Worksheet)objSheets.get_Item(1);

范围= objSheet.get_Range("A1"，Missing.Value);

objApp.Cells.Replace(< br/>"，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace(</a>"，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace(< a href ="，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace("target ="，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace("_ blank"，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace("http://www.south-com.co.za"，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Cells.Replace(>& nbsp;"，"，Excel.XlLookAt.xlPart，Excel.XlSearchOrder.xlByRows，true，System.Type.Missing，false，false);
objApp.Visible = true;
cmd1.CommandText ="SELECT * FROM hesk_combined WHERE email =" + var1 +";
adptr.SelectCommand = cmd1;
adptr.Fill(dt);

for(int i = 1; i< dt.Columns.Count +1; i ++)
{

//第一次通过
添加标头 如果(rowCount == 2)
{
objSheet.Cells [1，i] = dt.Columns [i-1] .ColumnName;

}
objSheet.Cells [rowCount，i] = var1 [i-1] .ToString();

}
objBook.SaveCopyAs(@"C:\ Mica_Report \ LorenzoReport.xls");
}

sqlConn.Close();

}

catch(异常e)
{
Console.WriteLine(错误" + e);
}


}

try
{

String MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";

MySqlConnection sqlConn = new MySqlConnection(MyConString);

MySqlCommand cmd = sqlConn.CreateCommand();
MySqlCommand cmd1 = sqlConn.CreateCommand();

DataSet ds = new DataSet();
DataTable dt = new DataTable();

cmd.CommandText = "SELECT * FROM jmds.email";
sqlConn.Open();

MySqlDataAdapter adptr = new MySqlDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);



int rowCount = 1;

foreach (DataRow row in dt.Rows)
{
rowCount += 1;
// get the data
var1 = (string)row["DBEmail"];
//var1 = (string)row["DBEmail"];

Console.WriteLine(var1);

Excel.Application objApp;
Excel._Workbook objBook;

Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;

objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);

range = objSheet.get_Range("A1", Missing.Value);

objApp.Cells.Replace("<br />", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace("</a>", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace("<a href=", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace("target=", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace("_blank", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace("http://www.south-com.co.za", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Cells.Replace(">&nbsp;", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
objApp.Visible = true;
cmd1.CommandText = "SELECT * FROM hesk_combined WHERE email = " + var1 + " ";
adptr.SelectCommand = cmd1;
adptr.Fill(dt);

for (int i = 1; i < dt.Columns.Count + 1; i++)
{

// Add the header the first time through
if (rowCount == 2)
{
objSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;

}
objSheet.Cells[rowCount, i] = var1[i - 1].ToString();

}
objBook.SaveCopyAs(@"C:\Mica_Report\LorenzoReport.xls");
}

sqlConn.Close();

}

catch (Exception e)
{
Console.WriteLine("Error " + e);
}


}

好吧，我很想根据电子邮件读取数据.
我的程序制作了excel工作表.
但是里面没有数据.
我已经发布了实际使用的代码.
如果您愿意，我可以发布整个解决方案的代码?

谢谢.

Well, i am stuck with reading the data according to the email.
My program makes the excel worksheet.
But there is no data in it.
I have posted the actual code i am stuck with.
If you want i can post the entire solution''s code?

Thank you.

这篇关于创建Excel报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！