C# 操作Excel

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.Office.Interop.Excel;



namespace CShareExcelInterop
{
    class Program
    {

        private static int lastRow=0;
        private static System.Drawing.Color borderColor = System.Drawing.Color.Black;
        private static System.Drawing.Color categoryInteriorColor = System.Drawing.Color.FromArgb(217, 217, 217);


        static void Main(string[] args)
        {
            

            string fileName = "c:\\book1.xlsx";
            
            var application = new Application();
            application.Visible = true;
            var workbook = application.Workbooks.Open(fileName);
            var worksheet = workbook.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;

            createHeader(application);
            createContentTable(application);
            createBottom(application);

            Console.ReadLine();
            application.Quit();
        }

        static void createHeader(Application app)
        {
            string logo = "c:\\logo.gif";
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            sheet.Shapes.AddPicture(logo, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoCTrue, 5, 5, 215, 51);
            sheet.Range["A1:A1"].EntireColumn.ColumnWidth = 40;
            sheet.Range["A1:A1"].EntireRow.RowHeight = 60;

            
            //sheet.Range["B1:D1"].MergeCells = true;
            sheet.Range["B1:D1"].Merge(0);
            sheet.Range["B1:D1"].EntireColumn.AutoFit();
            sheet.Range["B1:D1"].Value = "QUOTATION";
            sheet.Range["B1:D1"].Font.Size = 28;
            sheet.Range["B1:D1"].Font.Name = "Arial Black";
            System.Drawing.Color rgbColor = System.Drawing.Color.FromArgb(216, 228, 232); 
            sheet.Range["B1:D1"].Font.Color = System.Drawing.ColorTranslator.ToOle(rgbColor);
            sheet.Range["B1:D1"].EntireColumn.ColumnWidth = float.Parse(sheet.Range["B1:D1"].EntireColumn.ColumnWidth.ToString()) + 5;

            sheet.Range["A3"].Value = "SuiteA, 3rd Floor Room 1008, Yan Hong Building, Wan Hu West Street No22.";

            sheet.Range["A4"].Value = "Xi Ba He, Chao Yang District, Beijing,100015";
            sheet.Range["A5"].EntireColumn.AutoFit();
            sheet.Range["A5"].Value = "Phone:010-51292800   Fax:ext 603                                                    Mail： abc@abc.com";

            sheet.Range["C3"].Value = "DATE:";
            sheet.Range["C3"].Font.Size = 9;
            sheet.Range["C3"].Font.Name = "Arial";
            sheet.Range["C3"].Font.Bold = true;

            sheet.Range["D3"].Font.Size = 9;
            sheet.Range["D3"].Font.Name = "Arial";
            sheet.Range["D3"].Font.Bold = true;
            

            sheet.Range["C4"].Value = "QUOTATION #";
            sheet.Range["C4"].Font.Size = 9;
            sheet.Range["C4"].Font.Name = "Arial";
            sheet.Range["C4"].Font.Bold = true;

            sheet.Range["D4"].Font.Size = 9;
            sheet.Range["D4"].Font.Name = "Arial";
            sheet.Range["D4"].Font.Bold = true;

            sheet.Range["A3"].Font.Size = 8;
            sheet.Range["A3"].Font.Name = "Arial";
            sheet.Range["A4"].Font.Size = 8;
            sheet.Range["A4"].Font.Name = "Arial";
            sheet.Range["A5"].Font.Size = 8;
            sheet.Range["A5"].Font.Name = "Arial";
            sheet.Range["A5"].EntireColumn.ColumnWidth = float.Parse(sheet.Range["A5"].EntireColumn.ColumnWidth.ToString());// +5;


            sheet.Range["A7"].Value = "Bill To:";
            sheet.Range["A8"].Value = "Beijing City International School";
            sheet.Range["A9"].Value = "No.77 Baiziwan Nan Er Road Chaoyang District, Beijin";
            sheet.Range["A10"].Value = "100022";
            sheet.Range["A11"].Value = "Phone:010-87717171";


            sheet.Range["A7"].Font.Size = 10;
            sheet.Range["A7"].Font.Name = "Arial";
            sheet.Range["A7"].Font.Bold = true;
            sheet.Range["A8"].Font.Size = 8;
            sheet.Range["A8"].Font.Name = "Arial";
            sheet.Range["A9"].Font.Size = 8;
            sheet.Range["A9"].Font.Name = "Arial";
            sheet.Range["A10"].Font.Size = 8;
            sheet.Range["A10"].Font.Name = "Arial";
            sheet.Range["A10"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            sheet.Range["A11"].Font.Size = 8;
            sheet.Range["A11"].Font.Name = "Arial";

            lastRow=11;

        }

        static void createContentHeader(Application app)
        {
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

            //create content header
            //sheet.Range["A13", "D13"].Merge(0);
            System.Drawing.Color interiorColor = System.Drawing.Color.FromArgb(216, 228, 232);
            sheet.Range["A13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
            sheet.Range["B13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
            sheet.Range["C13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
            sheet.Range["D13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);



            sheet.Range["A13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
            sheet.Range["B13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
            sheet.Range["C13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
            sheet.Range["D13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);

            sheet.Range["A13"].Value = "DESCRIPTION";
            sheet.Range["A13"].Font.Size = 8;
            sheet.Range["A13"].Font.Name = "Arial";
            sheet.Range["A13"].Font.Bold = true;
            sheet.Range["B13"].Value = "QTY";
            sheet.Range["B13"].Font.Size = 8;
            sheet.Range["B13"].Font.Name = "Arial";
            sheet.Range["B13"].Font.Bold = true;
            sheet.Range["C13"].Value = "UNIT PRICE";
            sheet.Range["C13"].Font.Size = 8;
            sheet.Range["C13"].Font.Name = "Arial";
            sheet.Range["C13"].Font.Bold = true;
            sheet.Range["D13"].Value = "SUBTOTAL";
            sheet.Range["D13"].Font.Size = 8;
            sheet.Range["D13"].Font.Name = "Arial";
            sheet.Range["D13"].Font.Bold = true;
            lastRow = 13;
        }

        static void createCategoryHeader(Application app)
        {
            lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //create category
            var range = sheet.Range["A" + lastRow, "D" + lastRow];
            range.Merge(0);
            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(categoryInteriorColor);
            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
            range.Value = "******Category Name******";
            range.Font.Size = 10;
            range.Font.Name = "Arial";
            range.Font.Bold = true;
        }

        static void createProduct(Application app,System.Data.DataRow dr)
        {
            lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //create product
            //var range = sheet.Range["A" + lastRow , "D" + lastRow ];
            var range = sheet.Range["A" + lastRow];
            range.Value = dr["FItemName"].ToString();
            range.Font.Size = 10;
            range.Font.Name = "Arial";

            range = sheet.Range["B" + lastRow];
            range.Value = dr["FAuxqtyFrom"].ToString();
            range.Font.Size = 10;
            range.Font.Name = "Arial";

            range = sheet.Range["C" + lastRow];
            range.Value = dr["FAuxTaxPrice"].ToString();
            range.Font.Size = 10;
            range.Font.Name = "Arial";

            range = sheet.Range["D" + lastRow];
            range.Value = Convert.ToDecimal(dr["FAuxTaxPrice"]) * Convert.ToDecimal(dr["FAuxqtyFrom"]);
            range.Font.Size = 10;
            range.Font.Name = "Arial";

            /*A``
            range = sheet.Range["A" + lastRow , "D" + lastRow ];
            int startRow = range.Row;
            long endRow = long.Parse(range.EntireRow.Rows.CountLarge.ToString());
            int startColumn = range.Column;
            int endColumn = int.Parse(range.EntireColumn.Columns.CountLarge.ToString());
            Console.WriteLine("column:" + startColumn + " entircolumn:" + endColumn);
            Console.WriteLine("row:" + startRow + " endRow:" + endRow);
             * */
        }

        static void createDescription(Application app)
        {
            lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //create Description
            var range = sheet.Range["A" + lastRow , "D" + lastRow ];
            range.Cells.Merge(true);
            range.Value = "******Description******";
            range.Font.Size = 10;
            range.Font.Name = "Arial";
            range.EntireRow.RowHeight = 60;
        }

        static void createSubTotal(Application app)
        {
            lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //create subtotal
            var range = sheet.Range["A" + lastRow, "C" + lastRow];
            range.Cells.Merge(true);
            range.Interior.Color = categoryInteriorColor;
            range.Borders.Color = borderColor;
            range.Value = "Subtotal";
            range.Font.Bold = true;
            range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

            range = sheet.Range["D" + lastRow];
            range.Borders.Color = borderColor;
            range.Value = "0.00";
        }

        static void creTotal(Application app)
        {
            lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //create total
            var range = sheet.Range["A" + lastRow, "C" + lastRow];
            range.Cells.Merge(true);
            range.Interior.Color = categoryInteriorColor;
            range.Borders.Color = borderColor;
            range.Value = "TOTAL";
            range.Font.Bold = true;
            range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

            range = sheet.Range["D" + lastRow];
            range.Borders.Color = borderColor;
            range.Value = "0.00";

            //lastRow = 18;
        }


        static void createContentTable(Application app)
        {
            //var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

            createContentHeader(app);

            createCategoryHeader(app);

            System.Data.DataTable dt = DBHelper.GetProList("AQ000005");
            for (int i = 0; i < dt.Rows.Count ; i++)
            {
                createProduct(app,dt.Rows[i]);
                //if (i % 3 == 0)
                //{
                //    createCategoryHeader(app);
                //    createProduct(app);
                //    createDescription(app);
                //}
            }
            

            createSubTotal(app);

            creTotal(app);
        }

        //static void createContentTable(Application app)
        //{
        //    var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

        //    //create content header
        //    //sheet.Range["A13", "D13"].Merge(0);
        //    System.Drawing.Color interiorColor=System.Drawing.Color.FromArgb(216,228,232);
        //    sheet.Range["A13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
        //    sheet.Range["B13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
        //    sheet.Range["C13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
        //    sheet.Range["D13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);


        //    System.Drawing.Color borderColor = System.Drawing.Color.Black;
        //    sheet.Range["A13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
        //    sheet.Range["B13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
        //    sheet.Range["C13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
        //    sheet.Range["D13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);

        //    sheet.Range["A13"].Value = "DESCRIPTION";
        //    sheet.Range["A13"].Font.Size = 8;
        //    sheet.Range["A13"].Font.Name = "Arial";
        //    sheet.Range["A13"].Font.Bold = true;
        //    sheet.Range["B13"].Value = "QTY";
        //    sheet.Range["B13"].Font.Size = 8;
        //    sheet.Range["B13"].Font.Name = "Arial";
        //    sheet.Range["B13"].Font.Bold = true;
        //    sheet.Range["C13"].Value = "UNIT PRICE";
        //    sheet.Range["C13"].Font.Size = 8;
        //    sheet.Range["C13"].Font.Name = "Arial";
        //    sheet.Range["C13"].Font.Bold = true;
        //    sheet.Range["D13"].Value = "SUBTOTAL";
        //    sheet.Range["D13"].Font.Size = 8;
        //    sheet.Range["D13"].Font.Name = "Arial";
        //    sheet.Range["D13"].Font.Bold = true;
        //    lastRow = 13;

        //    //create category
        //    var range = sheet.Range["A14", "D14"];
        //    System.Drawing.Color categoryInteriorColor = System.Drawing.Color.FromArgb(217,217,217);
        //    range.Merge(0);
        //    range.Interior.Color = System.Drawing.ColorTranslator.ToOle(categoryInteriorColor);
        //    range.Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
        //    range.Value = "******Category Name******";
        //    range.Font.Size = 10;
        //    range.Font.Name = "Arial";
        //    range.Font.Bold = true;

        //    //create product
        //    range = sheet.Range["A15", "D15"];
        //    range.Value = "******Product Name******";
        //    range.Font.Size = 10;
        //    range.Font.Name = "Arial";

        //    range = sheet.Range["A15", "D15"];
        //    int startRow = range.Row;
        //    long endRow = long.Parse(range.EntireRow.Rows.CountLarge.ToString());
        //    int startColumn = range.Column;
        //    int endColumn = int.Parse(range.EntireColumn.Columns.CountLarge.ToString());
        //    Console.WriteLine("column:" + startColumn + " entircolumn:" + endColumn);
        //    Console.WriteLine("row:" + startRow + " endRow:" + endRow);

        //    //create Description
        //    range = sheet.Range["A16", "D16"];
        //    range.Cells.Merge(true);
        //    range.Value = "******Description******";
        //    range.Font.Size = 10;
        //    range.Font.Name = "Arial";
        //    range.EntireRow.RowHeight = 60;

        //    //create subtotal
        //    range = sheet.Range["A17", "C17"];
        //    range.Cells.Merge(true);
        //    range.Interior.Color = categoryInteriorColor;
        //    range.Borders.Color = borderColor;
        //    range.Value = "Subtotal";
        //    range.Font.Bold = true;
        //    range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
            
        //    range = sheet.Range["D17"];
        //    range.Borders.Color = borderColor;
        //    range.Value = "0.00";

        //    //create total
        //    range = sheet.Range["A18", "C18"];
        //    range.Cells.Merge(true);
        //    range.Interior.Color = categoryInteriorColor;
        //    range.Borders.Color = borderColor;
        //    range.Value = "TOTAL";
        //    range.Font.Bold = true;
        //    range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

        //    range = sheet.Range["D18"];
        //    range.Borders.Color = borderColor;
        //    range.Value = "0.00";

        //    lastRow = 18;
        //}

        static void createBottom(Application app)
        {
            lastRow++;
            lastRow++; lastRow++; lastRow++;
            var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

            var range = sheet.Range["A" + lastRow];
            range.Cells.Merge(true);
            range.Value = "If you have any questions concerning this quotation, please feel free to contact us.";

            lastRow++; lastRow++;
            range = sheet.Range["A" + lastRow,"D" + lastRow];
            int width = Convert.ToInt32(range.EntireColumn.Width);
            Shape textBox = sheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, Convert.ToInt32(range.Left), Convert.ToInt32(range.Top), width, 16);


            textBox.TextFrame.Characters(Type.Missing, Type.Missing).Text = "THANK YOU FOR YOUR BUSINESS!";
            textBox.TextFrame.Characters(Type.Missing, Type.Missing).Font.Bold = true;
            textBox.TextFrame.Characters(Type.Missing, Type.Missing).Font.Size = 10;
            textBox.TextFrame.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Microsoft.Office.Core.MsoTextEffectAlignment.msoTextEffectAlignmentCentered;
            textBox.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;

        }

        static void demo()
        {
            string fileName = "c:\\a.xlsx";
            var application = new Application();
            application.Visible = true;
            var workbook = application.Workbooks.Open(fileName);
            var worksheet = workbook.Worksheets[2] as Microsoft.Office.Interop.Excel.Worksheet;
            //var worksheet = workbook.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;

            worksheet.Cells[1, 1] = "Hello World!";
            //Console.Read();

            worksheet.Range["A1"].Value = "Hello Range";

            worksheet.Range["A1:c3"].MergeCells = true;

            Range range = worksheet.Range["A1:A3"];
            range.Name = "range1";
            worksheet.Names.Add("range1", range);

            //worksheet.Range["range1"].Value;
            //worksheet.Range["t1!range1"].Value

            //enum range
            foreach (Microsoft.Office.Interop.Excel.Name name in worksheet.Names)
            {
                Console.WriteLine(name.RefersToRange.Cells.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing));
                Console.WriteLine(name.RefersToRange.Cells.Worksheet.Name);
                Console.WriteLine(name.Name);
            }

            //search
            //set the cell color to red,which cell has the mached value
            Range currentFind = null;
            Range firstFind = null;
            Range Fruits = worksheet.get_Range("A1", "J50");
            currentFind = Fruits.Find("9300", Type.Missing,
                XlFindLookIn.xlValues, XlLookAt.xlPart,
                XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
                Type.Missing, Type.Missing);
            while (currentFind != null)
            {
                // Keep track of the first range you find.  
                if (firstFind == null)
                {
                    firstFind = currentFind;
                }

                // If you didn't move to a new range, you are done. 
                else if (currentFind.get_Address(XlReferenceStyle.xlA1)
                      == firstFind.get_Address(XlReferenceStyle.xlA1))
                {
                    break;
                }

                currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                currentFind.Font.Bold = true;

                currentFind = Fruits.FindNext(currentFind);
            }
            //end search

            Console.ReadLine();
            application.Quit();
        }
    }
}