C#在Excel的簡單操作--適用：與DB數據的簡單交互

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;


namespace WmsClient
{
    public class ExcelHelper
    {
        //Excel導入DataSet
        public static DataSet ExcelToDataSet()
        {
            DataSet dataSet = new DataSet();
            OpenFileDialog OFD = new OpenFileDialog();
            OFD.Filter = "Excel Files(.xls)|*.xls|All Files(*.*)|*.*";
            if (OFD.ShowDialog() == DialogResult.OK)   
            {
                string strFileName = OFD.FileName;
                dataSet = ExcelToDataSet(strFileName);
            }
            return dataSet;
        }
        //Excel導入DataSet
        public static DataSet ExcelToDataSet(string filePath)
        {
            if (!File.Exists(filePath))
                throw new FileNotFoundException("文件不存在");
            bool isExcel2003 = filePath.EndsWith(".xls");
            string connectionString = string.Format(
                isExcel2003
                ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;"
                : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"",
                filePath);
            DataSet ds = new DataSet();
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                string sheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim(); ;
                string commandText = "SELECT  * FROM [" + sheetName + "]";
                using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
                {
                    da.Fill(ds);
                    connection.Close();
                }
            }
            return ds;
        }
        //DataGridView導出Excel
        public static void DataGridViewToExcel(DataGridView dgv)
        {
            //DataTableToExcel(dgv.DataSource as DataTable);//數據源為DataTable時適用

            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.Title = "保存為Excel文件";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.Default);
                string columnTitle = "";
                try
                {
                    //寫入標題列 
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (dgv.Columns[i].Visible)
                        {
                            if (columnTitle == "")
                            {
                                columnTitle = "\"" + dgv.Columns[i].HeaderText + "\"";
                            }
                            else
                            {
                                columnTitle += "\t" + "\"" + dgv.Columns[i].HeaderText + "\"";
                            }
                        }
                    }
                    sw.WriteLine(columnTitle);

                    //寫入內容列 
                    for (int j = 0; j < dgv.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int i = 0; i < dgv.Columns.Count; i++)
                        {
                            if (dgv.Columns[i].Visible)
                            {
                                string cellValue = dgv.Rows[j].Cells[i].Value == null ? "" : dgv.Rows[j].Cells[i].FormattedValue.ToString().Replace("\"", "'");
                                if (columnValue == "")
                                {
                                    columnValue = "\"" + cellValue + "\"";
                                }
                                else
                                {
                                    columnValue += "\t" + "\"" + cellValue + "\"";
                                }
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }
        //DataTable導出Excel
        public static void DataTableToExcel(DataTable table)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.Title = "保存為Excel文件";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.Default);
                string columnTitle = "";
                try
                {
                    //寫入標題列 
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (columnTitle == "")
                        {
                            columnTitle = "\"" + table.Columns[i].ColumnName + "\"";
                        }
                        else
                        {
                            columnTitle += "\t" + "\"" + table.Columns[i].ColumnName + "\"";
                        }
                    }
                    sw.WriteLine(columnTitle);

                    //寫入內容列  
                    for (int j = 0; j < table.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            string cellValue = table.Rows[j][i] == DBNull.Value ? "" : table.Rows[j][i].ToString().Replace("\"", "'");
                            if (columnValue == "")
                            {
                                columnValue = "\"" + cellValue + "\"";
                            }
                            else
                            {
                                columnValue += "\t" + "\"" + cellValue + "\"";
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }
    }
}
 

 

转载于:https://www.cnblogs.com/kuangxiangnice/p/4384170.html