Read excel file and write in to same file in asp.net

Question

0.00/5 (No votes)

See more:

Hi,

I want to read an Ms-excel template and write in to the same excel file
how can i do that please help me...

in simple words
I want to append data in existing excel file how do i achieve that???

thanks in advance

Posted 10-Nov-10 19:00pm

Indrajeet Sutar

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S.M.Naresh · Answer 1 · 2010-11-10T19:19:00

Hi Indrajeet,
You can use third party dlls like IExcel.dll for writing into Excel Sheet.
You can use Workbook,WritableWorkbook,WritableSheet class objects for writing into Excel file.Make sure you have a existing Template Excel file;read it through a file stream and use the above classes also.
Enclosed is the sample code that reads values from Database and writes to Excel file.

C#

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using IExcel;
using AjaxControlToolkit;
using IExcel.write;
using IExcel.read;
using System.IO;
using IExcel.biff;


public enum ExcelColumnNames
    {
        A = 0, B = 1, C = 2, D = 3, E = 4, F = 5, G = 6, H = 7, I = 8,
        S = 18, T = 19, U = 20, V = 21, W = 22, X = 23,
        Y = 24, Z = 25, AA = 26, AB = 27, AC = 28
    }
    public partial class ExportToExcel : System.Web.UI.Page
    {
        BlClass blobj = new BlClass();
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        FileInfo fi = null;
        WritableSheet XlSheetSummary = null;
        //WritableSheet temp = null;
        protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            ArrayList excelal = blobj.BllGetAndroidMetrics();
            fi = new FileInfo(Server.MapPath("Templates/" + "DesignTemplate.xls"));
            Workbook wb = Workbook.getWorkbook(fi);
            string FileName = Server.MapPath("Resources/" + "ExportToExcel" + ".xls");
            WritableWorkbook wrib = Workbook.createWorkbook(new FileInfo(FileName), wb);
            //to rename a sheet
            Sheet sh = wb.getSheet(0);
            wrib.importSheet("Hello", 0, sh);
            
            //To Write Data to a cell (row/column) in a Sheet
            XlSheetSummary = wrib.getSheet(1);//XlSheetSummary is a WritableSheet object.
            int intRow = 4;
            
            for (int i = 0; i < excelal.Count; i++)
            {
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.B), intRow, ((ModelMetrics)(excelal[i])).Platform1);
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.C), intRow, (Convert.ToInt32(((ModelMetrics)(excelal[i])).ApiLevel1)).ToString());
                setExcelCellValue(XlSheetSummary, Convert.ToInt32(ExcelColumnNames.D), intRow, (((ModelMetrics)(excelal[i])).Distribution1).ToString());
                intRow++;
            }
            
            XlSheetSummary.setName("Android Data");
            //To Add Image to excel start
                 FileInfo fiimg = new FileInfo(Server.MapPath("/Reports/chartReport.PNG"));
                 setExcelImageValue(XlSheetSummary, fiimg);
            //Write inage end
            wrib.removeSheet(0);
            wrib.removeSheet(2);
            wrib.removeSheet(3);
            wrib.write();
            wrib.close();
            //Convert File stream to bytes data
            System.IO.FileStream fs = new System.IO.FileStream(FileName, System.IO.FileMode.Open);
            Byte[] b = new byte[fs.Length];
            fs.Read(b, 0, (int)fs.Length);
            fs.Close();
            //Delete file from Savelocation
            try
            {
                FileInfo TheFile = new FileInfo(FileName);
                if (TheFile.Exists)
                {
                    File.Delete(FileName);
                }
                else
                {
                    throw new FileNotFoundException();
                }
            }
            catch (FileNotFoundException)
            {
                ApplicationLog.WriteInfo(ex.Message, "ExportExcel.btnExportToExcel()", Session["UserId"].ToString());
            }
            //Send output stream to client window
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=AndroidMetrics.xls");
            Response.BinaryWrite(b);
            Response.Flush();
            Response.End();
        }
        //To write Data to Cell
        public void setExcelCellValue(WritableSheet xlSheet, int intCol, int intRow, string strCellText)
        {
            // For Writing the Text.    
            IExcel.write.Label lblCellValue;
            lblCellValue = new IExcel.write.Label(intCol, intRow, strCellText);
            xlSheet.addCell(lblCellValue);
        }
        //To Write Image to Excel Cell
        public void setExcelImageValue(WritableSheet xlSheet,FileInfo image)
        {
            IExcel.write.WritableImage imgCellValue;
            imgCellValue = new IExcel.write.WritableImage(Convert.ToDouble(4), Convert.ToDouble(ExcelColumnNames.F),7.5, 14.5, image);
            xlSheet.addImage(imgCellValue);
        }
        protected void btnShowData_Click(object sender, EventArgs e)
        {
            ArrayList al = new ArrayList();
            gvValues.Visible = true;
            btnExportToExcel.Visible = true;
            al = blobj.BllGetAndroidMetrics();
            gvValues.DataSource = al;
            gvValues.DataBind();
        }
    }

Hope this helps...

m@dhu · Answer 2 · 2010-11-10T19:07:00

Solution 1

check here[^]

Posted 10-Nov-10 19:07pm

m@dhu

Member 8033773 · Answer 3 · 2011-06-30T11:04:00

Here is a reader

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Threading;
using System.IO;
namespace Excel
{
    public class Reader
    {
        public static DataTable XLSREADER(string fileName, string firstcell, string lastcell)
        {
            try
            {
                //Example
                //DataTable Tablez = XLSREADER("Example.xls","A1","D5")
                //DataRow rowz = Tablez.Rows[row#];
                //Double num = Convert.ToDouble(row[element#]);               
                string ConnectionString = Provider.SetConn(fileName);
                OleDbConnection objConn = new OleDbConnection(ConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$" + firstcell + ":" + lastcell + "]", objConn);
                // Othercmdtype = New OleDbCommand("SELECT F9, F10 FROM [Sheet1$]", oledbConn)
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                objAdapter1.SelectCommand = objCmdSelect;
                DataSet ds = new DataSet();
                objAdapter1.Fill(ds);
                DataTable dt = ds.Tables[0];
                objConn.Close();
                return dt;
            }
            catch (OleDbException ex)
            {
                Console.WriteLine("Error: {0}", ex);
                DataTable Error = new DataTable();
                return Error;
            }
        }
    }
}

Here is a writer

using System;
using System.Resources;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace Excel
{
    public class Writer
    {
        public static void XLSWriter(string filename, string descell, double number)
        {
            try
            {
                //Example
                //string file = "Test.xls";
                //Excel.WriteTest.XLSWriteTest(file, "C4", 420);
                string connectionstring = Excel.Provider.SetConn(filename);
                OleDbConnection conn = new OleDbConnection(connectionstring);
                OleDbCommand command = new OleDbCommand();
                conn.Open();
                command.Connection = conn;
                string Sql = "UPDATE [Sheet1$" + descell + ":" + descell + "] SET F1='" + number + "';";
                command.CommandText = Sql;
                command.ExecuteNonQuery();
                conn.Close();
            }
            catch (OleDbException ex)
            {
                Console.WriteLine("Error: {0}", ex);
            }
        }
    }
}

And here is an extra method I included that switches connection strings for xls or xlsx files.

<pre lang="cs">using System;
using System.Collections.Generic;
using System.Text;

namespace Excel
{
    class Provider
    {
        public static string SetConn(string file)
        {
            string path = @"c:\Documents and Settings\rdockter\My Documents\" + file + "";
            char[] dividers = new char[] { '.' };
            string[] filepart = file.Split(dividers, StringSplitOptions.RemoveEmptyEntries);
            if (filepart[1] == "xlsx")
            {
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=NO;READONLY=FALSE\"";
                return connstring;
            }
            if (filepart[1] == "xls")
            {
                string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=NO;READONLY=FALSE\"";
                return connstring;
            }
            else
            {
                Console.WriteLine("Invalid file type");
                return null;
            }
        }
    }
}

Read excel file and write in to same file in asp.net

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0