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");
            }        
        }
    }
}