将数据写入excel [英] writing data into excel
本文介绍了将数据写入excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的任务是，在考勤机上，当我们刷卡时，我得到了卡ID，然后我需要将卡ID放在excel中，
首先我需要检查excel文件是否存在，如果不存在则创建它，如果存在则将该卡ID添加到下一行的excel中。有没有数据库是可能的。
我的要求是没有数据库，直接我需要写入excel。
之前我已开发as，首先保存到数据库中，然后我将数据从数据库中获取为excel，因为我使用了以下代码，但是如何直接写入excel.any建议.....
my task is, in attendance machine, when we swipe the card ,i got card id then that card id i need to place in excel,
first i need to check whether the excel file exists or not and if not create it, if exist add that card id into excel in next row . is it possible without database .
my requirement is no database, directly i need to write into excel.
previously i have developed as , first saved into database, then i get the data from database into excel for that i used following code , but how can i write directly into excel.any suggestions.....
try
{
using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
String query;
System.Data.DataTable dt = new System.Data.DataTable();
string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd");
if ((reportsmachine.Text == "") && (reportscustomer.Text == ""))
{
query = "select c.customer_id as 'Customer ID',c.father_name as 'Father Name',c.address as Address,c.phone_number as 'Phone Number',c.mobile_number as 'Mobile Number',c.id_proof as 'ID Proof',c.area as Area,c.ip_address as 'IP Address',c.mac_address as 'MAC Address',c.package_type as 'Package Type',c.name as Name,c.activation_date as 'Activation Date',c.status as Status,c.installation_cost as 'Installation Cost',c.totalamount_paid as 'Total Amount Paid',c.monthly_amount as 'Monthly Amount',c.lastpaid_date as 'Last Paid Date',c.lastpaid_amount as 'Last Paid Amount',c.nextpay_date as 'Next Pay Date',c.totaldue_amount as 'Total Due Amount',t.agent_id as 'Agent ID',t.token_number as 'Token Number',t.machine_id as 'Machine ID' from customer c INNER JOIN transaction t ON c.customer_id=t.customer_id WHERE DATE(t.paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' GROUP BY c.customer_id ";
da = new MySqlDataAdapter(query, con);
ds = new DataSet();
datasetvalue = da.Fill(ds);
datatablevalue = da.Fill(dt);
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet worksheet;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
try
{
worksheet.Cells[1, 1] = "Customer Name";
worksheet.Cells[1, 2] = "father Name";
worksheet.Cells[1, 3] = "Address";
worksheet.Cells[1, 4] = "Phone Number";
worksheet.Cells[1, 5] = "Mobile number";
worksheet.Cells[1, 6] = "ID Proof";
worksheet.Cells[1, 7] = "Area";
worksheet.Cells[1, 8] = "IP Address";
worksheet.Cells[1, 9] = "MAC Address";
worksheet.Cells[1, 10] = "Package Type";
worksheet.Cells[1, 11] = "User Name";
worksheet.Cells[1, 12] = "Activation Rate";
worksheet.Cells[1, 13] = "Status";
worksheet.Cells[1, 14] = "Installation Cost";
worksheet.Cells[1, 15] = "Total Amount Paid";
worksheet.Cells[1, 16] = "Monthly Amount";
worksheet.Cells[1, 17] = "Last Paid Date";
worksheet.Cells[1, 18] = "Last Paid Amount";
worksheet.Cells[1, 19] = "Next Payment Date";
worksheet.Cells[1, 20] = "Total Due Amount";
worksheet.Cells[1, 21] = "Agent ID";
worksheet.Cells[1, 22] = "Receipt Number";
worksheet.Cells[1, 23] = "Machine ID";
int row = 1;
for (int k = 1; k <= 23; k++)
{
worksheet.Cells[row, k].Interior.ColorIndex = 39;
}
string data = null;
int i = 0;
int j = 0;
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 2, j + 1]).Value2 = data;
}
}
excelApp.DisplayAlerts = false;
//excelApp.Visible = true;
//String fname =@"C:\Example.xls";
workbook.SaveAs(fname, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
excelApp.Quit();
releaseObject(worksheet);
releaseObject(workbook);
releaseObject(excelApp);
System.Diagnostics.Process.Start(fname);
}
catch (Exception p)
{
MessageBox.Show(p.StackTrace);
}
finally
{
if (excelApp != null)
releaseObject(excelApp);
if (workbook != null)
releaseObject(workbook);
if (worksheet != null)
releaseObject(worksheet);
}
推荐答案
你可以创建OLEDB连接 OLEDB连接 [ ^ ]到excel文件并作为数据库工作。有关更多信息，请查看以下代码项目文章
使用OLEDB读取和写入Excel文档 [ ^ ]
you can create OLEDB connection OLEDB connection [^] to your excel file and work as database. for more information check below code project article
Read and Write Excel Documents Using OLEDB[^]
这篇关于将数据写入excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
