C# 汇出功能

 

一：通过流方式导出Excel，这方法导出 .xlsx 格式的 Excel 文件时，没办法打开。导出 .xls 格式的，会提示文件格式和扩展名不匹配，不建议使用。

private void btnExport_Click(object sender, EventArgs e)
        {
            //gv是datagirdview name
            if (gv.Rows.Count == 0)
                return;

            SaveFileDialog s = new SaveFileDialog();
            s.Title = "Save";
            s.Filter = "EXECL (*.xls) |*.xls |All files(*.*) |*.*";
            s.FilterIndex = 1;

            if (s.ShowDialog() != DialogResult.OK)
                return;

            string filename = s.FileName;
            if (File.Exists(s.FileName))
                File.Delete(s.FileName);

            FileStream fs = new FileStream(s.FileName, FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, Encoding.GetEncoding("GB2312"));

            DataTable dt = new DataTable();
            dt.Columns.Add("PART_NO");
            dt.Columns.Add("SPEC1");
            dt.Columns.Add("RULE_NAME");
            dt.Columns.Add("TEMPLATE");
            dt.Columns.Add("EMP_NAME");
            dt.Columns.Add("CREATE_TIME");
            dt.Columns.Add("LABEL_TYPE");
            dt.Columns.Add("COLUMN_TYPE");
            dt.Columns.Add("ZPL");

            string strLine = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strLine += dt.Columns[i].ToString() + Convert.ToChar(9);
            }
            sw.WriteLine(strLine);

            //获取指定列data
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                dr["PART_NO"] = gv.Rows[i].Cells[2].Value;
                dr["SPEC1"] = gv.Rows[i].Cells[3].Value;
                dr["RULE_NAME"] = gv.Rows[i].Cells[4].Value;
                dr["TEMPLATE"] = gv.Rows[i].Cells[5].Value;
                dr["EMP_NAME"] = gv.Rows[i].Cells[6].Value;
                dr["CREATE_TIME"] = gv.Rows[i].Cells[7].Value;
                dr["LABEL_TYPE"] = gv.Rows[i].Cells[8].Value;
                dr["COLUMN_TYPE"] = gv.Rows[i].Cells[9].Value;
                dr["ZPL"] = gv.Rows[i].Cells[10].Value;
                dt.Rows.Add(dr);
            }

            //写入数据
            strLine = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                        strLine = strLine + " " + Convert.ToChar(9);
                    else
                    {
                        string rowstr = "";
                        rowstr = dt.Rows[i][j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                sw.WriteLine(strLine);
                strLine = "";
            }

            sw.Close();
            fs.Close();       
        }

二：导出数据到已有模板里。

            SaveFileDialog sf = new SaveFileDialog();
            sf.DefaultExt = "xlsx";
            sf.Filter = "All Files(*.xlsx)|*.xlsx";
            if (sf.ShowDialog() != DialogResult.OK)
                return;

            string sFileName = sf.FileName;
            if (File.Exists(sFileName))
            {
                File.Delete(sFileName);
            }
            File.Copy(System.Windows.Forms.Application.StartupPath + @"\测试文件.xlsx", sFileName, true);
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            if (app == null)
            {
                MessageBox.Show("未检测到安装Excel软件！", "ERROR");
                return;
            }
            //取消另存为警告提示
            app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;

            app.Visible = true;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(sFileName); //加载模板
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
            Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作薄。
            if (worksheet == null)
                return;

            //写入数据，Excel索引从1开始。
            for (int i = 1; i <= dgvData.Rows.Count; i++)
            {
                int row_ = 5 + i;  //Excel模板上表头和标题行占了5行,根据实际模板需要修改;
                //j表示数据的列数
                for (int j = 1; j < 11; j++)
                {
                    worksheet.Cells[row_, j] = dgvData.Rows[i - 1].Cells[j - 1].Value;
                }
            }

            Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A1", worksheet.Cells[dgvData.Rows.Count + 5, 8]);
            //rg.Borders.LineStyle = 1; //单元格加边框。
            worksheet.Columns.AutoFit(); //自动调整列宽。
            if (worksheet != null)
            {
                worksheet.SaveAs(sFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                app.Quit();
            }