用C#开发读取EXCEL内容存储到mysql数据库的软件,你们都是怎么做的?我想借鉴一些前辈的经验.

[幸福的奶牛]

如题:用C#开发读取EXCEL内容存储到mysql数据库的软件,你们都是怎么做的?我想借鉴一些前辈的经验.

[晨易夕]

还是用EPPlus吧。

using OfficeOpenXml;

private static readonly string[] a_z = 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" };

/// <summary>
        /// 导入excel
        /// </summary>
        /// <param name="colCount">列数</param>
        /// <returns>int行  string[]列</returns>
public static SortedList<int,string[]> GetDataFormExcel(int colCount)
    {
        SortedList<int,string[]> list = new SortedList<int,string[]>();
        System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog();
        openFileDialog.Filter = "Excel|*.xlsx|Excel|*.xls";
        openFileDialog.Title = "选择需要导入的Excel文件";
        openFileDialog.InitialDirectory = System.Windows.Forms.Application.StartupPath+ "\\Excel";
        if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK
            && !string.Empty.Equals(openFileDialog.FileName))
        {
            using (ExcelPackage ep = new ExcelPackage(new FileInfo(openFileDialog.FileName)))
            {
                ExcelWorksheet ws = ep.Workbook.Worksheets[1];
                int i = 2;
                while (true)
                {
                    try
                    {
                        string[] args = new string[colCount];
                        for (int j = 1; j <= colCount; j++)
                        {
                            string filePos = string.Empty;
                            filePos = a_z[j-1];
                            args[j-1] = ws.Cells[filePos + i.ToString()].Value.ToString();
                        }
                        list.Add(i - 2, args);
                        i++;
                    }
                    catch
                    {
                        break;
                    }
                }
            }
        }
        return list;
    }