/// <summary> /// 导出DataTable到Excel中(带隐藏列) /// </summary> /// <param name="datatable"></param> /// <param name="filepath"></param> /// <param name="error"></param> /// <returns></returns> public static bool DataTableToExcel(DataTable datatable, string filepath, string hiddens, out string error) { error = ""; try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.License license = new Aspose.Cells.License(); string licenseFile = ConfigurationManager.AppSettings["BASEDIRECTORY"].ToString() + "\\bin\\celllic.lic"; license.SetLicense(licenseFile); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; for (int i = 0; i < datatable.Columns.Count; i++) { cells[nRow, i].PutValue(datatable.Columns[i]); } Aspose.Cells.Range w; w = cells.CreateRange(nRow, 0, 1, datatable.Columns.Count); foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; System.IO.MemoryStream mstream = new System.IO.MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } string[] array = hiddens.Split(','); for (int i = 0; i < array.Length; i++) { if (string.IsNullOrEmpty(array[i])) { continue; } cells.HideColumn(byte.Parse(array[i].ToString())); } if (filepath.IndexOf(".xls") > -1) { workbook.Save(filepath); } else { workbook.Save(filepath, Aspose.Cells.FileFormatType.Excel2007Xlsx); } return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
public Workbook CreateCatalog() { //Open a template file string designerFile = MapPath("~/App_Data/xls/Northwind.xls"); Workbook workbook = new Workbook(designerFile); productsList = productDao.GetAll(); CategoryList = categoryDao.GetAll(); //Create a new datatable DataTable dataTable2 = new DataTable(); //Get a worksheet Worksheet sheet = workbook.Worksheets["Sheet2"]; //Name the sheet sheet.Name = "Catalog"; //Get the worksheet cells Aspose.Cells.Cells cells = sheet.Cells; int currentRow = 55; //Add LightGray color to color palette workbook.ChangePalette(Color.LightGray, 55); //Get the workbook's styles collection StyleCollection styles = workbook.Styles; //Set CategoryName style with formatting attributes int styleIndex = styles.Add(); Style styleCategoryName = styles[styleIndex]; styleCategoryName.Font.Size = 14; styleCategoryName.Font.Color = Color.Blue; styleCategoryName.Font.IsBold = true; styleCategoryName.Font.Name = "Times New Roman"; //Set Description style with formatting attributes styleIndex = styles.Add(); Style styleDescription = styles[styleIndex]; styleDescription.Font.Name = "Times New Roman"; styleDescription.Font.Color = Color.Blue; styleDescription.Font.IsItalic = true; //Set ProductName style with formatting attributes styleIndex = styles.Add(); Style styleProductName = styles[styleIndex]; styleProductName.Font.IsBold = true; //Set Title style with formatting attributes styleIndex = styles.Add(); Style styleTitle = styles[styleIndex]; styleTitle.Font.IsBold = true; styleTitle.Font.IsItalic = true; styleTitle.ForegroundColor = Color.LightGray; styleIndex = styles.Add(); Style styleNumber = styles[styleIndex]; styleNumber.Font.Name = "Times New Roman"; styleNumber.Number = 8; //Create the styleflag struct StyleFlag styleflag = new StyleFlag(); styleflag.All = true; //Get the horizontal page breaks collection HorizontalPageBreakCollection hPageBreaks = sheet.HorizontalPageBreaks; DataTable dataTable1 = ConvertCategoriesToDataTable(CategoryList); for (int i = 0; i < dataTable1.Rows.Count; i++) { currentRow += 2; cells.SetRowHeight(currentRow, 20); cells[currentRow, 1].SetStyle(styleCategoryName); DataRow categoriesRow = dataTable1.Rows[i]; //Write CategoryName cells[currentRow, 1].PutValue((string)categoriesRow["CategoryName"]); //Write Description currentRow++; cells[currentRow, 1].PutValue((string)categoriesRow["Description"]); cells[currentRow, 1].SetStyle(styleDescription); dataTable2.Clear(); dataTable2 = GetProductsByCateGoryID(Convert.ToInt32(categoriesRow["CategoryID"].ToString())); currentRow += 2; //Import the datatable to the sheet cells.ImportDataTable(dataTable2, true, currentRow, 1); //Create a range Range range = cells.CreateRange(currentRow, 1, 1, 4); //Apply style to the range range.ApplyStyle(styleTitle, styleflag); //Create a range range = cells.CreateRange(currentRow + 1, 1, dataTable2.Rows.Count, 1); //Apply style to the range range.ApplyStyle(styleProductName, styleflag); //Create a range range = cells.CreateRange(currentRow + 1, 4, dataTable2.Rows.Count, 1); //Apply style to the range range.ApplyStyle(styleNumber, styleflag); currentRow += dataTable2.Rows.Count; //Apply horizontal page breaks hPageBreaks.Add(currentRow, 0); } //Remove the unnecessary worksheets in the workbook for (int i = 0; i < workbook.Worksheets.Count; i++) { sheet = workbook.Worksheets[i]; if (sheet.Name != "Catalog") { workbook.Worksheets.RemoveAt(i); i--; } } //Return the generated workbook return(workbook); }