Lets Learn

Opinion Matters

Basics of Spreadsheet – How to Clear a Column – Part 3

Posted by Ankush on March 11, 2010


Now we understand the basics of Spreadsheet, lets discuss about a simple scenario. Consider this, I have an Excel sheet and I would like to clear a column. To understand what’s required to accomplish this, lets follow:

 Create a sample worksheet.

Extract the content and view Sheet1.xml / SharedString.XML 

Sheetdata( Sheet1.xml) SharedString.XML
<sheetData>
<row r=”1″ spans=”1:2″>
  <c r=”A1″ t=”s”>
        <v>0</v>
  </c>
  <c r=”B1″>
       <v>9</v>
  </c>
</row><row r=”2″ spans=”1:2″>
 <c r=”A2″ t=”s”>
   <v>1</v>
 </c>
 <c r=”B2″>
  <v>10</v>
 </c>
</row>

<row r=”3″ spans=”1:2″>
  <c r=”A3″>
   <v>1</v>
  </c>
 <c r=”B3″>
   <v>11</v>
 </c>
</row>

<row r=”4″ spans=”1:2″>
  <c r=”A4″>
   <v>2</v>
  </c>
 <c r=”B4″>
   <v>12</v>
 </c>
</row>

<row r=”5″ spans=”1:2″>
  <c r=”A5″>
   <v>3</v>
  </c>
 <c r=”B5″>
   <v>13</v>
 </c>
</row>

<row r=”6″ spans=”1:2″>
  <c r=”A6″>
   <v>4</v>
  </c>
 <c r=”B6″>
   <v>14</v>
 </c>
</row>

<row r=”7″ spans=”1:2″>
  <c r=”A7″>
   <v>5</v>
  </c>
 <c r=”B7″>
   <v>15</v>
 </c>
</row>
</sheetData>

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
<sst xmlns=”http://schemas.openxmlformats.org/spreadsheetml/2006/main&#8221; count=”2″ uniqueCount=”2″>
  <si>
    <t>John</t>
  </si>
  <si>
    <t>Tom</t>
  </si>
</sst>

 

As I have explained in my previous blog that strings are referenced based on index in Sheet1.xml and the actual values are present in the sharedString XML that’s why you don’t see any string stored in SheetData. Now if you look  at the SheetData, you will notice that it is defined row by row basis. So basically If I want to clear a column, I have to delete an XML node for each row element. Let’s just examine one row entry in detail:

 <row r=”1″ spans=”1:2″>
      <c r=”A1″ t=”s”>
        <v>0</v>
      </c>
      <c r=”B1″>
        <v>9</v>
      </c>
    </row> 

If you notice that to refer a column, Excel uses A1 and B1 and every row is defined in that way. Let’s say, you want to clear column A, then you need to go through the every row, find c node (basically cell) which refers to A column and delete that node. Basically

Previous XML After XML
    <row r=”1″ spans=”1:2″>
      <c r=”A1″ t=”s”>
        <v>0</v>
      </c>
      <c r=”B1″>
        <v>9</v>
      </c>
    </row>
    <row r=”2″ spans=”1:2″>
      <c r=”A2″ t=”s”>
        <v>1</v>
      </c>
      <c r=”B2″>
        <v>10</v>
      </c>
    </row>
    <row r=”1″ spans=”1:2″>
      <c r=”A1″>
        <v>9</v>
      </c>
    </row>
    <row r=”2″ spans=”1:2″>
      <c r=”A2″>
        <v>10</v>
      </c>
    </row>

 

So now you understand the concept, let’s have a look at the sample code which clears a column A 

// Clear a column from XL

    string docName=””,  worksheetName=”Sheet1″;
    SpreadsheetDocument document = SpreadsheetDocument.Open(@”Book1.xlsx”, true);
    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
    if (sheets.Count() == 0)
    {
        // The specified worksheet does not exist.
        return;
    }
    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
    var cells = sheetData.Descendants<Cell>().Where(s => s.CellReference.Value.Contains(“A”));
    foreach (var item in cells)
    {
        item.Remove();
    }
    worksheet.Save();

But are we done yet????..Still there are some strings left in SharedString which were referenced by column A. For the completeness you can delete them as well but even if you don’t delete them, Excel will do it for you. When you will open the modified sheet in Excel and save it, it will readjust the data for you!!!!!!

Let me know wht you think about this Post!!!!!!

Advertisements

6 Responses to “Basics of Spreadsheet – How to Clear a Column – Part 3”

  1. This works fine, but if you remove a column between other columns, how would you move the cells right of the column deleted one column to the left?

    Frode M

    • ankushbhatia said

      Hi Frode,

      Thanks for reading the post.

      It requires you to adjust SheetData when you delete a column. Basically it requires you to adjust row tag (r attribute) and c tag (r attribute).

      Currently I am busy with a post on PivotTable and Open XML but I’ll try and wirte a sample on “Delete a Column” and will post it by next week.
      Thanks,
      Ankush

  2. Doug said

    Can you get back to us with how to DELETE a whole column? (Not just clear.) I am looking for code with that ShiftCellsLeft option. Thanks, DOUG

    • Ankush said

      HI,

      I started writing the sample but I couldn’t complete it because I got into the SHarePoint trainings but now again I can work on that.

      I will send you the piece of code.

      Thanks
      Ankush

    • Ankush said

      Hi Doug,

      I have posted a solution for this. Please have a look and let me know if you find any issues!!

      Thanks,
      Ankush

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: