Epplus implementing Excel Report data and formula fills

Years after the first day of work, according to customer requirements to repair good Epplus report.

Epplus:

Epplus is an open source component that can read and write Excel 2007/2010 files using the Open Office XML (Xlsx) file format

The benefits are much better than office COM components, regardless of the Office version issue of the operating machine. Need to reference epplus. and using Officeopenxml;

Specification:

All action statements are placed in the following using:

 stringFile =Outputdir.fullname; if(file.exists (file)) file.delete (file); FileInfo NewFile=NewFileInfo (file);using(Excelpackage xlpackage =NewExcelpackage (Template,true) {byte[] bin=Xlpackage.getasbytearray ();                File.writeallbytes (file, bin); //Xlpackage.workbook.worksheets[1]            }            returnfile; 

As-is: template Overview Starting from A5 is done as part of the template, fixed, and now needs to be updated automatically based on the database content.

to-be: as follows, fills and automatically fills formulas and styles based on content.

The code is as follows:

//2016-02-15 added new auto-update dealer_group list feature                #regionDataTable Dtoverview=mo.                Getoverview (); DataRow Dr=Dtoverview.newrow (); DataRow DR1=Dtoverview.newrow (); dr[0] =""; dr[1] ="Others"; dr[2] ="OTHERS"; dr1[0] =""; dr1[1] =" Total"; dr1[2] ="";                DTOVERVIEW.ROWS.ADD (DR);                DTOVERVIEW.ROWS.ADD (DR1); if(DtOverview.Rows.Count >0) {Excelworksheet Ws_overview= xlpackage.workbook.worksheets["Overview"]; //Fill Mode                    varDataRange = ws_overview.cells["A5"]. Loadfromdatatable (Dtoverview,false); //Populating DataDatarange.autofitcolumns (); //Fill Formula                     for(inti =5; I < DtOverview.Rows.Count-1+5; i++)                    {                        vardataRangeFormula0 = Ws_overview.cells[i,4]; Datarangeformula0.formula="=iferror (Sumifs (dci_mo_tgt! e:e,dci_mo_tgt! c:c,overview! B"+ i +"),\"\")"; varDATARANGEFORMULA1 = Ws_overview.cells[i,5]; Datarangeformula1.formula="=iferror (Sumifs (dci_mo_tgt! g:g,dci_mo_tgt! c:c,overview! B"+ i +"),\"\")"; varDATARANGEFORMULA9 = Ws_overview.cells[i, -]; Datarangeformula9.formula="=sum (J"+ i +", K"+ i +", L"+ i +")"; //...                    }                     for(intj =4; J <= -; j + + )                    {                        intRow = DtOverview.Rows.Count +5-1; intRowbottom = Row-1; varDatarangeformula =Ws_overview.cells[row, J]; stringCHR =mo.                        Indextocolumn (j); Datarangeformula.formula="=sum ("+ CHR +"5"+":"+ CHR + Rowbottom +")"; }                    //Set Style                     for(intj =1; J <= -; J + +)                    {                        intRow = DtOverview.Rows.Count +5-1;  for(inti =5; I <= row;i++)                        {                            varDatarangeline =Ws_overview.cells[i, J]; DataRangeLine.Style.Border.Right.Style=OfficeOpenXml.Style.ExcelBorderStyle.Thin; DataRangeLine.Style.Border.Bottom.Style=OfficeOpenXml.Style.ExcelBorderStyle.Thin; if(i==Row) {DataRangeLine.Style.Font.Bold=true; DataRangeLine.Style.Fill.PatternType=OfficeOpenXml.Style.ExcelFillStyle.Solid;                            DataRangeLine.Style.Fill.BackgroundColor.SetColor (System.Drawing.Color.LightBlue); }                        }                    }                }                #endregion

Very simple, mainly need to mention is an Excel: column number and letter correspondence problem, because the filling formula when the general need is the column letter. The following is the indextocolumn (int i) method:

 Public stringIndextocolumn (intindex) {            if(Index <=0)            {                Throw NewException ("Invalid parameter"); } Index--; stringColumn =string.            Empty;  Do            {                if(column. Length >0) {Index--; } column= ((Char) (Index% -+ (int)'A')). ToString () +column; Index= (int) ((Index-index% -) / -); }  while(Index >0); returncolumn; }

Epplus implementing Excel Report data and formula fills