using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Sample
{
public partial class Form1 : Form
{
private readonly String[] Headers = { "姓名", "座號", "國文成績" };
private readonly String[] Users = { "王小明", "陳大吉", "洪船長" };
private readonly int[] CH_Score = { 57, 80, 92 };
private readonly int MAX = 3;
private DataTable table = new DataTable();
public Form1()
{
InitializeComponent();
table.Columns.Add("Header");
table.Columns.Add("User");
table.Columns.Add("Score");
}
private void button1_Click(object sender, EventArgs e)
{
for (int index = 0; index < MAX; index++)
{
DataRow row = table.NewRow();
row["Header"] = Headers[index];
row["User"] = Users[index];
row["Score"] = CH_Score[index];
table.Rows.Add(row);
}
DataTableToCSV(table);
}
private void DataTableToCSV(DataTable table)
{
Excel.Application excel = new Excel.Application();
try
{
if (excel == null)
throw new Exception();
//不顯示Excel
excel.Visible = false;
//不顯示Excel錯誤
excel.DisplayAlerts = false;
Excel._Workbook workbook = excel.Workbooks.Add();
//Excel已1為開頭
Excel._Worksheet sheet = (Excel._Worksheet)workbook.Worksheets[1];
int point = 0;
foreach (var row in table.AsEnumerable())
{
int maxLength = row.ItemArray.Length;
for (int index = 0; index < maxLength; index++)
sheet.Cells[point + 1, index + 1] = row.ItemArray[index];
point++;
}
workbook.SaveAs(String.Format("{0}\\{1}.xls", Directory.GetCurrentDirectory(), DateTime.Now.Ticks),
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
workbook.Close(false, Type.Missing, Type.Missing);
workbook = null;
sheet = null;
excel = null;
}
catch (Exception ex)
{
MessageBox.Show("無法將資料寫入Excel");
}
}
}
}
執行結果:
