CODEWITHSUNDEEP
exceltemplatesusingfillaspose
GeorgeT
GeorgeT

Reputation: 51

how to fill excel sheet from database using Aspose

how to fill excel sheet from database using Aspose total or Aspose Cells giving an Excel template that might contain formulas which should be keept active after filling the Excel document.

Upvotes: 0

Views: 2150

Answers (2)

GeorgeT
GeorgeT

Reputation: 51

after creating a folder in your project where you will have the Excel file that you want to generate and adding the Aspose.Total to your using statments. Create the following method to generate the excel file:

    protected void CurrentExcel_Click(object sender, EventArgs e){

        //getting the items that will fill the cells(should be different 
        //than below)
        Items searchItems = new SearchItems();

        searchItems.ProjectStatusIDs = new List<int> { 24721 };
        List<CurrentRecord> resultsRecords = 
        YourEntity.GetCurrentRecords().OrderBy(c => c.LOCATION).ToList();
        // the template Excel file that you will fill
        string fileName = "currents_list_Excel_Shortened.xlsx";
        //define a workbook that will help you access Excel file cells
        Workbook wb = new Workbook(Server.MapPath(string.Format(@" 
        {0}/{1}", "~/Templates/", fileName)));

        //adding worksheet that will be filled
        wb.Worksheets.Add(SheetType.Worksheet);
        Worksheet ws = wb.Worksheets[0];
        ws.Name = "Current Shortened";
        try
        {

            Aspose.Cells.Cells wsCells = ws.Cells;
            int x = 8;
            foreach (CurrentRecord mwa in resultsRecords)
            {



                Cell Cell1 = ws.Cells[x, 0];
                Cell Cell2 = ws.Cells[x, 1];
                Cell Cell3 = ws.Cells[x, 2];
                Cell Cell4 = ws.Cells[x, 3];
                Cell Cell5 = ws.Cells[x, 4];
                Cell Cell6 = ws.Cells[x, 5];
                Cell Cell7 = ws.Cells[x, 6];
                Cell Cell8 = ws.Cells[x, 7];
                Cell Cell9 = ws.Cells[x, 8];
                Cell Cell10 = ws.Cells[x, 9];
                Cell Cell11 = ws.Cells[x, 10];
                Cell Cell12 = ws.Cells[x, 11];
                Cell Cell13 = ws.Cells[x, 12];
                Cell Cell14 = ws.Cells[x, 13];
                // here filling your object properties to the cells which 
                //should be different than the one below
                Cell1.PutValue(mwa.ID + "-" + 
                mwa.LOCATION);
                Cell2.PutValue(mwa.number);
                Cell3.PutValue(mwa.Rate + " " + mwa.POSTMILE + " " + 
                mwa.POSTMILE_KPList);
                Cell4.PutValue(mwa.PROJECT_LOCATION_TYPE);
                Cell5.PutValue(mwa.RELName.Split(' ')[0] + "/" + 
                mwa.RECell);
                if (mwa.COMPANY_NAME != "")
                {
                    Cell6.PutValue(mwa.COMPANY_NAME.IndexOf('-') != -1 ? 
                    mwa.COMPANY_NAME.Split(' ')[0] : 
                    mwa.COMPANY_NAME.Split(' ')[0] + ' ' + 
                    mwa.COMPANY_NAME.Split(' ')[1]);
                }
                Cell7.PutValue(mwa.PROJECT_STATUS);
                Cell8.PutValue(mwa.PROJECT_LOCATION_WORKING_DAYS);
                Cell9.PutValue(mwa.PROJECT_STATUS_PE_DAYS);
                Cell10.PutValue(mwa.PROJECT_STATUS_WORK_SUSPENDED == true 
                ? "Yes" : "NO");
                Cell11.PutValue(string.Format("{0:0.######}", 
                mwa.PROJECT_STATUS_WORK_COMPLETED) + "/" + 
                string.Format("{0:0.######}", 
                mwa.PROJECT_STATUS_TIME_COMPLETED));
                Cell12.PutValue(mwa.M600 != null ? string.Format("{0:d}", 
                mwa.M600) : "TBD");
                Cell13.PutValue(mwa.Contractual != null ? string.Format(" 
                {0:d}", mwa.Contractual) : "TBD");
                Cell14.PutValue(mwa.PROJECT_STATUS_UPDATED_EST_COMPLETION 
                != null ? string.Format("{0:d}", 
                mwa.PROJECT_STATUS_UPDATED_EST_COMPLETION) : "TBD");

                x++;


            }
            wb.Save(HttpContext.Current.Response, fileName, 
            Aspose.Cells.ContentDisposition.Attachment, new 
            XlsSaveOptions(Aspose.Cells.SaveFormat.Xlsx));
        }
        catch(Exception ex)
        {
            throw;
        }
    }

Upvotes: 0

Amjad Sahi
Amjad Sahi

Reputation: 1931

Well, to import or merge data from your data source to Excel files, we have two options and you may try any one for your needs. e.g

1) Use Smart Markers feature provided by Aspose.Cells. So, you may create a designer template file inserting the markers into the cells in the sheet(s), you may also format the cells accordingly for your needs. For example, you may create reports for your database tables related to different sets of data or as per your desired records etc. Smart Markers are processed based on your desired dataset/resultset that might be the result of the query or stored procedure, so you may specify or write the query to be processed by using your own code with e.g ADO.NET APIs and get the data to be filled into the DataTable or variables/Array. When the markers are processed, data is inserted into the cells in place of your pasted markers in the designer file's sheets, see the document for your complete reference.

2) Use data importing options from different data sources provided by Aspose.Cells. For example, you may use Cells.ImportDataTable() method to import the data table to fill the worksheet in the Workbook. Please see the document for your complete reference.

PS. I am working as Support developer/ Evangelist at Aspose.

Upvotes: 1

Related Questions