基本信息

---

下载

   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

---

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：78630559

本次赞助数额为： 2 元　

支付宝支付

微信支付

   源码介绍

---

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 System.Data.OleDb;
using System.Collections;
using System.Threading;

namespace zjr
{
    public partial class Form10 : Form
    {
        public Form10()
        {
            InitializeComponent();
        }

        private void Form10_Load(object sender, EventArgs e)
        {

        }


        private void Btn_导入_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel (*.xls, *.xlsx)|*.xls;*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable dt = XlsToDataTable(ofd.FileName);
            }
        }



        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel (*.xls, *.xlsx)|*.xls;*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    //Load selected Excel File
                    c1FlexGrid1.DataSource = null;
                    c1FlexGrid1.LoadExcel(ofd.FileName);
                    DataTable dt = c1FlexGrid1.DataSource as DataTable;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to open Excel file: "   ex.Message);
                }
            }
            for (int i = 0; i < c1FlexGrid1.Rows.Count; i  )
            {

            }
        }
        private DataTable XlsToDataTable(String strpath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"  
                "Data Source="   strpath   ";"  
                "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            DataTable sheetNames = conn.GetOleDbSchemaTable
           (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }


            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [历史版本$]", strConn);
            DataTable dt = new DataTable();

            myCommand.Fill(dt);
            dt.Columns[7].ColumnName = "列名";
            dt.Columns[8].ColumnName = "第一天";
            dt.Columns[9].ColumnName = "第二天";
            dt.Columns[10].ColumnName = "第三天";
            dt.Columns[11].ColumnName = "第四天";
            dt.Columns[12].ColumnName = "第五天";
            dt.Columns[13].ColumnName = "第六天";
            dt.Columns[14].ColumnName = "第七天";
            dt.Columns[15].ColumnName = "ALL";
            int all = 0;
            int sum = 0;
            string line = "";
            string pro = "";
            string pronum = "";
            string WORK = "";
            string user = "";
            foreach (DataRow item in dt.Rows)
            {
                if (item["列名"].ToString() == "实际数量")
                {
                    item["第一天"] = 200;
                    item["第二天"] = 200;
                    item["第三天"] = 200;
                    item["第四天"] = 200;
                    item["第五天"] = 200;
                    item["第六天"] = 200;
                    item["第七天"] = 200;
                }
            }
            int a = 0;
            foreach (DataRow item in dt.Rows)
            {
                sum  ;
                if (item["生产线"].ToString() != "")
                {
                    line = item["生产线"].ToString();
                }
                if (item["产品编号"].ToString() != "")
                {
                    pro = item["产品编号"].ToString();
                }
                if (item["初始库存"].ToString() != "")
                {
                    pronum = item["初始库存"].ToString();
                }

                if (item["列名"].ToString() == "发货数量")
                {

                    all = sum - all;
                    for (int i = 1; i < all; i  )
                    {
                        dt.Rows[sum - all   i]["生产线"] = line;
                        dt.Rows[sum - all   i]["产品编号"] = pro;
                        dt.Rows[sum - all   i]["初始库存"] = pronum;
                        if (dt.Rows[sum - all   1]["工作中心"].ToString() != "" && dt.Rows[sum - all   1]["列名"].ToString() == "库存数量")
                        {
                            dt.Rows[sum - all   i]["工作中心"] = dt.Rows[sum - all   1]["工作中心"].ToString();
                            dt.Rows[sum - all   i]["客户名称"] = dt.Rows[sum - all   1]["客户名称"].ToString();
                        }
                        else
                        {
                            if (dt.Rows[sum - all   i]["工作中心"].ToString() != "" && dt.Rows[sum - all   1]["列名"].ToString() == "计划数量")
                            {
                                WORK = dt.Rows[sum - all   i]["工作中心"].ToString();
                                user = dt.Rows[sum - all   i]["客户名称"].ToString();
                            }
                            dt.Rows[sum - all   i]["工作中心"] = WORK;
                            dt.Rows[sum - all   i]["客户名称"] = user;
                        }
                    }
                    if (a > 0)
                    {
                        dt.Rows[sum - all]["第一天"] = dt.Rows[sum - all   2]["初始库存"].ToString();
                    }
                    else
                    {
                        dt.Rows[sum - all   1]["第一天"] = dt.Rows[sum - all   2]["初始库存"].ToString();
                    }
                    for (int i = 8; i < 15; i  )
                    {
                        int shuliang1 = 0;
                        int b = 0;
                        if (a > 0)
                        {
                            b = 0;
                        }
                        else
                        {
                            b = 1;
                        }
                        for (int j = b; j < all; j  )
                        {
                            if (dt.Rows[sum - all   j]["列名"].ToString() == "库存数量")
                            {
                                if (dt.Rows[sum - all   j][i].ToString() != "")
                                {
                                    shuliang1 = Convert.ToInt16(dt.Rows[sum - all   j][i]);
                                }
                            }
                            if (dt.Rows[sum - all   j]["列名"].ToString() == "实际数量")
                            {
                                if (dt.Rows[sum - all   j][i].ToString() != "")
                                {
                                    shuliang1 = shuliang1   Convert.ToInt16(dt.Rows[sum - all   j][i]);
                                }
                            }
                            if (dt.Rows[sum - all   j]["列名"].ToString() == "发货数量")
                            {
                                if (dt.Rows[sum - all   j][i].ToString() != "")
                                {
                                    shuliang1 = shuliang1 - Convert.ToInt16(dt.Rows[sum - all   j][i]);
                                }
                            }
                        }
                        for (int j = b; j < all; j  )
                        {
                            if (dt.Rows[sum - all   j]["列名"].ToString() == "计划数量")
                            {
                                int all1 = 0;
                                for (int k = 8; k < 15; k  )
                                {
                                    all1 = all1   Convert.ToInt16(dt.Rows[sum - all   j][k]);
                                }
                                dt.Rows[sum - all   j]["ALL"] = all1;
                            }
                            if (dt.Rows[sum - all   j]["列名"].ToString() == "实际数量")
                            {
                                int all1 = 0;
                                for (int k = 8; k < 15; k  )
                                {
                                    all1 = all1   Convert.ToInt16(dt.Rows[sum - all   j][k]);
                                }
                                dt.Rows[sum - all   j]["ALL"] = all1;
                            }
                        }
                        if (i   1 < 15)
                        {
                            if (a > 0)
                            {
                                dt.Rows[sum - all][i   1] = shuliang1;
                            }
                            else
                            {
                                dt.Rows[sum - all   1][i   1] = shuliang1;
                            }
                        }
                    }
                    a  ;
                }
            }
            DataTable dtMain = new DataTable();
            dtMain.Columns.Add("生产线", typeof(string));
            dtMain.Columns.Add("产品编号", typeof(string));
            dtMain.Columns.Add("初始库存", typeof(string));
            dtMain.Columns.Add("第几周", typeof(string));
            DataTable dtSub1 = new DataTable();
            dtSub1.Columns.Add("工作中心", typeof(string));
            dtSub1.Columns.Add("日期", typeof(string));
            dtSub1.Columns.Add("库存数量", typeof(string));
            dtSub1.Columns.Add("实际数量", typeof(string));
            dtSub1.Columns.Add("计划数量", typeof(string));
            dtSub1.Columns.Add("发货数量", typeof(string));
            dtSub1.Columns.Add("客户名称", typeof(string));
            List<string> list = new List<string>();
            foreach (DataRow item in dt.Rows)
            {
                if (item["生产线"].ToString() != "")
                {
                    if (!list.Contains(item["生产线"].ToString()))
                    {
                        list.Add(item["生产线"].ToString());
                        DataRow dr = dtMain.NewRow();
                        dr["生产线"] = item["生产线"].ToString();
                        dr["产品编号"] = item["产品编号"].ToString();
                        //dr["客户名称"] = item["客户名称"].ToString();
                        dr["初始库存"] = item["初始库存"].ToString();
                        dr["第几周"] = "CW1";
                        dtMain.Rows.Add(dr);
                        List<string> list1 = new List<string>();
                        DataRow[] drs = dt.Select("生产线='"   item["生产线"].ToString()   "'");
                        foreach (DataRow item1 in drs)
                        {
                            if (!list1.Contains(item1["工作中心"].ToString()))
                            {
                                list1.Add(item1["工作中心"].ToString());
                            }
                            for (int j = 8; j < 15; j  )
                            {

                            }
                        }
                        list1.Remove("");
                        DataTable dtSub = dtSub1.Copy();
                        if (list1.Count == 1)
                        {
                            for (int i = 0; i < list.Count * 7; i  )
                            {
                                DataRow dr1 = dtSub.NewRow();
                                dtSub.Rows.Add(dr1);
                            }

                            for (int i = 0; i < 4; i  )
                            {
                                for (int j = 8; j < 15; j  )
                                {
                                    dtSub.Rows[j - 8][i   2] = drs[i][j].ToString();
                                    dtSub.Rows[j - 8]["工作中心"] = list1[0].ToString();
                                    dtSub.Rows[j - 8]["日期"] = dt.Rows[0][j].ToString();
                                    dtSub.Rows[j - 8]["客户名称"] = dt.Rows[1]["客户名称"].ToString();
                                }
                            }
                        }
                        else if (list1.Count > 1)
                        {
                            for (int i = 0; i < list1.Count; i  )
                            {
                                DataRow[] drs1 = dt.Select("工作中心='"   list1[i].ToString()   "'");
                                if (drs1.Length > 0)
                                {
                                    for (int l = 0; l < 7; l  )
                                    {
                                        DataRow dr1 = dtSub.NewRow();
                                        dtSub.Rows.Add(dr1);
                                    }
                                    for (int k = 0; k < 2; k  )
                                    {
                                        for (int j = 8; j < 15; j  )
                                        {
                                            dtSub.Rows[j - 8   i * 7][k   3] = drs1[k][j].ToString();
                                            dtSub.Rows[j - 8   i * 7]["工作中心"] = list1[i].ToString();
                                            dtSub.Rows[j - 8   i * 7]["日期"] = dt.Rows[0][j].ToString();
                                            dtSub.Rows[j - 8   i * 7]["库存数量"] = drs[0][j].ToString();
                                            dtSub.Rows[j - 8   i * 7]["发货数量"] = drs[drs.Length - 1][j].ToString();
                                            dtSub.Rows[j - 8   i * 7]["客户名称"] = drs1[k]["客户名称"].ToString();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return dt;
        }

        private string ToDateTimeValue(string strNumber)
        {
            if (!string.IsNullOrWhiteSpace(strNumber))
            {
                Decimal tempValue;
                //先检查 是不是数字;
                if (Decimal.TryParse(strNumber, out tempValue))
                {
                    //天数,取整
                    int day = Convert.ToInt32(Math.Truncate(tempValue));
                    //这里也不知道为什么. 如果是小于32,则减1,否则减2
                    //日期从1900-01-01开始累加 
                    // day = day < 32 ? day - 1 : day - 2;
                    DateTime dt = new DateTime(1900, 1, 1).AddDays(day < 32 ? (day - 1) : (day - 2));

                    //小时:减掉天数,这个数字转换小时:(* 24) 
                    Decimal hourTemp = (tempValue - day) * 24;//获取小时数
                    //取整.小时数
                    int hour = Convert.ToInt32(Math.Truncate(hourTemp));
                    //分钟:减掉小时,( * 60)
                    //这里舍入,否则取值会有1分钟误差.
                    Decimal minuteTemp = Math.Round((hourTemp - hour) * 60, 2);//获取分钟数
                    int minute = Convert.ToInt32(Math.Truncate(minuteTemp));
                    //秒:减掉分钟,( * 60)
                    //这里舍入,否则取值会有1秒误差.
                    Decimal secondTemp = Math.Round((minuteTemp - minute) * 60, 2);//获取秒数
                    int second = Convert.ToInt32(Math.Truncate(secondTemp));

                    //时间格式:00:00:00
                    string resultTimes = string.Format("{0}:{1}:{2}",
                            (hour < 10 ? ("0"   hour) : hour.ToString()),
                            (minute < 10 ? ("0"   minute) : minute.ToString()),
                            (second < 10 ? ("0"   second) : second.ToString()));

                    if (day > 0)
                        return string.Format("{0} {1}", dt.ToString("yyyy-MM-dd"), resultTimes);
                    else
                        return resultTimes;
                }
            }
            return string.Empty;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //string a = ToDateTimeValue("42205");
            string str = "2007年12月8日";
            DateTime dt = Convert.ToDateTime(str);
            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");
            string res = dt.ToString("m").Substring(0,6);
        }

        public ArrayList ExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   filepath   ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string a = "CW30_V1.0.XLSX";
            string [] f = a.Split('_');
            string B = f[0];
            string[] b = f[1].Split('.');


            string C = b[0]   "."   b[1];
           
        }
    }
}