public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); string lic = @"<License> <Data> <SerialNumber>aed83727-21cc-4a91-bea4-2607bf991c21</SerialNumber> <EditionType>Enterprise</EditionType> <Products> <Product>Aspose.Total</Product> </Products> </Data> <Signature>CxoBmxzcdRLLiQi1kzt5oSbz9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1Fn9KgDAQ8B11psxIWvepKidw8ZmDmbk9kdJbVBOkuAESXDdtDEDZMB/zL7Y=</Signature> </License>"; Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic)); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = "Sheet1"; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return succeed; }
public MemoryStream ToExcel <T>(IPaginatedList <T> list) { try { //Create excel workbook Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = book.Worksheets[0]; List <string> objHeaders = new List <string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); //Avoid columns from Base Business Entity if (property.Name == "ChangedFields") { break; } objHeaders.Add(property.Name); } sheet.Cells.ImportCustomObjects(list.Items.ToList(), objHeaders.ToArray(), true, 1, 0, list.TotalRecordCount, true, string.Empty, false); #region Header style Aspose.Cells.Style style = book.CreateStyle(); style.Font.IsBold = true; // Define a style flag struct. Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag(); flag.FontBold = true; // Get the first row in the first worksheet. Aspose.Cells.Row row = book.Worksheets[0].Cells.Rows[1]; // Apply the style to it. row.ApplyStyle(style, flag); #endregion // Auto-fit all the columns book.Worksheets[0].AutoFitColumns(); using (MemoryStream outputStream = new MemoryStream()) { //Save workbook as stream instead of saving it to the Disk book.Save(outputStream, Aspose.Cells.SaveFormat.Xlsx); //Call dispose methods sheet.Dispose(); book.Dispose(); outputStream.Position = 0; return(outputStream); } } catch (Exception) { // TODO : Handle out of memory exception throw; } }
/// <summary> /// 导出到Excel文件 /// </summary> /// <param name="dt">DataTable</param> /// <param name="path">保存路径</param> /// <returns></returns> public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); //设置破解DLL的许可证 li.SetLicense(Application.StartupPath + @"\Resources\License.lic"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = dt.TableName; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception) { succeed = false; } } return(succeed); }
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); string lic = @"<License> <Data> <SerialNumber>aed83727-21cc-4a91-bea4-2607bf991c21</SerialNumber> <EditionType>Enterprise</EditionType> <Products> <Product>Aspose.Total</Product> </Products> </Data> <Signature>CxoBmxzcdRLLiQi1kzt5oSbz9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1Fn9KgDAQ8B11psxIWvepKidw8ZmDmbk9kdJbVBOkuAESXDdtDEDZMB/zL7Y=</Signature> </License>"; Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic)); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = "Sheet1"; int rowIndex = 0; int colIndex = 0; int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return(succeed); }
public static bool ExportExcelWithAspose(DataSet ds, string path) { bool succeed = false; if (ds != null && ds.Tables.Count > 0) { try { Aspose.Cells.License li = new Aspose.Cells.License(); var lic = Resources.License; Stream s = new MemoryStream(lic); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Worksheets.Clear(); for (int tableCount = 0; tableCount < ds.Tables.Count; tableCount++) { workbook.Worksheets.Add(ds.Tables[tableCount].TableName); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[tableCount]; // cellSheet.Name = ds.Tables[tableCount].TableName; int rowIndex = 0; int colIndex = 0; int colCount = ds.Tables[tableCount].Columns.Count; int rowCount = ds.Tables[tableCount].Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); } path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return(succeed); }
//internal void RectangleExport() //{ // throw new NotImplementedException(); //} /// <summary> /// 导出方法 /// </summary> /// <param name="DataGridView"></param> /// <param name="dt"></param> /// <param name="Export"></param> public void DataGridViewToExcel(DataGridView DataGridView, int Export = 0) { if (DataGridView.Rows.Count < 1) { return; } int i = 0; int Col = 0; ArrayList colList = new ArrayList(); //存放不显示的列 Aspose.Cells.Workbook xlBook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet xlSheet = default(Aspose.Cells.Worksheet); string filepath = ""; Aspose.Cells.Style style = new Aspose.Cells.Style(); style.Font.Name = "宋体"; Aspose.Cells.Style stylecolor = new Aspose.Cells.Style(); stylecolor.ForegroundColor = Color.LightBlue; Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag(); flag.Font = true; xlSheet = xlBook.Worksheets[0]; //intExcelTempIndex += 1; //xlSheet.Name = "牙模盒使用记录" + intExcelTempIndex.ToString(); int Rowi = 0; int Rown = 0; int RownMax = 0; //最后Export列不导出 RownMax = System.Convert.ToInt32(DataGridView.Columns.Count - Export); //填写表头 for (i = 0; i < RownMax; i++) { if (DataGridView.Columns[i].Width <= 5 || DataGridView.Columns[i].Visible == false) { } else { colList.Add(i); xlSheet.Cells[0, Col].PutValue(DataGridView.Columns[i].HeaderText); Col++; } } //表内容 int intcount = 0; for (Rowi = 0; Rowi <= DataGridView.Rows.Count - 1; Rowi++) { if (DataGridView.Rows[Rowi].Visible == false) { intcount++; continue; } for (Rown = 0; Rown <= colList.Count - 1; Rown++) { if (DataGridView.Rows[Rowi].Cells[Rown].Value != null) { xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue(DataGridView.Rows[Rowi].Cells[Rown].Value.ToString()); } else { xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue(""); } } } xlSheet.Cells.ApplyStyle(style, flag); if (DataGridView.Rows.Count > 60000) { filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xlsx"; } else { filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xls"; } xlBook.Save(filepath); System.Diagnostics.Process.Start(filepath); //'打开导出Excel }
public static bool ExportExcelWithAspose(DataSet ds, string path) { bool succeed = false; if (ds != null&&ds.Tables.Count>0) { try { Aspose.Cells.License li = new Aspose.Cells.License(); var lic = Resources.License; Stream s = new MemoryStream(lic); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); for (int tableCount = 0; tableCount < ds.Tables.Count; tableCount++) { workbook.Worksheets.Add(ds.Tables[tableCount].TableName); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[tableCount]; // cellSheet.Name = ds.Tables[tableCount].TableName; int rowIndex = 0; int colIndex = 0; int colCount = ds.Tables[tableCount].Columns.Count; int rowCount = ds.Tables[tableCount].Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Rows[i][j].ToString()); colIndex++; } rowIndex++; } cellSheet.AutoFitColumns(); } path = Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { succeed = false; } } return succeed; }