博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

[原]用c#操纵excel,500大圆

Posted on 2006-12-28 15:02  fanciex  阅读(589)  评论(1编辑  收藏  举报
前两天一个long long ago的客户的助理MM通过MSN联系到我,楚楚可怜(用了很多MSN表情)的说有个很紧急的事,让我帮她个忙。原来他们公司年终盘点,有8k多种产品,每种产品要生成一个盘点单(excel sheet)。产品列表也在一个叫goods.xsl的excel表中。这位助理MM会点宏,可惜功底太弱,做不来。
我本就乐于助人,现又不禁怜香惜玉起来,正要满口答应的时候,mm又说:"老大说了,这会给报酬的哦!",我喜出望外,然后商定完成后给我500大圆。ok,我在网上Ctrl+c,Ctrl +v了一通,做成了下面这个值500大圆的代码。

 class Program
    {
        static void Main(string[] args)
        {
            PrintFilledCard();
        }

        private static void PrintFilledCard()
        {

            Console.Write("开始号:");
            string stmp = Console.ReadLine();
            int start = int.Parse(stmp);
            Console.Write("结束号:");
            stmp = Console.ReadLine();
            int end = int.Parse(stmp);

            Console.WriteLine("Waitting...");

            string cardFileName = @"D:\Code\ExcelAuto\Cards.xls";
            string goodsFileName = @"D:\Code\ExcelAuto\Goods.xls";
            string saveFileName = @"D:\Code\ExcelAuto\Cards1.xls";

            object missing = System.Reflection.Missing.Value;
            Excel.Application ThisApplication = new ApplicationClass();

            Workbook workBookTemplate = null;
            Workbook workBookGoods = null;

            try
            {
                //加载产品文件
                workBookGoods = ThisApplication.Workbooks.Open(goodsFileName, missing, missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing);
                Worksheet sheetGoods = (Worksheet)workBookGoods.Sheets[1];

                //加载Excel模板文件
                workBookTemplate = ThisApplication.Workbooks.Open(cardFileName, missing, missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing);
                Worksheet sheetTemplate = (Worksheet)workBookTemplate.Sheets[1];
                ThisApplication.Visible = false;

                int index = start + 1;
                int errorCount = 0;
                int totalCount = sheetGoods.UsedRange.Rows.Count - 1;
                // int totalCount = (int)numericTotal.Value;
                end = end + 1;

                object goodsSN = null;
                object goodsID = null;
                object goodsName = null;
                object goodsDepository = null;
                object goodsUnit = null;
                string pageFooter;
                DateTime startTime = DateTime.Now;

                while (index <= end && errorCount < 3)
                {
                    sheetTemplate.Copy(missing, workBookTemplate.Sheets[workBookTemplate.Sheets.Count]);
                    Worksheet sheetNew = (Worksheet)workBookTemplate.Sheets[workBookTemplate.Sheets.Count];
                    sheetNew.Name = (index - 1).ToString();

                    try
                    {
                        goodsSN = sheetGoods.Cells[index, 1];
                        goodsID = sheetGoods.Cells[index, 2];
                        goodsName = sheetGoods.Cells[index, 3];
                        goodsUnit = sheetGoods.Cells[index, 4];
                        goodsDepository = sheetGoods.Cells[index, 5];

                        sheetNew.Cells[4, 2] = goodsSN;
                        sheetNew.Cells[5, 2] = goodsID;
                        sheetNew.Cells[6, 2] = goodsName;
                        sheetNew.Cells[7, 2] = goodsUnit;
                        sheetNew.Cells[7, 4] = goodsDepository;

                        pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
                        //sheetNew.Cells[12, 4] = pageFooter;
                        Console.WriteLine(pageFooter);

                    }
                    catch
                    {
                        errorCount++;
                    }
                    index++;

                    if (index > end)
                    { break; }

                    //一页两个
                    try
                    {
                        goodsSN = sheetGoods.Cells[index, 1];
                        goodsID = sheetGoods.Cells[index, 2];
                        goodsName = sheetGoods.Cells[index, 3];
                        goodsUnit = sheetGoods.Cells[index, 4];
                        goodsDepository = sheetGoods.Cells[index, 5];

                        sheetNew.Cells[19, 2] = goodsSN;
                        sheetNew.Cells[20, 2] = goodsID;
                        sheetNew.Cells[21, 2] = goodsName;
                        sheetNew.Cells[22, 2] = goodsUnit;
                        sheetNew.Cells[22, 4] = goodsDepository;

                        pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
                        //sheetNew.Cells[12, 4] = pageFooter;
                        Console.WriteLine(pageFooter);
                    }
                    catch
                    {
                        errorCount++;
                    }
                    index++;

                }

                //删除模版页
                sheetTemplate.Delete();

                //更新数据后另存为新文件
                workBookTemplate.SaveAs(saveFileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing,
                    missing, missing, missing, missing);

                Console.WriteLine("All done.");
                DateTime endTime = DateTime.Now;
                Console.Write("{0}~{1},total time: {2}", startTime, endTime, endTime - startTime);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                workBookTemplate.Close(false, missing, missing);
                workBookTemplate = null;

                ThisApplication.Quit();

                ThisApplication = null;

            }
            try
            {
                ////打开刚才生成的Excel文件
                //Microsoft.Office.Interop.Excel.Application NewApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //Microsoft.Office.Interop.Excel.Workbook NewWorkBook;

                //NewWorkBook = NewApplication.Workbooks.Open(strSaveFileName, missing, missing, missing, missing, missing, missing, missing,
                //    missing, missing, missing, missing, missing, missing, missing);
                //Microsoft.Office.Interop.Excel.Worksheet NewSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets[1];
                //NewApplication.Visible = true;

                System.Diagnostics.Process.Start(saveFileName);//来打开新文件

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.Read();
        }
    }

--------
--- 开源的BUG管理系统,易于安装,使用方便!
 http://sourceforge.net/projects/bugonline
基于Asp.net2.0,Ajax ,sqlserver2005,欢迎加入共同维护.