从mysql数据库创建Excel电子表格 [英] Create Excel Spreadsheet from mysql db
本文介绍了从mysql数据库创建Excel电子表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好.我仍然有完全相同的问题.好的，我有一个充满各种数据的表.包括电子邮件.此数据包含提交给我们公司的有关现场问题的故障单.我现在的工作是根据数据库中的电子邮件创建电子表格.
因此，我创建了一个单独的表，仅用于读取所有电子邮件地址.有了包含独特但又现在存在的问题的语句，现在的问题是为数据库中的每个电子邮件地址创建一个工作簿，并用与所选电子邮件相关的所有数据填充电子表格.
我使用数据集从数据库中获取数据.和一个MySQL适配器来填充数据.然后，我使用了一个foreach语句来遍历数据条目，但是我仍然被卡住.
有人可以帮助我吗?拜托?
预先谢谢您
Good day all. I still have the exact same problem. OK, I have a table that is filled with all sorts of data. Including email. This data consists of tickets that are submitted to our company about problems on site. My job now is to create spreadsheets according to the email in the database.
So I have created a separate table just to read in all the email addresses. With that statement that includes distinct but the problem now is to create a workbook for every email address in the database and to fill the spreadsheet with all data that is connected to the email that is selected.
I used a dataset to get the data from the database. And a MySQL adapter to fill the data with. Then I used a foreach statement to iterate through the data entries but I am still stuck.
Could any one assist me? Please?
Thank you in advance
推荐答案
好，我将粘贴我的整个代码-----
OK, i will paste my entire code-----
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace WorkingWithSQL_EXCEL
{
class Program
{
static string var1;
static void Main(string[] args)
{
try
{
if (Directory.Exists("C:\\Mica_Report"))
Console.WriteLine("C:\\Mica_Report already exists");
else
Directory.CreateDirectory("C:\\Mica_Report");
Console.WriteLine("1. Create Mica_Report if directory if it does not exist");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
//--------------------------------------------------------------------------------------------------------------------------------------//
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
//String cmdText = "DROP PROCEDURE IF EXISTS `jmds`.`hesk_tickets`";
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "DROP TABLE IF EXISTS `jmds`.`hesk_combined`";
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
//table exists..hence drop it..
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("1. Checking if table exist, if it does. Then delete it");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
//--------------------------------------------------------------------------------------------------------------------------------------//
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "CREATE TABLE jmds.hesk_combined LIKE jmds.hesk_tickets";//
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
//table exists..hence drop it..
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("2. Create hesk_combined table (Still empty)");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
// Put data into the new table
//--------------------------------------------------------------------------------------------------------------------------------------//
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "INSERT INTO jmds.hesk_combined SELECT * FROM jmds.hesk_tickets";
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//table exists..hence drop it..
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("3. Insert hesk_tickets data into new table");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
//--------------------------------------------------------------------------------------------------------------------------------------//
try
{
string MyConString2 = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString2);
MySqlCommand cmd2 = sqlConn.CreateCommand();
cmd2.CommandText = "UPDATE hesk_combined JOIN hesk_replies ON hesk_combined.id = hesk_replies.id SET hesk_combined.message = hesk_replies.message, hesk_combined.id =hesk_replies.id, hesk_combined.name =hesk_replies.name ;";
sqlConn.Open();
MySqlDataReader reader2 = cmd2.ExecuteReader();
while (reader2.Read())
{
//table exists..hence drop it..
cmd2.BeginExecuteReader();
}
sqlConn.Close();
reader2.Close();
Console.WriteLine("4. Update hesk_combined table with hesk_replies table data");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
//--------------------------------------------------------------------------------------------------------------------------------------//
//////////try
//////////{
////////// DataSet ds = new DataSet("New_DataSet");
////////// DataTable dt = new DataTable("New_DataTable");
////////// ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
////////// dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
////////// string MyConString3 = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
////////// MySqlConnection sqlConn3 = new MySqlConnection(MyConString3);
////////// MySqlCommand command3 = sqlConn3.CreateCommand();
////////// command3.CommandText = "SELECT * FROM `jmds`.`hesk_combined` order by id";
////////// sqlConn3.Open();
////////// MySqlDataAdapter adptr = new MySqlDataAdapter();
////////// adptr.SelectCommand = command3;
////////// adptr.Fill(dt);
////////// sqlConn3.Close();
////////// //Add the table to the data set
////////// ds.Tables.Add(dt);
////////// Excel.Application oXL;
////////// Excel.Workbook oWB;
////////// Excel.Worksheet oSheet;
////////// Excel.Range oRange;
////////// // Start Excel and get Application object.
////////// oXL = new Excel.Application();
////////// // Set some properties
////////// //oXL.Visible = true;
////////// oXL.DisplayAlerts = false;
////////// // Get a new workbook.
////////// oWB = oXL.Workbooks.Add(Missing.Value);
////////// // Get the active sheet
////////// oSheet = (Excel.Worksheet)oWB.ActiveSheet;
////////// oSheet.Name = "Mica_Ticket_Submit";
////////// sqlConn3.Open();
////////// int rowCount = 1;
////////// foreach (DataRow dr in dt.Rows)
////////// {
////////// rowCount += 1;
////////// for (int i = 1; i < dt.Columns.Count + 1; i++)
////////// {
////////// // Add the header the first time through
////////// if (rowCount == 2)
////////// {
////////// oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
////////// }
////////// oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
////////// }
////////// }
////////// sqlConn3.Close();
////////// // Resize the columns
////////// oRange = oSheet.get_Range(oSheet.Cells[1, 1],
////////// oSheet.Cells[rowCount, dt.Columns.Count]);
////////// oRange.EntireColumn.AutoFit();
////////// oRange.WrapText = true;
////////// // Find a word and replace it
////////// oXL.Cells.Replace("<br />", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
////////// oXL.Cells.Replace("", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
////////// oXL.Cells.Replace(" ////////// oXL.Cells.Replace("> ", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
////////// // Save the sheet and close
////////// oSheet = null;
////////// oRange = null;
////////// oWB.SaveCopyAs(@"C:\Mica_Report\OverAllReport.xls");
////////// //oWB.SaveAs("ExerciseNew.xls", Excel.XlFileFormat.xlWorkbookNormal,
////////// // Missing.Value, Missing.Value, Missing.Value, Missing.Value,
////////// // Excel.XlSaveAsAccessMode.xlExclusive,
////////// // Missing.Value, Missing.Value, Missing.Value,
////////// // Missing.Value, Missing.Value);
////////// oWB.Close(Missing.Value, Missing.Value, Missing.Value);
////////// oWB = null;
////////// oXL.Quit();
////////// // Clean up
////////// // NOTE: When in release mode, this does the trick
////////// GC.WaitForPendingFinalizers();
////////// GC.Collect();
////////// GC.WaitForPendingFinalizers();
////////// GC.Collect();
////////// sqlConn3.Close();
//////////}
//////////catch (Exception e)
//////////{
////////// Console.WriteLine("Error " + e);
//////////}
//////////Console.WriteLine("5. Create EXCEL spreadsheet in documents folder .");
//--------------------------------------------------------------------------------------------------------------------------------------//
//--------------------------------------------------------------------------------------------------------------------------------------//
//--------------------------------------------------------------------------------------------------------------------------------------//
// NAREEN's REPORT
try
{
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
string MyConString3 = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn3 = new MySqlConnection(MyConString3);
//MySqlCommand command3 = sqlConn3.CreateCommand();
MySqlCommand command = new MySqlCommand("select * from jmds.hesk_combined where email = @email", sqlConn3);
MySqlParameter param = new MySqlParameter();
param.ParameterName = "@email";
param.Value = (var1);
command.Parameters.Add(param);
sqlConn3.Open();
MySqlDataAdapter adptr = new MySqlDataAdapter();
adptr.SelectCommand = command;
adptr.Fill(dt);
//Add the table to the data set
ds.Tables.Add(dt);
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
//oXL.Visible = true;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
var1 = (string)dr["DBEmail"];
oSheet.Name = var1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
oRange.WrapText = true;
// Find a word and replace it
oXL.Cells.Replace("<br />", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oXL.Cells.Replace("", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oXL.Cells.Replace(" oXL.Cells.Replace("> ", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oWB.SaveCopyAs(@"C:\Mica_Report\new.xls");
// Save the sheet and close
oSheet = null;
oRange = null;
//oWB.SaveAs("NareenReport.xls", Excel.XlFileFormat.xlWorkbookNormal,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
//oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
Console.WriteLine("Reports done in Process");
}
}
}
这篇关于从mysql数据库创建Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
