C#导入程序

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Aspose.Cells;
using AsposeExcel;
using DB = DBUtility.Data.DbHelper;

namespace AsposeExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable DT = new DataTable();
        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;
            }


        }
        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 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 ;
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    // DataColumns = "";
                    DataValues = "";

                    for (int j = 0; j < DT.Columns.Count; j++)
                    {
                        //if (DataColumns == "")
                        //    DataColumns += DT.Columns[j].ColumnName;
                        //else
                        //    DataColumns += "," + DT.Columns[j].ColumnName;\

                        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 == 7) 
                        {
                            SaleNo = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                        }
                    }
                    Cell10 = "";
                    Cell11 = "";
                    sql = "INSERT INTO OutReport_Out (OgInPlanNo,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 + "' ";
                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);
                    }
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e) {

            List<string> list = new List<string>() { };


            string SaleNo = "";
            for (int i = 0; i < DT.Rows.Count; i++)
            {


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

                    if (j == 7)
                    {
                        SaleNo = DT.Rows[i][DT.Columns[j].ColumnName].ToString();
                    }
                }
                string SelectSql = " DELETE FROM OutReport_Out Where SaleNo='" + SaleNo + "' ";
                list.Add(SelectSql);

            }

            int l = DB.GetDAO(DB.DbTypes.SqlServer, Class1.sqlcon1).ExecuteSqlTran(list);
            if (list.Count > 0)
            {
                if (list.Count <= l)
                {
                    MessageBox.Show("删除成功！请重新导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    MessageBox.Show("删除失败，请先导入文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("删除失败，请先导入文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }


        }
    }
}