C#导入程序

来源：互联网发布：为什么四川美女多知乎编辑：程序博客网时间：2024/05/22 08:10
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);            }        }    }}
0 0