Aspose.cell处理Excel
（一）从数据库中读取数据写入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");
??????????? }????????
??????? }
??? }
}
