Delete rows from Excel using Microsoft Office Interop Library in ASP.Net

shabzo1

SOLVED CLOSED ACTIVE ANSWERED
User: shabzo1
Posted: on May 30, 2018 07:23 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 11066
Sample Code: Download

no of rows on twmplate 103

row starts at A21

min rows to display 32

if more hen delete only empty rows.

transferred from datgridview1 to excel.

If there is +- 16 rows then i want the template should only show 32 rows and delete the bal of the rows on the template, If there is 80 rows from the datagrid then show the 60 on template and the balance of 20 rows to be deleted.

Please assist

        private void button1_Click(object sender, EventArgs e)
        {
            //Excel.Application xlApp;
            //Excel.Workbook xlWorkBook;
            //Excel.Worksheet xlWorkSheet;
            int currentSheet = 1;
            Excel.Application excel = new Excel.Application();
            string filePath = @"C:\MyProdecData\testquote.xltx";
            Excel.Workbook workbook = excel.Workbooks.Add(filePath);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(currentSheet); ;
            object misValue = System.Reflection.Missing.Value;
            Excel.Range range = worksheet.UsedRange;
            
            //xlApp = new Excel.Application();
            //xlWorkBook = xlApp.Workbooks.Add(filePath);

            string datestr = DateTime.Now.ToString("dd.MM.yyyy");
            string quote = txtName.Text.Replace(" ", " ");
            string filename = @"C:\MyProdecQuotes\Quote_";
            string myfile = filename + quote + "_" + datestr + ".xlsx";
            string salesman = txtEmail.Text.ToUpper();
            string customer = txtName.Text.ToUpper();
            string custname = txtCustName.Text.ToUpper();
            string delivery = txtDel.Text;

            //int currentSheet = 1;
            int cell = 0;
            int rowsToDisplay = 32;
            int rowsToStart = 21;
            
            for (int row = 0; row < dataGridView1.Rows.Count - 1; row++)
            {
                if (currentSheet <= workbook.Sheets.Count)
                {
                    worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(currentSheet);
                }
                else
                {
                    var xlSheets = workbook.Sheets as Excel.Sheets;
                    worksheet = (Excel.Worksheet)xlSheets.Add(xlSheets[workbook.Sheets.Count], misValue, misValue, misValue);
                    worksheet.Name = "Sheet" + currentSheet.ToString();
                }
                worksheet.get_Range("F18", "H18").Value2 = salesman;
                worksheet.get_Range("A13", "D14").Value2 = customer;
                worksheet.get_Range("B15", "D15").Value2 = custname;
                worksheet.get_Range("H13", "K15").Value2 = delivery;
                worksheet.get_Range("A13", "D14").Font.Bold = true;
                //xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
                worksheet.get_Range("J3", "J3").Value2 = DateTime.Now;
                worksheet.get_Range("J5", "K5").Value2 = quote + "_" + datestr;

                for (int column = 0; column < dataGridView1.Columns.Count; column++)
                {
                    if (column == 2 || column == 5)
                    {
                        worksheet.Cells[cell + rowsToStart, column + 1] = "'" + dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper();
                    }
                    else
                    {
                        worksheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper();
                    }
                }
                if ((row + 1) % rowsToDisplay == 0)
                {
                    currentSheet++;
                    cell = 0;
                }
                else
                {
                    cell++;
                }
            
            }
            
            if (System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);
            }
            
            //workbook.SaveAs(myfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            workbook.SaveAs(myfile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
            workbook.Saved = true;
            workbook.Close(true, misValue, misValue);
            if (excel != null)
            {
                excel.Quit();
            }
            releaseObject(worksheet);
            releaseObject(workbook);
            releaseObject(excel);

            MessageBox.Show("Excel file created , CLICK on the Open quote button");
            {
                btn2.Show();

            }

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 14

Reply
ANSWER
User: dharmendr
Replied: on Jun 06, 2018 07:32 AM
Report

Hi shabzo1,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView runat="server" ID="dataGridView1" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<asp:Button Text="Save" runat="server" OnClick="Save" />

Namespaces

using Excel = Microsoft.Office.Interop.Excel;
using System.Data;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
        dt.Rows.Add(1, "John Hammond", "United States");
        dt.Rows.Add(2, "Mudassar Khan", "India");
        dt.Rows.Add(3, "Suzanne Mathews", "France");
        dt.Rows.Add(1, "John Hammond", "United States");
        dt.Rows.Add(2, "Mudassar Khan", "India");
        dt.Rows.Add(3, "Suzanne Mathews", "France");
        dt.Rows.Add(4, "Robert Schidner", "Russia");
        dt.Rows.Add(1, "John Hammond", "United States");
        dt.Rows.Add(2, "Mudassar Khan", "India");
        dt.Rows.Add(3, "Suzanne Mathews", "France");
        dt.Rows.Add(4, "Robert Schidner", "Russia");
        dt.Rows.Add(1, "John Hammond", "United States");
        dt.Rows.Add(2, "Mudassar Khan", "India");
        dt.Rows.Add(3, "Suzanne Mathews", "France");
        dt.Rows.Add(4, "Robert Schidner", "Russia");
        dt.Rows.Add(1, "John Hammond", "United States");        
        dataGridView1.DataSource = dt;
        dataGridView1.DataBind();
    }
}

protected void Save(object sender, EventArgs e)
{
    string sourcePath = @"C:\Users\dharmendra\Desktop\masterquote.xlsx";
    string destinationPath = @"C:\Users\dharmendra\Desktop\Quote_" + "Test.xls";
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet = null;
    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(sourcePath);
    string datestr = DateTime.Now.ToShortDateString();
    string quote = "Test" + "_" + datestr;
    string salesman = "txtEmail".ToUpper();
    string customer = "txtName".ToUpper();
    int currentSheet = 1;
    int cell = 0;
    int rowsToDisplay = 5;
    int rowsToStart = 19;
    for (int row = 0; row < dataGridView1.Rows.Count; row++)
    {
        if (currentSheet <= xlWorkBook.Sheets.Count)
        {
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
        }
        else
        {
            var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
            xlWorkSheet = (Excel.Worksheet)xlSheets.Add(misValue, xlSheets[xlWorkBook.Sheets.Count]);
            xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
        }
        xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
        xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("H12", "I12").Value2 = DateTime.Now;
        xlWorkSheet.get_Range("H10", "I10").Value2 = quote;
        for (int column = 0; column < dataGridView1.Columns.Count; column++)
        {
            if (column == 2 || column == 5)
            {
                xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "'" + dataGridView1.Rows[row].Cells[column].Text.ToString();
            }
            else
            {
                xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Text.ToString();
            }
        }
        int rowToDeleteFrom = rowsToStart + rowsToDisplay;
        if ((row + 1) % rowsToDisplay == 0)
        {
            Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A49");
            Excel.Range entireRow = range.EntireRow;
            entireRow.Delete(Excel.XlDirection.xlUp);
            currentSheet++;
            cell = 0;
        }
        else
        {
            cell++;
        }
    }

    if (System.IO.File.Exists(destinationPath))
    {
        System.IO.File.Delete(destinationPath);
    }

    xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
}

It will delete the defined rows from the excel.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Delete rows from Excel using Microsoft Office Interop Library in ASP.Net

Company

Explore

Follow us

Contact Us