C#+Aspose.Cells 导出Excel及设置样式 （Webform/Winform）

在项目中用到，特此记录下来，Aspose.Cells 不依赖机器装没有装EXCEL都可以导出，很方便。具体可以参考其他

http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets#ImportingDatatoWorksheets-array

Webfrom版本：

效果如下：

  protected void btnAsnExport_ServerClick(object sender, EventArgs e)  {   var getAsnData = SearchDataClass.GetAsnSearchData(txtAsnNo.Value,   hfCustomerID.Value, txtTimeSelect.Value,txtSku.Value,txtSkuContent.Value);//设置导出excel列的标题   ArrayList ColTitle = new ArrayList() { "ASN编号", "SKU", "产品描述", "预期数量", "收货数量", "单位","收货库位","收货时间","所属客户","ASN状态","ASN创建时间" };   //string[] strTitle = new string[] { "ASNNo", "SKU", "SKUDescrC", "ExpectedQty", "ReceivedQty", "UOM", "ReceivingLocation", "ReceivedTime", "CustomerID", "CodeName_C" };   if (getAsnData.ToList().Count > 0)   {Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();//创建一个sheetAspose.Cells.Worksheet sheet = workbook.Worksheets[0];//为单元格添加样式      Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线 //给各列的标题行PutValue赋值int currow = 0;byte curcol = 0;//sheet.Cells.ImportCustomObjects((System.Collections.ICollection)getAsnData,//strTitle, true, 0, 0, getAsnData.Count, true, "yyyy/MM/dd HH:mm", false);sheet.Cells.ImportCustomObjects((System.Collections.ICollection)getAsnData,null, true, 0, 0, getAsnData.Count, true, "yyyy/MM/dd HH:mm", false);// 设置内容样式for (int i = 0; i < getAsnData.ToList().Count; i++){ for (int j = 0; j < 11; j++) {  sheet.Cells[i + 1, j].Style = style;  sheet.Cells[i + 1, 2].Style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left;  sheet.Cells[i + 1, 7].Style.Custom = "yyyy/MM/dd HH:mm";  sheet.Cells[i + 1, 10].Style.Custom = "yyyy/MM/dd HH:mm"; }}// 设置标题样式及背景色foreach (string s in ColTitle){ sheet.Cells[currow, curcol].PutValue(s); style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); style.Pattern = Aspose.Cells.BackgroundType.Solid; style.Font.IsBold = true; sheet.Cells[currow, curcol].Style = style; curcol++;}Aspose.Cells.Cells cells = sheet.Cells;//设置标题行高cells.SetRowHeight(0, 30);//让各列自适应宽度sheet.AutoFitColumns();//生成数据流System.IO.MemoryStream ms = workbook.SaveToStream();byte[] bt = ms.ToArray();//客户端保存的文件名string fileName = "入库查询数据导出" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";//以字符流的形式下载文件  Response.ContentType = "application/vnd.ms-excel";//通知浏览器下载文件而不是打开Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));Response.BinaryWrite(bt);Response.Flush();Response.End();   }  }

Winform版本：

效果图如下：

 public void ExportExcelWithAspose(MDataTable dt, string fileName)  {   string saveFileName = "";   SaveFileDialog saveDialog = new SaveFileDialog();   saveDialog.DefaultExt = "xls";   saveDialog.Filter = "Excel文件|*.xls";   saveDialog.FileName = fileName;   saveDialog.ShowDialog();   saveFileName = saveDialog.FileName;   if (saveFileName.IndexOf(":") < 0) return; //被点了取消   Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();   if (xlApp == null)   {MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");return;   }   try   {Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];Aspose.Cells.Cells cells = cellSheet.Cells ;//单元格//为单元格添加样式      Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];//设置居中  style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;//行索引int rowIndex = 0;//列索引int colIndex = 0;//列总数int colCount = dt.Columns.Count;//总行数int rowCount = dt.Rows.Count;rowIndex++;for (int i = 0; i < rowCount; i++){ colIndex = 0; for (int j = 0; j < colCount; j++) {  if (j == 5) { cellSheet.Cells[rowIndex, colIndex].PutValue(Convert.ToDateTime (dt.Rows[i][j].Value).ToString("yyyy/MM/dd HH:mm:ss")); }  else { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].Value); }  style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线  style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线  style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线  style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线   cellSheet.Cells[rowIndex, colIndex].Style = style;  colIndex++; } rowIndex++;}//清除内容时行列索引值为0rowIndex = 0; colIndex = 0;//列名的处理  for (int i = 0; i < colCount; i++){ cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); //设置背景颜色   style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); style.Pattern = Aspose.Cells.BackgroundType.Solid; style.Font.IsBold = true; style.IsTextWrapped = true; cells.SetRowHeight(0, 38);//设置行高 cellSheet.Cells[rowIndex, colIndex].Style = style; colIndex++;}cellSheet.AutoFitColumns();workbook.Save(Path.GetFullPath(saveFileName));xlApp.Quit();GC.Collect();//强行销毁MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK,MessageBoxIcon.Information);   }   catch (Exception ex)   {MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);   }  }  

使用方法：        ExportExcelWithAspose(SaveExcelData, "巴洛克条形码数据一览"); //SaveExcelData是datatable