Sunday, December 19, 2010

export DataTable To Excel

static DataTable GetTable()
{
DataTable table = new DataTable(); // New data table.
table.Columns.Add("Dosage", typeof(int)); // Add five columns.
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));

table.Rows.Add(15, "Abilify", "xxx", DateTime.Now); // Add five data rows.
table.Rows.Add(40, "Accupril", "yyy", DateTime.Now);
table.Rows.Add(40, "Accutane", "zzz", DateTime.Now);
table.Rows.Add(20, "Aciphex", "zyy", DateTime.Now);
table.Rows.Add(45, "Actos", "xxxy", DateTime.Now);

return table; // Return reference.
}


private void exportDataTableToExcel(DataTable dt, string filePath)
{
// Excel file Path
string myFile = filePath;

//System.Data.DataRow dr = default(System.Data.DataRow);

int colIndex = 0;
int rowIndex = 0;

// Open the file and write the headers
StreamWriter fs = new StreamWriter(myFile, false);

fs.WriteLine("<? xml version=\"1.0\"?>");
fs.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
fs.WriteLine("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");

// Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"1\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#254117\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column information
fs.WriteLine(" <ss:Style ss:ID=\"2\">");
fs.WriteLine(" <ss:Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"3\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#736AFF\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
fs.WriteLine(" </ss:Styles>");

// Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=\"Sheet1\">");
fs.WriteLine(" <ss:Table>");

fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{
fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"1\">" + "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", dc.ColumnName ));
}

fs.WriteLine(" </ss:Row>");

object cellText = null;

// Write contents for each cell
foreach (DataRow dr in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{
cellText = dr[dc];
// Check for null cell and change it to empty to avoid error
if (cellText == null ) cellText = "";
fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"2\">" +
"<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", cellText));
colIndex = colIndex + 1;
}
fs.WriteLine(" </ss:Row>");
}

fs.WriteLine(" <ss:Row>");
fs.WriteLine(" </ss:Row>");

// Close up the document
fs.WriteLine(" </ss:Table>");
fs.WriteLine("</ss:Worksheet>");
fs.WriteLine("</ss:Workbook>");
fs.Close();
}

Example:
exportDataTableToExcel (GetTable(),"C:\\PatientDetails.xls");
will write the content of data table with Formatting Styles.

5 comments:

Natan Rolnik said...

Hi,
I ported your code to Objective C to be able to write an xls file with the iPhone SDK. Although in Excel it opens correctly, the problem is that the file can't be opened in the mail preview (or any UIWebView) neither in the app Numbers.

Anonymous said...

Hi Natan,

Can u pls post the objective C code for this..

Anonymous said...

Pls provide the code for Objective-C.

Anonymous said...

provide the code for Objective-C.

LINDA SUSAN said...

Pls provide the code for Objective-C.