using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace WpfApplication1
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
static DataTable GetTable()
{
// Here we create a DataTable with four columns.
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Data", typeof(double));
// Here we add five DataRows.
table.Rows.Add(25, "Indocin", "David", DateTime.Now,1212.222);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now,555.555);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now,55.34);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now,4545.4);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now,3333.3);
return table;
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
ExportToExcel(GetTable(),@"E:\\RastPro\\test.xlsx");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ExportToExcel( DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Workbooks.Add();
excelApp.Visible = true;
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("es-CL");
string dd = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();
CultureInfo cSystemCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo ci = new CultureInfo("es-CL");
excelApp.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
// excelApp.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
SetCellValue((Tbl.Rows[i][j]).ToString(), workSheet, i, j,ci);
//workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
private void SetCellValue(string data, Microsoft.Office.Interop.Excel._Worksheet ws, int row, int col, CultureInfo ci)
{
double val;
try
{
val = Convert.ToDouble(data);
Console.WriteLine(val);
}
catch (Exception e)
{
//Util.Log("Null Value ignored.", LogType.ERROR);
return;
}
try
{
string s = val.ToString();
ws.Cells[row + 2, col + 1] = s;
//Util.Log("S:" + s, LogType.ERROR);
}
catch
{
//Util.Log("Null Value ignored.", LogType.ERROR);
}
}
}
}
No comments:
Post a Comment