C # Excel export example

Summary:

Excel export has been widely used in C # code. I will summarize it here for you and your readers to learn.

Excel knowledge point.
1. Add reference and namespace
Add Microsoft. office. interop. excel reference. The default path is C: \ Program Files \ Microsoft Visual Studio 9.0 \ Visual Studio Tools for Office \ PIA \ Office12 \ Microsoft. office. interop. excel. dll
Add reference using Microsoft. Office. Interop. Excel in the Code;
II. Introduction to Excel
The structure of the Excel class in this namespace is as follows:
ApplicationClass-is our excel application.
Workbook-an excel file that we usually see. It is often operated by the Workbooks class.
Worksheet-a sheet in an excel file.
Worksheet. cells [row, column]-is the cell of a column in a row. Note that the subscript row and column start from 1, it is different from the subscript of an array or set that I usually use.
After learning the basic knowledge above, it is much clearer to use this class to Operate excel.
Iii. Excel operations
Any Excel operation must first use an excel application. First, you must create an ApplicationClass instance and release the instance.

ApplicationClass xlsApp = new ApplicationClass (); // 1. Create an instance of an Excel application object, which is equivalent to opening an Excel application from the Start Menu. If (xlsApp = null) {// verify this instance. if it is null, it indicates that the machine running this Code may not have Excel installed}

1. Open an existing Excel File

Workbook workbook = xlsApp. workbooks. open (excelFilePath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing); Worksheet mySheet = workbook. sheets [1] as Worksheet; // the first sheet page mySheet. name = "testsheet"; // modify the sheet Name here

2. Copy the sheet page

MySheet. copy (Type. missing, workbook. sheets [1]); // copy mySheet to form a new sheet page. After copying, add a (2) Name After mySheet Page name. Here testsheet (2) is used ), after copying, add one more Worksheet

Note that the two parameters of the Copy method refer to whether the new sheet page is copied before or after the specified sheet page, the preceding example indicates that the copied sheet is behind the first sheet.

3. Delete the sheet page

XlsApp. DisplayAlerts = false; // to delete a sheet, set this item to fasle. (XlsApp. ActiveWorkbook. Sheets [1] as Worksheet). Delete ();

4. Select the sheet page

(XlsApp. ActiveWorkbook. Sheets [1] as Worksheet). Select (Type. Missing); // Select a sheet

5. save an excel file

　　workbook.Saved = true;　　workbook.SaveCopyAs(filepath);

6. Release excel Resources

　　workbook.Close(true, Type.Missing, Type.Missing);　　workbook = null;　　xlsApp.Quit();　　xlsApp = null;

 

 

Generally, input a DataTable to generate the Excel Code.

        /// <summary>        ///         /// </summary>        /// <param name="dt"></param>        protected void ExportExcel(DataTable dt)        {            if (dt == null||dt.Rows.Count==0) return;            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();            if (xlApp == null)            {                return;            }            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];            Microsoft.Office.Interop.Excel.Range range;            long totalCount = dt.Rows.Count;            long rowRead = 0;            float percent = 0;            for (int i = 0; i < dt.Columns.Count; i++)            {                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];                range.Interior.ColorIndex = 15;                range.Font.Bold = true;            }            for (int r = 0; r < dt.Rows.Count; r++)            {                for (int i = 0; i < dt.Columns.Count; i++)                {                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();                }                rowRead++;                percent = ((float)(100 * rowRead)) / totalCount;            }            xlApp.Visible = true;        }

 

 

To insert an image into an excel file, add the code to a line, as shown below:

    protected void ExportExcel(DataTable dt)    {        if (dt == null || dt.Rows.Count == 0) return;        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();        if (xlApp == null)        {            return;        }        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");        Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;        Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];        Microsoft.Office.Interop.Excel.Range range;        long totalCount = dt.Rows.Count;        long rowRead = 0;        float percent = 0;        for (int i = 0; i < dt.Columns.Count; i++)        {            worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];            range.Interior.ColorIndex = 15;        }        for (int r = 0; r < dt.Rows.Count; r++)        {            for (int i = 0; i < dt.Columns.Count; i++)            {                try                {                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();                }                catch                {                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");                }            }            rowRead++;            percent = ((float)(100 * rowRead)) / totalCount;        }                worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);        worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);        xlApp.Visible = true;    }

 

 

The call is as follows:

    public void GenerateExcel()    {        DataTable dt = new DataTable();        dt.Columns.Add("Name", typeof(string));        dt.Columns.Add("Age", typeof(string));        DataRow dr = dt.NewRow();        dr["Name"] = "spring";        dr["Age"] = "20";        dt.Rows.Add(dr);        dt.AcceptChanges();        ExportExcel(dt);    }

 

 

The running result is as follows:

The following code is used

    worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);

Add an image to a specified position in Excel

   worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);

 

Add the text box and content to the specified position in Excel.

 

We can design a base class of ExcelBase as follows:

First create an ExcelBE. cs:

   public class ExcelBE    {        private int _row = 0;        private int _col = 0;        private string _text = string.Empty;        private string _startCell = string.Empty;        private string _endCell = string.Empty;        private string _interiorColor = string.Empty;        private bool _isMerge = false;        private int _size = 0;        private string _fontColor = string.Empty;        private string _format = string.Empty;        public ExcelBE(int row, int col, string text, string startCell, string endCell, string interiorColor, bool isMerge, int size, string fontColor, string format)        {            _row = row;            _col = col;            _text = text;            _startCell = startCell;            _endCell = endCell;            _interiorColor = interiorColor;            _isMerge = isMerge;            _size = size;            _fontColor = fontColor;            _format = format;        }        public ExcelBE()        { }        public int Row        {            get { return _row; }            set { _row = value; }        }        public int Col        {            get { return _col; }            set { _col = value; }        }        public string Text        {            get { return _text; }            set { _text = value; }        }        public string StartCell        {            get { return _startCell; }            set { _startCell = value; }        }        public string EndCell        {            get { return _endCell; }            set { _endCell = value; }        }        public string InteriorColor        {            get { return _interiorColor; }            set { _interiorColor = value; }        }        public bool IsMerge        {            get { return _isMerge; }            set { _isMerge = value; }        }        public int Size        {            get { return _size; }            set { _size = value; }        }        public string FontColor        {            get { return _fontColor; }            set { _fontColor = value; }        }        public string Formart        {            get { return _format; }            set { _format = value; }        }    }

 

 

Next, create ExcelBase. cs:

    public class ExcelBase    {        private Microsoft.Office.Interop.Excel.Application app = null;        private Microsoft.Office.Interop.Excel.Workbook workbook = null;        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;        private Microsoft.Office.Interop.Excel.Range workSheet_range = null;        public ExcelBase()        {            createDoc();        }        public void createDoc()        {            try            {                app = new Microsoft.Office.Interop.Excel.Application();                app.Visible = true;                workbook = app.Workbooks.Add(1);                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];            }            catch (Exception e)            {                Console.Write("Error");            }            finally            {            }        }        public void InsertData(ExcelBE be)        {            worksheet.Cells[be.Row, be.Col] = be.Text;            workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell);            workSheet_range.MergeCells = be.IsMerge;            workSheet_range.Interior.Color = GetColorValue(be.InteriorColor);            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();            workSheet_range.ColumnWidth = be.Size;            workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb();            workSheet_range.NumberFormat = be.Formart;        }        private int GetColorValue(string interiorColor)        {            switch (interiorColor)            {                case "YELLOW":                    return System.Drawing.Color.Yellow.ToArgb();                case "GRAY":                    return System.Drawing.Color.Gray.ToArgb();                case "GAINSBORO":                    return System.Drawing.Color.Gainsboro.ToArgb();                case "Turquoise":                    return System.Drawing.Color.Turquoise.ToArgb();                case "PeachPuff":                    return System.Drawing.Color.PeachPuff.ToArgb();                default:                    return System.Drawing.Color.White.ToArgb();            }        }    }

 

 

The call code is as follows:

        private void btnRun_Click(object sender, EventArgs e)        {            ExcelBase excel = new ExcelBase();            //creates the main header            ExcelBE be = null;            be = new ExcelBE (5, 2, "Total of Products", "B5", "D5", "YELLOW", true, 10, "n",null);            excel.InsertData(be);            //creates subheaders            be = new ExcelBE (6, 2, "Sold Product", "B6", "B6", "GRAY", true, 10, "",null);            excel.InsertData(be);            be=new ExcelBE(6, 3, "", "C6", "C6", "GRAY", true, 10, "",null);            excel.InsertData(be);            be=new ExcelBE (6, 4, "Initial Total", "D6", "D6", "GRAY", true, 10, "",null);            excel.InsertData(be);            //add Data to cells            be=new ExcelBE (7, 2, "114287", "B7", "B7",null,false,10,"", "#,##0");            excel.InsertData(be);            be=new ExcelBE (7, 3, "", "C7", "C7", null,false,10,"",null);            excel.InsertData(be);            be = new ExcelBE(7, 4, "129121", "D7", "D7", null, false, 10, "", "#,##0");            excel.InsertData(be);            //add percentage row            be = new ExcelBE(8, 2, "", "B8", "B8", null, false, 10, "", "");            excel.InsertData(be);            be = new ExcelBE(8, 3, "=B7/D7", "C8", "C8", null, false, 10, "", "0.0%");            excel.InsertData(be);            be = new ExcelBE(8, 4, "", "D8", "D8", null, false, 10, "", "");            excel.InsertData(be);            //add empty divider            be = new ExcelBE(9, 2, "", "B9", "D9", "GAINSBORO", true, 10, "",null);            excel.InsertData(be);           }

 

 

The result is shown in:

 

 

Author: spring yang

Source: http://www.cnblogs.com/springyangwc/

The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.