Import Excel void (NPOI)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace listsjk
{
    class 把dataGridView数据导入excel
    {

    }
     private void ExportExcel(string banname, string fileName, DataGridView dataGridView1)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            //打开对话框
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消 
            //新建excel文件对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.get_Range("A1", "A2").ColumnWidth = 7;
            //设置excel单元格格式
            string[] abc = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                //范围内单元格文本居中显示
                worksheet.get_Range("A3", abc[dataGridView1.Columns.Count - 1] + Convert.ToString(dataGridView1.Rows.Count + 5)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //范围内单元格合并
                worksheet.get_Range(abc[0] + Convert.ToString(1), abc[j] + Convert.ToString(3)).Merge(worksheet.get_Range(abc[j] + Convert.ToString(1), abc[j] + Convert.ToString(3)).MergeCells);
                //excel.Cells[abc[0] + Convert.ToString(1), abc[j] + Convert.ToString(3)] = "装港新增加一个工作簿";
                //范围内单元格宽度
                worksheet.get_Range(abc[j + 1] + Convert.ToString(1), abc[j] + Convert.ToString(1)).ColumnWidth = 18;
                //worksheet.get_Range(abc[0] + Convert.ToString(1), abc[0] + Convert.ToString(2)).Merge(worksheet.get_Range(abc[0] + Convert.ToString(1), abc[0] + Convert.ToString(2)).MergeCells);
                //    //worksheet.get_Range("B1", "B2").Merge(worksheet.get_Range("B2", "B2").MergeCells);
            }

            worksheet.Cells[1, 1] = banname ;
            //MessageBox.Show(abc[0] + Convert.ToString(0 + 1), abc[0] + Convert.ToString(0 + 2));
            //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错     
            //excel.Application.Workbooks.Add(true);
            
            //生成Excel中列头名称    
            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                excel.Cells[4, i + 1] = dataGridView1.Columns[i].HeaderText;
            }
            //把DataGridView当前页的数据保存在Excel中     
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    if (dataGridView1[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 5, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 5, j + 1] = dataGridView1[j, i].Value.ToString();
                    }
                }
            }

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                }

            }
            //else
            //{
            //    fileSaved = false;
            //}
            excel.Quit();
            GC.Collect();//强行销毁 
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
            MessageBox.Show(fileName + "的统计信息保存成功", "提示", MessageBoxButtons.OK);
        }
}

  

posted @ 2018-08-06 22:41  ZaraNet  阅读(214)  评论(0编辑  收藏  举报