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