Aspose.Cells

#region 导入数据到变量dt
String AsposeLicPath = String.Empty;
if (System.Configuration.ConfigurationManager.AppSettings["AsposeLicPath"] == null)
throw new Exception("Please setup [AsposeLicPath] in Web.Config!");
else
AsposeLicPath = System.Configuration.ConfigurationManager.AppSettings["AsposeLicPath"];

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(AsposeLicPath);
//打开文件，参数可以是文件的路径，也可以直接传入一个文件流
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fileStream);
//获取sheet表
Aspose.Cells.WorksheetCollection worksheets = workbook.Worksheets;
Aspose.Cells.Worksheet worksheet = null;
Aspose.Cells.Cells cell = null;

//默认第一行为列名 所以第一行不读，索引从第二行开始
int rowIndex = 0;
//从第一列读取
int colIndex = 0;
worksheet = worksheets[0];
//获取每个sheet表的所有单元格
cell = worksheet.Cells;
for (int i = 0; i < cell.MaxDataColumn + 1; i++)
{
if (i < 2)
dt.Columns.Add(new DataColumn(cell[0, i].Value.ToString(), typeof(string)));
else
dt.Columns.Add(new DataColumn(cell[0, i].Value.ToString(), typeof(int)));
}
cell.ExportDataTable(dt, rowIndex + 1, colIndex, cell.MaxDataRow, false);

worksheets.Clear();
worksheet = null;
worksheets = null;
workbook = null;
#endregion

创建Excel档案

Aspose.Cells.Workbook workbook1 = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet1 = workbook1.Worksheets[0];
Aspose.Cells.Cells cells1 = sheet1.Cells;

int Colnum = dtResult.Columns.Count;//表格列数
int Rownum = dtResult.Rows.Count;//表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells1[0, i].PutValue(dtResult.Columns[i].ColumnName);
}

//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells1[1 + i, k].PutValue(dtResult.Rows[i][k].ToString(), true);
Aspose.Cells.Style style = cells1[1 + i, k].GetStyle();
if ((k + 1) % 7 == 0)
style.Number = 2;
else
{
style.Number = 1;
}
style.Font.Size = 11;
cells1[1 + i, k].SetStyle(style);
}
}

设置样式

Aspose.Cells.Style style2 = cells1[0, i + 0].GetStyle();
style2.ForegroundColor = System.Drawing.Color.FromArgb(128, 96, 0);
style2.Pattern = Aspose.Cells.BackgroundType.Solid;
style2.Font.Size = 12;
style2.Font.IsBold = true;
style2.Font.Color = System.Drawing.Color.White;
cells1[0, i + 0].SetStyle(style2);
cells1[0, i + 1].SetStyle(style2);
cells1[0, i + 2].SetStyle(style2);
cells1[0, i + 3].SetStyle(style2);
cells1[0, i + 4].SetStyle(style2);
cells1[0, i + 5].SetStyle(style2);
cells1[0, i + 6].SetStyle(style2);

保存Excel档案

workbook1.Save(ConfigurationManager.AppSettings["TFile"].ToString());
workbook1.Worksheets.Clear();
sheet1 = null;
workbook1 = null;