C# 使用Aspose.Cell导出Excel

最新推荐文章于 2024-07-26 11:37:46 发布

weixin_33989780

最新推荐文章于 2024-07-26 11:37:46 发布

阅读量149

点赞数

文章标签： python

原文链接：https://my.oschina.net/guanxinsui/blog/913398

版权

2019独角兽企业重金招聘Python工程师标准>>>

/// <summary>
/// 导出数据到本地
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="tableName">表格标题</param>
/// <param name="path">保存路径</param>
public static void OutFileToDisk(DataTable dt, string tableName, string path)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格

//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中
styleTitle.Font.Name = "宋体"; //文字字体
styleTitle.Font.Size = 18; //文字大小
styleTitle.Font.IsBold = true; //粗体

//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style2.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style2.Font.Name = "宋体"; //文字字体
style2.Font.Size = 14; //文字大小
style2.Font.IsBold = true; //粗体
style2.IsTextWrapped = true; //单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

//样式3
Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style3.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style3.Font.Name = "宋体"; //文字字体
style3.Font.Size = 12; //文字大小
style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

int Colnum = dt.Columns.Count; //表格列数
int Rownum = dt.Rows.Count; //表格行数

//生成行1 标题行
cells.Merge(0, 0, 1, Colnum); //合并单元格
cells[0, 0].PutValue(tableName); //填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);

//生成行2 列名行
for (int i = 0; i < Colnum; i++)
{
cells[1, i].PutValue(dt.Columns[i].ColumnName);
cells[1, i].SetStyle(style2);
cells.SetRowHeight(1, 30);
cells.SetColumnWidth(i, 35); //100 * 256
}

//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[2 + i, k].PutValue(dt.Rows[i][k].ToString());
cells[2 + i, k].SetStyle(style3);
}
cells.SetRowHeight(2 + i, 24);
cells.SetColumnWidth(2 + i, 35);//100 * 256
}

workbook.Save(path);
}

public MemoryStream OutFileToStream(DataTable dt, string tableName)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格

int Colnum = dt.Columns.Count; //表格列数
int Rownum = dt.Rows.Count; //表格行数

//生成行1 标题行
cells.Merge(0, 0, 1, Colnum); //合并单元格
cells[0, 0].PutValue(tableName); //填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);

//生成行2 列名行
for (int i = 0; i < Colnum; i++)
{
cells[1, i].PutValue(dt.Columns[i].ColumnName);
cells[1, i].SetStyle(style2);
cells.SetRowHeight(1, 25);
}

MemoryStream ms = workbook.SaveToStream();
return ms;
}

public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
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);
Style cellStyle= cellSheet.Cells[rowIndex, colIndex].GetStyle();
cellStyle.Font.IsBold = true;
cellStyle.Font.Name= "宋体";
cellStyle.HorizontalAlignment = TextAlignmentType.Center;//设置水平位置
cellStyle.VerticalAlignment = TextAlignmentType.Center;//设置垂直位置
cellSheet.Cells.SetColumnWidth(colIndex, 300);//设置列宽
cellSheet.Cells[rowIndex, colIndex].SetStyle(cellStyle);
cellSheet.Cells[rowIndex, colIndex].SetStyle(cellStyle);
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;
}
}

转载于:https://my.oschina.net/guanxinsui/blog/913398