将ListView导出到EXCEL,并设置格式

最新推荐文章于 2019-08-01 12:04:03 发布
最新推荐文章于 2019-08-01 12:04:03 发布
阅读量1.5k 收藏
点赞数
分类专栏： c# asp.net2.0 文章标签： listview excel exception string null 产品
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dmfoyq/article/details/5868263
版权

Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                throw new Exception("無法創建Excel物件，可能您的機子未安裝Excel2003");
                //return;
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            try
            {

                int rcount = worksheet.Rows.Count;
                Excel.Range ranCol = worksheet.get_Range("A1", "AE" + rcount.ToString());
                ranCol.Font.Name = "新細明體";
                ranCol.Font.Size = 10;
                ranCol.RowHeight = 18;
                ranCol.Interior.ColorIndex = 2;

                ranCol = worksheet.get_Range("A1", "A" + rcount.ToString());
                ranCol.ColumnWidth = 30;
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
               
                ranCol = worksheet.get_Range("B1", "B" + rcount.ToString());
                ranCol.ColumnWidth = 8;
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                ranCol = worksheet.get_Range("C1", "C" + rcount.ToString());
                ranCol.ColumnWidth = 8;
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                ranCol = worksheet.get_Range("D1", "D" + rcount.ToString());
                ranCol.ColumnWidth = 10;
               // ranCol.NumberFormatLocal = "#,##0_";
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                ranCol = worksheet.get_Range("E1", "E" + rcount.ToString());
                ranCol.ColumnWidth = 10;
                //ranCol.NumberFormatLocal = "#,##0_";
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                ranCol = worksheet.get_Range("F1", "F" + rcount.ToString());
                ranCol.ColumnWidth = 25;
                ranCol.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                寫入欄位
                int rowIndex = 1;
                int colIndex = 1;

                Excel.Range rangTitle = worksheet.get_Range("A1","F1");
                rangTitle.Font.Name = "新細明體";
                rangTitle.Font.Size = 20;
                rangTitle.RowHeight = 36;
                rangTitle.Font.ColorIndex = 49;
                rangTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                rangTitle.Font.FontStyle = "加粗";
                rangTitle.WrapText = true;
                rangTitle.Merge(rangTitle.MergeCells);
                rangTitle.Borders.LineStyle = 4;     //设置单元格边框的粗细
                rangTitle.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
                worksheet.Cells[rowIndex, colIndex] = ExcelTitle;
                rowIndex++;


                Excel.Range rangCompanyName = worksheet.get_Range("A2", "F2");
                rangCompanyName.Font.Name = "新細明體";
                rangCompanyName.Font.Size = 12;
                rangCompanyName.RowHeight = 24;
                rangCompanyName.Font.ColorIndex = 49;
                rangCompanyName.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangCompanyName.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                //rangCompanyName.Font.FontStyle = "加粗";
                rangCompanyName.WrapText = true;
                rangCompanyName.Merge(rangCompanyName.MergeCells);
                rangCompanyName.Borders.LineStyle = 4;     //设置单元格边框的粗细
                rangCompanyName.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
                string strText = FrmMain.StrCompanyName.Trim();
                if (!string.IsNullOrEmpty(FrmMain.CompanyTel))
                    strText += "     "+FrmMain.CompanyTel;
                if (!string.IsNullOrEmpty(FrmMain.CompanyAddress))
                    strText += "     " + FrmMain.CompanyAddress;
                worksheet.Cells[rowIndex, colIndex] = strText;
                rowIndex++;

                string StrTotalMoney = "";//用于计算总计金额的
                foreach (ListViewGroup lg in lv.Groups)
                {

                    #region 设置分组的格式
                    int startRowIndex = rowIndex;
                    Excel.Range rangeClass = worksheet.get_Range("A" + rowIndex.ToString(), "F" + rowIndex.ToString());
                   // rangeClass.Select();
                    worksheet.Cells[rowIndex, 1] = lg.Header.Trim();
                    rangeClass.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rangeClass.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    rangeClass.Font.Name = "新細明體";
                    rangeClass.Font.Size = 12;
                    rangeClass.Font.FontStyle = "加粗";
                    rangeClass.Font.ColorIndex = 2;
                    rangeClass.WrapText = true;
                    rangeClass.Cells.Interior.ColorIndex = 49;
                    rangeClass.Merge(rangeClass.MergeCells);
                    rowIndex++;
                    #endregion

                    #region 设置标题
                  
                    Excel.Range rangeTitle = worksheet.get_Range("A" + rowIndex.ToString(), "F" + rowIndex.ToString());
                    rangeTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rangeTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    rangeTitle.Font.Name = "新細明體";
                    rangeTitle.Font.Size = 10;
                   // rangeTitle.ColumnWidth = 7;
                    //rangeTitle.Font.FontStyle = "加粗";
                    //rangeTitle.Font.ColorIndex = 2;
                    rangeTitle.WrapText = true;
                    //rangeClass.Cells.Interior.ColorIndex = 49;
                    //
                    for(int k=1;k<lv.Columns.Count;k++)
                    {
                       ColumnHeader ch=lv.Columns[k];
                       worksheet.Cells[rowIndex, k] = ch.Text;
                       colIndex++;
                    }
                    worksheet.Cells[rowIndex, colIndex] = "備註";
                    rowIndex++;
                    colIndex = 1;
                    #endregion

                    #region 写入产品内容
                  
                    foreach (ListViewItem li in lg.Items)
                    {
                        for(int v=1;v<li.SubItems.Count;v++)
                           worksheet.Cells[rowIndex,v] = li.SubItems[v].Text;
                       rowIndex++;
                    }
                    colIndex = 1;
                    #endregion

                    #region 写入小计
                    worksheet.Cells[rowIndex, colIndex] = "             小　　　計";
                    Excel.Range rangXj = worksheet.get_Range("B" + rowIndex.ToString(), "D" + rowIndex.ToString());
                    rangXj.Merge(rangXj.MergeCells);
                    colIndex+=4;
                    worksheet.Cells[rowIndex, colIndex] = "=SUM(E"+startRowIndex.ToString()+":E"+(rowIndex-1).ToString()+")";
                    if(string.IsNullOrEmpty(StrTotalMoney.Trim()))
                        StrTotalMoney += "E" + rowIndex.ToString();
                    else
                        StrTotalMoney += ",E" + rowIndex.ToString();
                   #endregion

                    #region 设置边框
                    Excel.Range rangBorder = worksheet.get_Range("A" + (startRowIndex).ToString(), "F" + rowIndex.ToString());
                    rangBorder.Borders.LineStyle = 1;     //设置单元格边框的粗细
                    rangBorder.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
                    rowIndex+=2;
                    colIndex = 1;
                    #endregion

                }


                #region 写入总计
                if (!string.IsNullOrEmpty(StrTotalMoney.Trim()))
                    StrTotalMoney = "=SUM(" + StrTotalMoney + ")";

                colIndex = 5;
                worksheet.Cells[rowIndex, colIndex] = StrTotalMoney;
                Excel.Range rangTotal2 = worksheet.get_Range("B" + rowIndex.ToString(), "F" + rowIndex.ToString());
                rangTotal2.Font.Name = "新細明體";
                rangTotal2.Font.Size = 12;
                rangTotal2.Font.FontStyle = "加粗";
                rangTotal2.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                rangTotal2.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                rangTotal2.Borders.LineStyle = 2;     //设置单元格边框的粗细
                rangTotal2.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
                rangTotal2.RowHeight = 25;

                colIndex = 2;
                worksheet.Cells[rowIndex, colIndex] = "總計";
                Excel.Range rangTotal = worksheet.get_Range("B" + rowIndex.ToString(), "D" + rowIndex.ToString());
                rangTotal.Merge(rangTotal.MergeCells);
                rangTotal.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangTotal.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                colIndex+=4;
                #endregion

                #region 设置数字格式
                //Excel.Range rangNum1 = worksheet.get_Range("E1", "E" +);

                #endregion

 


                //for (int i = 0; i < dv.Columns.Count; i++)
                //{
                //    if (dv.Columns[i].Visible)
                //    {
                //        worksheet.Cells[rowIndex, colIndex] = dv.Columns[i].HeaderText;
                //        colIndex++;
                //    }
                //}
                //rowIndex = 2;
                //colIndex = 1;
                //foreach (DataGridViewRow r in dv.Rows)
                //{
                //    colIndex = 1;
                //    foreach (DataGridViewCell cell in r.Cells)
                //    {
                //        if (cell.Visible)
                //        {
                //            worksheet.Cells[rowIndex, colIndex] = "'"+cell.Value;

                //            colIndex++;
                //        }
                //    }
                //    rowIndex++;
                //}

                //worksheet.Columns.EntireColumn.AutoFit();//列寬自適應。
                //worksheet.Columns.EntireColumn.HorizontalAlignment = 2;
                //Excel.Range rangCpkj = worksheet.get_Range("D2", "D" + worksheet.UsedRange.Rows.Count.ToString());
                //Excel.Range rangZz = worksheet.get_Range("G2", "G" + worksheet.UsedRange.Rows.Count.ToString());
                //rangCpkj.NumberFormatLocal = "@";
                //rangZz.NumberFormatLocal = "@";
                worksheet.Name = ExcelTitle;
                workbook.Saved = true;
                workbook.SaveCopyAs(saveFilePath);

            }
            catch (Exception ex)
            {
                //fileSaved = false;
                throw new Exception("導出檔時出錯,檔可能正被打開！/n" + ex.Message);
            }
            finally
            {
                xlApp.Quit();
                GC.Collect();//強行銷毀
            }

 

dmfoyq
关注
专栏目录
c# listview导出excel文件
gyzsky的专栏
06-20 2258
首先在工程中需要添加对Microsoft Excel office 11.0 object的引用 生成excel的类代码如下 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms; namespace LvwToE
将ListView中的数据导出到Excel中
04-26
将WPF中的ListView中的数据导出到Excel中
C#中ListView导出Excel
weixin_30920091的博客
08-01 512
/// <summary> /// 把listView里面的数据保存成Excle /// </summary> /// <param name="lv"></param> public static void ExportToExcel(ListView pListView)...
listview导出到Excel
zsk
08-26 1716
<br />导出数据时，用到了Excel来接收数据，首先在VB6中引用Excel<br />然后定义<br />        Dim mobjExcel     As Excel.Application<br />        Dim mobjWorkBook     As Excel.Workbook<br />        Dim strListItem     As String<br />        Dim strCol     As String<br />        Dim lng
ListView数据导出至Excel
weixin_34310369的博客
07-10 117
1ListView数据导出成Excel#regionListView数据导出成Excel2/**////<summary>3///ListView导出数据至Excel4///</summary>5///<paramname="list">ListView</...
VB将Listview数据导出到Excel表格的实例
05-16
以上就是VB将Listview数据导出到Excel的基本流程。通过理解这些步骤，你可以根据实际需求进行调整，例如添加自定义格式化、合并单元格或使用其他Excel功能。这不仅有助于提高用户体验，还能增强数据处理能力。
C# 导入Excel文件到ListView和导出ListView到Excel文件
04-15
本主题将详细探讨如何使用C#实现从Excel文件导入数据到ListView控件，以及如何将ListView中的数据导出回Excel文件。这两个操作在数据分析、报表生成和数据管理等场景中非常常见。 首先，要导入Excel文件到ListView...
C#中将ListView中数据导出到Excel的实例方法
12-26
代码如下: private void 导出数据_Click(object sender, EventArgs e) { ExportToExecl(); } ///  /// 执行导出数据 /// </summary> public void ExportToExecl() { System.Windows.Forms.SaveFileDialog ...
VB将Listview数据导出到Excel表格的实例.rar
04-03
本实例主要介绍如何使用VB将ListView控件中的数据导出到Excel表格中，这在数据管理、报表生成或数据分析等场景下非常有用。 首先，我们需要了解ListView控件。ListView是VB中一个常用的数据展示控件，它可以显示多...
在C#编程中将listview中的数据导出到excel表格中
05-08
在C#编程中将listview中的数据导出到excel表格中
listview导出到电子表格xls
04-09
listVIEW的极速导出 应用上流技术 一万条记录导出不到半分钟
VB把Listview中显示的内容导出到Excel中.rar
07-10
VB把Listview中显示的内容导出到Excel中，用到了控件：MSCOMCTL.OCX和COMDLG32.OCX，我觉得挺实用的一个报表显示实例。
listview中的数据导出到excel中
01-14
利用listView控件将数据导出到外部的excel文件中
将ListView中的内容以Excel导出
weixin_30699463的博客
08-22 68
HTML: <div> <asp:ListView ID="ListView1" runat="server"> <ItemTemplate> <tr> <td id="Td1" runat="server" style=""> ...
ListView导出Excel
大森林的专栏(Jack Wu)
01-29 1928
 先引用：COM——》Microsoft.Excel--------------------------------------------------------------------public void ExportExcel(ListView lv)  {   if(lv.Items == null) return;      string saveFileName = "";  
ListView导出到Excel
我的英文站点：https://iorilan.medium.com/
01-21 1588
from:http://limmo.blog.sohu.com/45206198.html using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; usin
C# 实现ListView数据导出到Excel操作
在循环内部，将ListView的数据逐个添加到Excel单元格中，具体做法是根据列索引和行索引确定单元格位置，并设置其值。 通过这个函数，你可以轻松地将ListView中的数据结构（如列表、数组等）转换成Excel表格形式，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值