Excel导入程序 C#

利用Aspose.cell dll 来导入程序！调用方法！

 public DataTable ExcelToDatatalbe(string filename)//导入  
        {
            Workbook book = new Workbook();
            book.Open(filename);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中  
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
            // dt_Import.  
            return dt_Import;
        }

点击弹出获取文件的框

   private void btnOpen_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                DataTable dt = new DataTable();
                dt = ExcelToDatatalbe(openFileDialog1.FileName);
                dataGridView1.DataSource = dt;
                DT = dt;
            }


        }

遍历所有的数据，然后点击导入文件

private void btnIn_Click(object sender, EventArgs e)
        {

            if (DT.Rows.Count <= 0)
            {
                MessageBox.Show("请选择文件", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Hand);
            }
            else
            {
                List<string> list = new List<string>() { };
                string sql = "";
                //  string DataColumns = "";
                string DataValues = "";
                //string OgInPlanNo = "";
                //string ProjectName = "";
                string SaleNo = "";
                string  Cell10="";
                string Cell11="" ;
                double Cell12 ;
                string OgInPlanNo = "";
                for (int i = 0; i < DT.Rows.Count; i++)
                {

                    DataValues = "";

                    for (int j = 0; j < DT.Columns.Count; j++)
                    {


                        if (DataValues == "")
                        {
                            DataValues += "'" + DT.Rows[i][DT.Columns[j].ColumnName] + "'";
                        }
                        else
                        {
                            if (j==4||j == 10 || j == 11 || j == 12 || j == 13 || j == 14||j==16||j==17)
                            {

                                    if (j == 4) {
                                        if (DT.Rows[i][DT.Columns[j].ColumnName].ToString() == "")
                                        {
                                            DataValues += "," + "NULL";
                                        }
                                        else {
                                            DataValues += "," + "'" + DT.Rows[i][DT.Columns[j].ColumnName] + "'";
                                        }
                                    }
                                    else if (j == 10) 
                                    {
                                        Cell10 = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                                        DataValues += "," + "'" + Cell10 + "'";
                                    }
                                    else if (j == 11) 
                                    {
                                        Cell11 = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                                        DataValues += "," + "'" + Cell11 + "'";
                                    }
                                    else if (j == 12)
                                    {
                                        Cell12 = Convert.ToDouble(Cell11) - Convert.ToDouble(Cell10);
                                        DataValues += "," + "'" + Cell12 + "'";
                                    }
                                    else if (DT.Rows[i][DT.Columns[j].ColumnName].ToString() == "")
                                    {

                                        object Number = DT.Rows[i][DT.Columns[j].ColumnName];
                                        Number = 0;
                                        DataValues += "," + "'" + Number + "'";
                                    }
                                    else
                                    {
                                        DataValues += "," + "'" + DT.Rows[i][DT.Columns[j].ColumnName] + "'";
                                    }

                            }
                            else {
                                DataValues += "," + "'" + DT.Rows[i][DT.Columns[j].ColumnName] + "'";
                            }


                        }
                        if (j == 0) {
                            OgInPlanNo = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                        }
                        if (j == 7) 
                        {
                            SaleNo = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                        }
                    }
                    Cell10 = "";
                    Cell11 = "";
                    sql = "INSERT INTO OutReport_Out (OgInPlanNo,PlanType,ProjectName,CusCheck,TaxCheck,TaxTime,PickPlace,PickTime,SaleNo,TransportNo,CusName,PickWeight,EndWeight,ProfitLose,SinglePrice,SumPrice,Scale,C3Amount,C4Amount,InTime,OutTime,Rem) VALUES (" + DataValues + ")";
                    list.Add(sql);
                }
                string SelectSql = "select *from OutReport_Out Where SaleNo='" + SaleNo + "'and OgInPlanNo='" + OgInPlanNo + "'";
                DataSet ls= DB.GetDAO(DB.DbTypes.SqlServer, Class1.sqlcon1).Query(SelectSql);
                if (ls.Tables[0].Rows.Count > 0)
                {
                    MessageBox.Show("重复导入数据,请删除重复数据后导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    int count = DB.GetDAO(DB.DbTypes.SqlServer, Class1.sqlcon1).ExecuteSqlTran(list);
                    if (list.Count > count)
                    {
                        MessageBox.Show("导入失败!请检查单元格格式！例如:数字列不能为空，填0;日期不能是数字", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    else
                    {
                        MessageBox.Show("导入成功！请回网站页面刷新后查看!", "提示：", MessageBoxButtons.OK);
                    }
                }
            }
        }