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

转载于:https://www.cnblogs.com/qiuzhongyang/p/3874088.html