Aspose.cell處理Excel

標籤：os   資料   io   for   cti   代碼   ar   line   

（一）從資料庫中讀取資料寫入Excel中

方法1；

步驟：1、建立一個新的項目，引用動態連結程式庫Aspose.dll

2、見下面的原代碼

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Data.SqlClient;
namespace FromDBToExcel
{
class Program
{
static void Main(string[] args)
{
String connText = "server=localhost;database=mydb;uid=sa;password=123456";
try
{
using (SqlConnection conn = new SqlConnection(connText))
{
conn.Open();
SqlCommand command = new SqlCommand("select * from t_user", conn);
SqlDataReader data = command.ExecuteReader();
//從資料庫中讀出並列印在控制台上
int cnt = 0;
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cell = sheet.Cells;
cell[cnt, 0].PutValue("編號");
cell[cnt, 1].PutValue("姓名");
cell[cnt, 2].PutValue("年齡");
while (data.Read())
{
cnt++;
//Console.WriteLine(String.Format("{0}, {1},{2}",
// data[0], data[1], data[2]));
cell[cnt, 0].PutValue(data[0]);
cell[cnt, 1].PutValue(data[1]);
cell[cnt, 2].PutValue(data[2]);
}
// sheet.AutoFitColumns();
book.Save("student.xlsx");
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}
}
}
註：資料庫：表名t_user; 表中的列為id ,name,age;

程式執行完以後就會在項目的Debug中出現結果表

 

方法2：

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
namespace AsposeDemo
{
class Program
{
static void Main(string[] args)
{ 
//從資料庫中讀取資料寫到excel中
SqlCommand command = null;
DataSet dataset = null;
try
{
using (SqlConnection conn = new SqlConnection("server=localhost;database=mydb;uid=sa;password=123456"))
{
conn.Open();
command = new SqlCommand("select * from t_student", conn);
dataset = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dataset);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message); ;
}

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Cells acells = sheet.Cells;
int cnt = 0;
acells[cnt, 0].PutValue("編號");
acells[cnt,1].PutValue("姓名");
acells[cnt,2].PutValue("郵件");
acells[cnt,3].PutValue("電話");
acells[cnt,4].PutValue("年齡");
if (dataset!=null)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
cnt++;
acells[cnt, 0].PutValue(row["id"]);
acells[cnt, 1].PutValue(row["name"]);
acells[cnt, 2].PutValue(row["email"]);
acells[cnt, 3].PutValue(row["phone"]);
acells[cnt, 4].PutValue(row["age"]);
}
sheet.AutoFitColumns();
wb.Save("C:\\Documents and Settings\\Administrator\\案頭\\student.xlsx");
} 
}
}
}