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