C# 使用Aspose.Cells 将DataGridView,ListView,DataTable导出为Excel

下载地址：Aspose.Cells

path：导出路径支持xls和xlsx两种格式，若数据超过65535，必须使用xlsx格式；

sheetName：要导入的Excel工作区

bool newExcel：是否为新建表，若需将数据导出到新建Excel表格，则设置为true，若需将数据导出到已存在的Excel，则设置为false；

bool NewSheet：是否为新建工作区，若需将数据导出到新建工作区，则设置为true，若需将数据导出到已存在的工作区，则设置为false；

/// <summary>
/// DataGridView导出excel
/// </summary>
/// <param name="dgv">DataGridView</param>
/// <param name="path">导出路径</param>
/// <param name="sheetName">工作区名称</param>
/// <param name="newExcel">是否为新建表</param>
/// <param name="newSheet">是否新建工作区</param>
[Obsolete]
public static void DataGirdViewToExcel(DataGridView dgv, string path, string sheetName,bool newExcel,bool newSheet)
{
    Workbook workbook = new Workbook();
    if (!newExcel && newSheet)
    {
        workbook.Open(path);
        workbook.Worksheets.Add(sheetName);
    }
    Worksheet sheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
    sheet.Name = sheetName;
    Cells cells = sheet.Cells;
    Style styleTitle = workbook.Styles[workbook.Styles.Add()];
    styleTitle.HorizontalAlignment = TextAlignmentType.Left;
    styleTitle.Font.Name = "微软雅黑";
    styleTitle.Font.Size = 14;
    styleTitle.Font.IsBold = false;
    Style style2 = workbook.Styles[workbook.Styles.Add()];
    style2.HorizontalAlignment = TextAlignmentType.Left;
    style2.Font.Name = "微软雅黑";
    style2.Font.Size = 12;
    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;
    Style style3 = workbook.Styles[workbook.Styles.Add()];
    style3.HorizontalAlignment = TextAlignmentType.Left;
    style3.Font.Name = "微软雅黑";
    style3.Font.Size = 10;
    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 = dgv.Columns.Count;
    int Rownum = dgv.Rows.Count;
    for (int i = 0; i < Colnum; i++)
    {
        string title = dgv.Columns[i].HeaderText;
        cells[0, i].PutValue(title);
        cells[0, i].SetStyle(style2);
        cells.SetRowHeight(0, 24);
        cells.SetColumnWidth(i, 20);
    }
    for (int i = 0; i < Rownum - 1; i++)
    {
        for (int k = 0; k < Colnum; k++)
        {
            cells[1 + i, k].PutValue(dgv[k, i].Value);
            cells[1 + i, k].SetStyle(style3);
        }
        cells.SetRowHeight(1 + i, 18);
    }
    workbook.Save(path);
}
/// <summary>
/// DataTable转Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="path">导出路径</param>
/// <param name="sheetName">工作区名称</param>
/// <param name="newExcel">是否为新建表</param>
/// <param name="newSheet">是否新建工作区</param>
[Obsolete]
public static void DataTableToExcel(DataTable dt, string path, string sheetName, bool newExcel, bool newSheet)
{
    Workbook workbook = new Workbook();
    if (!newExcel && newSheet)
    {
        workbook.Open(path);
        workbook.Worksheets.Add(sheetName);
    }
    Worksheet sheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
    sheet.Name = sheetName;
    Cells cells = sheet.Cells;
    Style styleTitle = workbook.Styles[workbook.Styles.Add()];
    styleTitle.HorizontalAlignment = TextAlignmentType.Left;
    styleTitle.Font.Name = "微软雅黑";
    styleTitle.Font.Size = 14;
    styleTitle.Font.IsBold = false;
    Style style2 = workbook.Styles[workbook.Styles.Add()];
    style2.HorizontalAlignment = TextAlignmentType.Left;
    style2.Font.Name = "微软雅黑";
    style2.Font.Size = 12;
    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;
    Style style3 = workbook.Styles[workbook.Styles.Add()];
    style3.HorizontalAlignment = TextAlignmentType.Left;
    style3.Font.Name = "微软雅黑";
    style3.Font.Size = 10;
    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;
    for (int i = 0; i < Colnum; i++)
    {
        string title = dt.Columns[i].ColumnName;
        cells[0, i].PutValue(title);
        cells[0, i].SetStyle(style2);
        cells.SetRowHeight(0, 24);
        cells.SetColumnWidth(i, 20);
    }
    for (int i = 0; i < Rownum; i++)
    {
        for (int k = 0; k < Colnum; k++)
        {
            cells[1 + i, k].PutValue(dt.Rows[i][k]);
            cells[1 + i, k].SetStyle(style3);
        }
        cells.SetRowHeight(1 + i, 18);
    }
    workbook.Save(path);
}

/// <summary>
/// ListView导出到excel
/// </summary>
/// <param name="list">ListView</param>
/// <param name="ColumnWidth">表格列宽</param>
/// <param name="path">导出路径</param>
/// <param name="sheetName">工作区名称</param>
/// <param name="newExcel">是否为新建表</param>
/// <param name="newSheet">是否新建工作区</param>
[System.Obsolete]
public static void ListViewToExcel(ListView list, List<int> ColumnWidth, string path, string sheetName,bool newExcel,bool newSheet)
{
    Workbook wb = new Workbook();
    if (!newExcel && newSheet)
    {
        wb.Open(path);
        wb.Worksheets.Add(sheetName);
    }
    Worksheet ws = wb.Worksheets[wb.Worksheets.Count - 1];
    ws.Name = sheetName;
    Cells cell = ws.Cells;
    //定义并获取导出的数据源
    string[,] _ReportDt = new string[list.Items.Count, list.Columns.Count];
    for (int i = 0; i < list.Items.Count; i++)
    {
        for (int j = 0; j < list.Columns.Count; j++)
        {
            _ReportDt[i, j] = list.Items[i].SubItems[j].Text.ToString();
        }
    }
    //设置行高
    cell.SetRowHeight(0, 20);
    //设置字体样式
    Style style1 = wb.Styles[wb.Styles.Add()];
    style1.HorizontalAlignment = TextAlignmentType.Left;
    style1.Font.Name = "微软雅黑";
    style1.Font.IsBold = false;
    style1.Font.Size = 12;
    style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
    style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
    style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
    style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
    Style style2 = wb.Styles[wb.Styles.Add()];
    style2.HorizontalAlignment = TextAlignmentType.Left;
    style2.Font.Name = "微软雅黑";
    style2.Font.IsBold = false;
    style2.Font.Size = 10;
    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;
    //给单元格关联样式
    cell["A1"].SetStyle(style1); //报表名字 样式
    //设置Execl列名
    for (int i = 0; i < list.Columns.Count; i++)
    {
        cell[0, i].PutValue(list.Columns[i].Text);
        cell[0, i].SetStyle(style2);
    }
    //设置单元格内容
    int posStart = 1;
    for (int i = 0; i < list.Items.Count; i++)
    {
        for (int j = 0; j < list.Columns.Count; j++)
        {
            cell[i + posStart, j].PutValue(_ReportDt[i, j].ToString());
            cell[i + posStart, j].SetStyle(style2);
        }
    }
    //设置列宽
    for (int i = 0; i < list.Columns.Count; i++)
    {
        cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
    }
    //保存excel表格
    wb.Save(path);
}