Tag Archives: DataTable

Create Excel Report Programmatically Without Installing MS Excel. Asp.Net & Win Form C# Using WebService Example

Assuming you only have open source spreadsheet software i.e OpenOffice.org Calc and you want to make Excel report file programmtically.
You dont have MS Office installed in your computer or server. Sure you can do it using ExcelLibrary library from Google Apps.

In order to try examples in this blog post, please download ExcelLibrary library from this url https://code.google.com/p/excellibrary/downloads/list

Here, I give examples implementing it by creating a WebService so that Asp.Net Website or Win From C# can use this WebService.
This makes ExcelLibrary file only have to copy in WebService’s server without distribute it to local / client box.

First of all we need to know about basic of ExcelLibrary usage. Taken from https://code.google.com/p/excellibrary

//create new xls file
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);

// open xls file
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];

 // traverse cells
 foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
 {
     dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
 }

 // traverse rows by Index
 for (int rowIndex = sheet.Cells.FirstRowIndex; 
        rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
 {
     Row row = sheet.Cells.GetRow(rowIndex);
     for (int colIndex = row.FirstColIndex; 
        colIndex <= row.LastColIndex; colIndex++)
     {
         Cell cell = row.GetCell(colIndex);
     }
 }

Continue reading

How to Show Data from Any Source such as Array, DataTable, WebService and Database Using Asp.Net ListView and ObjectDataSource

A web page is about to show a data. This data may generate from database or other object like an class instance.

Mostly, data is presented to a table and a lot of tutorial about making a table that I have is related to database. Developers have a listview / gridview / repeater etc for data view and sqldatasource control as a data source.

Although sqldatasource is good and may increase productivity. An organisation is not always use SQL Server and one of their web tables may not use database at all.

So I prefer to use ObjectDataSource control. This control is more generic data source because we use our own bare class to return a DataTable instance. Since we write our own class then inside it we can code an operation to any RDBMS or work with any objects.
ObjectDataSource control is also good option to retrieve data via Web Service from another Server and maybe that Server is located at different site. We cant see any code like database connection in Web Service but only class and namespace reference of it. Although maybe there is a database operation inside Web Service itself.

In this blog post, I generate the data manualy to DataTable in the first example and secondly, I generate it from webservice and database. Both of them show data via listview and use arbitrary class that I make. ObjectDatasource acts as bridge between this custom class instance and listview.

Adding Data Manualy to DataTable

Here’s the example:
I will show simple table that has persons info with their name, city and province.
.aspx file (piece of code):


    <div>
        
        
            <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>City</th>
                    <th>Province</th>
                </tr>
            </thead>
             
            </table>    
        
        
            
            <tr>
            <td></td><td></td>
            <td></td>
            </tr>
        
       
         
        
    </div>
    

Continue reading

Useful PHP DataTable Class Example To Store Objects including Recordset

Introduction

Any table like objects like array and recordset have same structure. They have rows and columns.
Usually column is a key and row is a value.

A recordset that comes from database ‘select’ operation usually stored in array or OOP object variable on MVC concept or any kind of business logic and view layer.
A web page access this variable to show its data. View layer only include database and datatable class files. So web page users doesn’t access database operational directly.

With this concept, I have build a simple DataTable class to store those kind of objects.
This DataTable has functions to add columns and rows and get their values so that we can extract information from DataTable with easy and use same methods repeatedly.
Also we can get DataTable object value on another page with SESSION functionality.

Continue reading