Spreadsheets in ASP.NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Spreadsheets in ASP.NET

Displaying data in spreadsheets can be done in the following ways. Before going any further, make sure that http://localhost is present in the trusted sites list of your Internet Explorer.

Type I

1. Insert the Microsoft Office Spreadsheet Component on the web form. I used version 11.0.
2. Right-click on it and go to Properties. Click on the data source tab.
3. Click Edit, open a new datasource, and create one.
4. Write the query in the command text textbox.
5. Now, you can see that the spreadsheet is populated with the table. You will change the query at runtime.
6. Go to the HTML view. You can see an object tag. Give this object an ID; I gave it ssheet.
7. Look for the XMLData parameter. Copy the value of it; don’t include the single quotation.
8. Add a new item to the project, a new XML file. Name it data.xml.
9. Delete its data and paste the value you copied earlier.
10. Press Crtl+F and click the Replace button. In the find what box, type 
 and click the Replace all button.
11. Save data.xml and go back to the webform where you have the spreadsheet.
12. Right-click; go to properties. Click the import tab. In the URL box, type data.xml. Don’t forget to select the Refresh data from url at runtime check box because you want to update the data at runtime.
13. Click Import now.
14. Now that the spreadsheet is ready, go to the PageLoad event of the webform and manipulate the XML file so that the spreadsheet looks empty at pageload (if you want to show it empty).

Task

Create an xmldocument object and load the data.xml file to it. Change the CommandText tag text.

Using System.XML;

private void Page_Load(object sender, System.EventArgs e)
{
   //create an XML document.
   XmlDocument xdoc = new XmlDocument();
   //load your XML file.
   xdoc.Load(Server.MapPath("data.xml"));

   //search for the c:CommandText node and store it in a node
   //so that you can manipulate it

   XmlNodeList xn = xdoc.GetElementsByTagName("c:CommandText");
   /*The first one found will be the command text of the visible
     sheet that you have to work on if you want to have an empty
     spreadsheet on page load. Replace the innertext of this node
     with ""; otherwise, write the query string.*/

   xn[0].InnerText = "/*query*/";

   //save changes to the xml file
   xdoc.Save(Server.MapPath("data.xml"));
}

Type II

1. Open Microsoft Excel.
2. Edit whatever you want.
3. Go to the File menu and select Save as webpage. Select the Add interactivity checkbox so that the file is saved in HTML format.
4. Open the HTML page that you saved earlier. Right-click on the page and select View page source.
5. Look for the object tag and copy the whole tag from <object> to </object>.
6. Open the webform in which you want to include the spreadsheet. Go to the HTML view. Paste the code in between the <form></form> tags. Change its ID to a simple name. I use id = “ssheet1”.
7. Run the page. You see the data that you have stored in the Excel file. Input some data. If you want to apply a cell format to another cell, copy the cell and paste it to the cell to which you want to apply your format.

Type III

This one needs both client script and client code. Before going to Type III, you need to learn how to update the database table from the spreadsheet.

Updating changes to the database

Type 1: Long Procedure updates the entire spreadsheet data at once. Whatever is displayed in a spreadsheet can be derived as CVS data you use to update changes. Include the onblur=”submits()” event in the object tag. You write submits() using JavaScript.

<script language="javascript">
   function submitcs()
   {
      Form1.txt.value = Form1.spread.CSVData;
   }
</script>

txt is a hidden textarea html control on the webform. You fill it with CSVData of the spreadsheet component whenever the spreadsheet loses its focus.

This step totally needs your logic. One should be sure of what he/she wants from the CSV data, depending on which one can divide the CSB (Comma Separated Values) data and use it.

Suppose I have a table category that has catid, catname, and catdesc fields. By using any of the above types, I have populated the spreadsheet. Now, any change made to it should change the value in the database.

For this, I created a DataTable that contains these three fields and filled the table with csvdata from textarea txt that I retrieved from the spreadsheet.

//seperate the csv (comma seperated values) into a string array.
//split the txt (text area) value delimiter is ',' as its CSVData
//and 'r' for row seperator
string[] s = txt.Value.Split(new char[]{',','r'});
int len = s.Length;

//create a data table
DataTable dt = new DataTable();
//define columns
dt.Columns.Add("catid", System.Type.GetType("System.String"));
dt.Columns.Add("catname", System.Type.GetType("System.String"));
dt.Columns.Add("catdesc", System.Type.GetType("System.String"));

   //ignore the first three values
   //as i have used them as heading. so we start with 4 value so i=3
   int i = 3;
   //j = i+1 next value after i.
   int j = i+1;

   do
   {
      //CreateRow
      DataRow dr = dt.NewRow();
      // Fill Row
      //first column value is s[i]
      dr["catid"] = s[i];
      //second column value is s[i+1]
      dr["catname"] = s[j];

      //third column value is s[i+2] we have 3 columns only.
      dr["catdesc"] = s[j+1];

      //add the row to the data table
      dt.Rows.Add( dr );

      //now move the pointer to next row
      //i.e, after 3 more values.
      i+=3;
      j+=3;
   } while(j+1<len);    //len is the length of the array.

   //use try-catch to be safe. i almost forgot why i used it.
   try {
      //get the count of rows
      int count = dt.Rows.Count;

      //we need to update all records
      for(int k=0; k <count ; k++)
      {
         //i have used a stored procedure and con is my connection
         //object
         SqlCommand cmd = new SqlCommand();
         cmd = new SqlCommand("sp_category_update",con);
         cmd.CommandType = CommandType.StoredProcedure;

         pcatid = new SqlParameter("@catid",SqlDbType.BigInt,8);
         pcatid.Value = dt.Rows[k][0].ToString();
         cmd.Parameters.Add(pcatid);

         pcatname = new SqlParameter("@catname",SqlDbType.NVarChar,50);
         pcatname.Value = dt.Rows[k][1].ToString();
         cmd.Parameters.Add(pcatname);

         pcatdesc = new SqlParameter("@catdesc",SqlDbType.NVarChar,50);
         pcatdesc.Value = dt.Rows[k][2].ToString();
         cmd.Parameters.Add(pcatdesc);

         SqlParameter perr1 = new SqlParameter("@err",SqlDbType.Int,4);
         perr1.Direction = ParameterDirection.Output;
         cmd.Parameters.Add(perr1);

         cmd.ExecuteNonQuery();
      }

   }
   catch{};

To Be Continued ….