导出excel

    StringBuilder sb = new StringBuilder();  
      
    sb.Append("MAC地址,生产商,报备厂商,终端类型,硬件版本,软件版本kernel,软件版本APP,上线IP,开机时间\n");  
      
    for (int i = 0; i < itemCount; i++)  
    {  
        sb.Append(((Label)rptList.Items[i].FindControl("lblMac")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblVendor")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblCom")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblBoxType")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblHw")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblKernel")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblSw")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblIP")).Text + ",");  
        sb.Append(((Label)rptList.Items[i].FindControl("lblAddDate")).Text);  
        sb.Append("\n");  
    }  
    Response.ClearHeaders();  
    Response.AppendHeader("Content-disposition", "attachment;filename=开机认证记录.csv");  
    Response.Write(sb.ToString());  
    Response.End();  

以下还有4种方法,不过有点麻烦:

1.采用流方式

    private void DataGridViewToExcel(DataGridView dgv)  
            {  
                SaveFileDialog dlg = new SaveFileDialog();  
                dlg.Filter = "Execl files (*.xls)|*.xls";  
                dlg.FilterIndex = 0;  
                dlg.RestoreDirectory = true;  
                dlg.CreatePrompt = true;  
                dlg.Title = "保存为Excel文件";  
                if (dlg.ShowDialog() == DialogResult.OK)  
                {  
                    Stream myStream;  
                    myStream = dlg.OpenFile();  
                    StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
                    string columnTitle = "";  
                    try  
                    {  
                        //写入列标题  
                        for (int i = 0; i < dgv.ColumnCount; i )  
                        {  
                            if (i > 0)  
                            {  
                                columnTitle = "\t";  
                            }  
                            columnTitle = dgv.Columns[i].HeaderText;  
                        }  
                        sw.WriteLine(columnTitle);  
                        //写入列内容  
                        for (int j = 0; j < dgv.Rows.Count; j )  
                        {  
                            string columnValue = "";  
                            for (int k = 0; k < dgv.Columns.Count; k )  
                            {  
                                if (k > 0)  
                                {  
                                    columnValue = "\t";  
                                }  
                                if (dgv.Rows[j].Cells[k].Value == null)  
                                    columnValue = "";  
                                else  
                                    columnValue = dgv.Rows[j].Cells[k].Value.ToString().Trim();  
                            }  
                            sw.WriteLine(columnValue);  
                        }  
                        sw.Close();  
                        myStream.Close();  
                    }  
                    catch (Exception e)  
                    {  
                        MessageBox.Show(e.ToString());  
                    }  
                    finally  
                    {  
                        sw.Close();  
                        myStream.Close();  
                    }  
                }  
            }  

2.显示Excel界面

    /// <remarks>  
    /// 从工具箱中添加"Microsoft Excel 11.0 Object Library"  
    /// 命名空间添加 using Excel = Microsoft.Office.Interop.Excel;  
    /// </remarks>  
    /// <returns> </returns>  
    public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)  
    {  
    if (dgv.Rows.Count == 0)  
    return false;  
    //建立Excel对象  
    Excel.Application excel = new Excel.Application();  
    excel.Application.Workbooks.Add(true);  
    excel.Visible = isShowExcle;  
    //生成字段名称  
    for (int i = 0; i < dgv.ColumnCount; i )  
    {  
    excel.Cells[1, i 1] = dgv.Columns[i].HeaderText;  
    }  
    //填充数据  
    for (int i = 0; i < dgv.RowCount - 1; i )  
    {  
    for (int j = 0; j < dgv.ColumnCount; j )  
    {  
    if (dgv[j, i].ValueType == typeof(string))  
    {  
    excel.Cells[i 2, j 1] = "'" dgv[j, i].Value.ToString();  
    }  
    else  
    {  
    excel.Cells[i 2, j 1] = dgv[j, i].Value.ToString();  
    }  
    }  
    }  
    return true;  
    }  

3.采用Excel控件方法

    /// <summary>  
    ///方法,导出DataGridView中的数据到Excel文件  
    /// </summary>  
    /// <remarks>  
    /// add com "Microsoft Excel 11.0 Object Library"  
    /// using Excel=Microsoft.Office.Interop.Excel;  
    /// using System.Reflection;  
    /// </remarks>  
    /// <param name="dgv" /> DataGridView   
    public static void DataGridViewToExcel(DataGridView dgv)  
    {  
     
    #region 验证可操作性  
    //申明保存对话框  
    SaveFileDialog dlg = new SaveFileDialog();  
    //默然文件后缀  
    dlg.DefaultExt = "xls ";  
    //文件后缀列表  
    dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";  
    //默然路径是系统当前路径  
    dlg.InitialDirectory = Directory.GetCurrentDirectory();  
    //打开保存对话框  
    if (dlg.ShowDialog() == DialogResult.Cancel) return;  
    //返回文件路径  
    string fileNameString = dlg.FileName;  
    //验证strFileName是否为空或值无效  
    if (fileNameString.Trim() == " ")  
    { return; }  
    //定义表格内数据的行数和列数  
    int rowscount = dgv.Rows.Count;  
    int colscount = dgv.Columns.Count;  
    //行数必须大于0  
    if (rowscount <= 0)  
    {  
    MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    return;  
    }  
    //列数必须大于0  
    if (colscount <= 0)  
    {  
    MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    return;  
    }  
    //行数不可以大于65536  
    if (rowscount > 65536)  
    {  
    MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    return;  
    }  
    //列数不可以大于255  
    if (colscount > 255)  
    {  
    MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    return;  
    }  
    //验证以fileNameString命名的文件是否存在,如果存在删除它  
    FileInfo file = new FileInfo(fileNameString);  
    if (file.Exists)  
    {  
    try  
    {  
    file.Delete();  
    }  
    catch (Exception error)  
    {  
    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
    return;  
    }  
    }  
    #endregion  
    Excel.Application objExcel = null;  
    Excel.Workbook objWorkbook = null;  
    Excel.Worksheet objsheet = null;  
    try  
    {  
    //申明对象  
    objExcel = new Microsoft.Office.Interop.Excel.Application();  
    objWorkbook = objExcel.Workbooks.Add(Missing.Value);  
    objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;  
    //设置EXCEL不可见  
    objExcel.Visible = false;  
    //向Excel中写入表格的表头  
    int displayColumnsCount = 1;  
    for (int i = 0; i <= dgv.ColumnCount - 1; i )  
    {  
    if (dgv.Columns[i].Visible == true)  
    {  
    objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();  
    displayColumnsCount ;  
    }  
    }  
    //设置进度条  
    //tempProgressBar.Refresh();  
    //tempProgressBar.Visible = true;  
    //tempProgressBar.Minimum=1;  
    //tempProgressBar.Maximum=dgv.RowCount;  
    //tempProgressBar.Step=1;  
    //向Excel中逐行逐列写入表格中的数据  
    for (int row = 0; row <= dgv.RowCount - 1; row )  
    {  
    //tempProgressBar.PerformStep();  
    displayColumnsCount = 1;  
    for (int col = 0; col < colscount; col )  
    {  
    if (dgv.Columns[col].Visible == true)  
    {  
    try  
    {  
    objExcel.Cells[row 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();  
    displayColumnsCount ;  
    }  
    catch (Exception)  
    {  
    }  
    }  
    }  
    }  
    //隐藏进度条  
    //tempProgressBar.Visible = false;  
    //保存文件  
    objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
    Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,  
    Missing.Value, Missing.Value);  
    }  
    catch (Exception error)  
    {  
    MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
    return;  
    }  
    finally  
    {  
    //关闭Excel应用  
    if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);  
    if (objExcel.Workbooks != null) objExcel.Workbooks.Close();  
    if (objExcel != null) objExcel.Quit();  
    objsheet = null;  
    objWorkbook = null;  
    objExcel = null;  
    }  
    MessageBox.Show(fileNameString "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    }  

4.用模版的方式导出

    /// <summary>  
            /// 导出到Excel  
            /// </summary>  
            /// <param name="sender" />  
            /// <param name="e" />  
            private void 导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e)  
            {  
      
                string excelPath = null;  
     
                #region  获取Excel保存路径  
                //using (FolderBrowserDialog myDialog = new FolderBrowserDialog())  
                //{  
                //    myDialog.ShowNewFolderButton = false;  
      
                //    myDialog.Description = "请选择要保存文件夹或目录";  
      
                //    if (myDialog.ShowDialog() == DialogResult.OK)  
                //    {  
      
                //        excelPath = myDialog.SelectedPath + "\\股东清册.xls";  
                //    }  
                //}  
                 
                #endregion  
     
                #region  选择Excel模板  
      
                using (OpenFileDialog openFile = new OpenFileDialog())  
                {  
                    openFile.Title = "请选择Excel文件模板";  
                    openFile.Filter = "Excel 97-2003 文件模板|*.xls|Excel 2007-2010 文件模板|*.xlsx";  
                    openFile.CheckFileExists = true;  
                    openFile.CheckPathExists = true;  
                    if (openFile.ShowDialog(this) == DialogResult.OK)  
                    {  
                        excelPath = openFile.FileName;  
                        //MessageBox.Show(openFile.FileName, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
                    }  
                    else  
                    {  
                        MessageBox.Show("未选择Excel文件模板!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
                    }  
                }  
                #endregion  
      
                System.Data.DataTable dt = new System.Data.DataTable();  
                DataColumn column;  
                DataRow row;  
     
                #region  
                //column = new DataColumn();  
                //column.ColumnName = "序号";  
                //column.DataType = typeof(string);  
                //dt.Columns.Add(column);  
                //DataColumn column;  
      
                //    row = dt.NewRow();  
                //    row["序号"] = i + 1;  
                //    row["股权托管证编号"] = dtStock.Rows[i]["trustee_id"].ToString();  
                //    row["股东帐号"] = dtStock.Rows[i]["stock_account"].ToString();  
                //    row["股东名称"] = dtStock.Rows[i]["customer_name"].ToString();  
                //    row["股权类别"] = dtStock.Rows[i]["stock_bonus_type_name"].ToString();  
                //    row["股权数量"] = dtStock.Rows[i]["begin_amount"].ToString();  
                //    row["联系地址"] = dtStock.Rows[i]["address"].ToString();  
                //    row["邮编"] = dtStock.Rows[i]["zip"].ToString();  
                //    row["电话"] = dtStock.Rows[i]["phone"].ToString();  
      
                //    dt.Rows.Add(row);  
                #endregion  
      
                for (int j = 0; j < dataGridView1.ColumnCount; j++)  
                {  
                    if (dataGridView1.Columns[j].Visible == true)  
                    {  
                        column = new DataColumn();  
                        column.ColumnName = dataGridView1.Columns[j].HeaderText;  
                        column.DataType = typeof(string);  
                        dt.Columns.Add(column);  
                    }  
                }  
      
                for (int i = 0; i < dataGridView1.RowCount; i++)  
                {  
                    row = dt.NewRow();  
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)  
                    {  
                        if (dataGridView1.Columns[j].Visible == true)  
                        {  
                            row[dataGridView1.Columns[j].HeaderText] = dataGridView1.Rows[i].Cells[j].Value;  
                        }  
                    }   
                    dt.Rows.Add(row);  
                }  
      
                DataTableToExcel(dt, excelPath);  
      
            }  
      
            /// <summary>  
            /// 将Datatable导出Excel时新建Excel  
            /// </summary>  
            /// <param name="dt" />  
            /// <param name="excelPath" />  
            /// <returns></returns>  
            public static bool DataTableTonewExcel(System.Data.DataTable dt, string excelPath)  
            {  
                try  
                {  
                    object missing = System.Reflection.Missing.Value;  
                    Excel.Application app = new Excel.ApplicationClass();  
                    app.Application.Workbooks.Add(true);  
                    Excel.Workbook newExcel = (Excel.Workbook)app.ActiveWorkbook;  
                    Excel.Worksheet newSheet = (Excel.Worksheet)newExcel.ActiveSheet;  
      
                    int row = dt.Rows.Count;  
                    int column = dt.Columns.Count;  
                    for (int i = 0; i < row; i++)  
                    {  
                        for (int j = 0; j < column; j++)  
                        {  
                            newSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();  
                        }  
                    }  
      
                    for (int i = 0; i < column; i++)//设置Sheet的列名  
                    {  
                        newSheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;  
                    }  
      
                    newExcel.Save();  
                    app.Cells.Font.Name = "宋体"; //字体  
                    app.Cells.Font.Size = 9; //字号                 
      
                    //保存excel文件  
                    newExcel.SaveCopyAs(excelPath);  
                    //关闭文件  
                    newExcel.Close(false, missing, missing);  
                    //退出excel  
                    app.Quit();  
      
                    return true;  
                }  
                catch  
                {  
                    return false;  
                }  
                finally  
                {  
                    KillExcel();  
                }  
            }  
      
            /// <summary>  
            /// 将DataTable导出到指定路径Excel模板  
            /// </summary>  
            /// <param name="dt" />  
            /// <param name="excelPath" />  
            /// <returns></returns>  
            public static bool DataTableToExcel(System.Data.DataTable dt, string excelPath)  
            {  
                try  
                {  
                    Excel.Application app = new Excel.Application();  
      
                    app.Visible = true;  
      
                    Excel.Workbook myExcel = app.Workbooks.Open(excelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
                                             Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
                                             Missing.Value, Missing.Value);  
                    Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.Worksheets[1];  
      
                    int row = dt.Rows.Count;  
                    int column = dt.Columns.Count;  
                    for (int i = 0; i < row; i++)  
                    {  
                        for (int j = 0; j < column; j++)  
                        {  
                            mySheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();  
                        }  
                    }  
      
                    for (int i = 0; i < column; i++)//设置Sheet的列名  
                    {  
                        mySheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;  
                    }  
      
      
                    myExcel.Save();  
      
                    app.Cells.Font.Name = "宋体"; //字体  
                    app.Cells.Font.Size = 9; //字号  
                    app.DisplayAlerts = false;  
                    //覆盖时不提示  
                    app.AlertBeforeOverwriting = false;  
      
      
                    app.SaveWorkspace(excelPath);  
                    app.Quit();  
                    app = null;  
      
      
                    return true;  
                }  
                catch  
                {  
                    return false;  
                }  
      
                finally  
                {  
                    KillExcel();  
                }  
            }  
      
            /// <summary>  
            /// 关闭Excel进程  
            /// </summary>  
            private static void KillExcel()  
            {  
                System.Diagnostics.Process[] pro = System.Diagnostics.Process.GetProcessesByName("Excel");  
                foreach (System.Diagnostics.Process killpro in pro)  
                {  
                    killpro.Kill();  
                }  
            }  


阅读更多
上一篇JAVA面试问题
下一篇个人电脑安全设置防止黑吃黑
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭