public static void ExportExcel(Letter[] data)
{
try
{
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Letters");
var row = sheet.FirstRow();
SetHeader(row);
row = row.RowBelow();
foreach (var letter in data)
{
SetData(row, letter);
row = row.RowBelow();
}
sheet.Columns().AdjustToContents();
var table = sheet.RangeUsed().CreateTable();
table.Theme = XLTableTheme.TableStyleMedium15;
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.FileName = "Letters";
if (dialog.ShowDialog() != DialogResult.OK) return;
workbook.SaveAs(dialog.FileName);
System.Diagnostics.Process.Start(dialog.FileName);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private static void SetData(IXLRow row, Letter data)
{
row.Cell(1).SetValue(data.Number).SetDataType(XLCellValues.Number);
row.Cell(2).SetValue(data.CreationDate).SetDataType(XLCellValues.DateTime);
row.Cell(3).SetValue(data.EventDate).SetDataType(XLCellValues.DateTime);
row.Cell(4).SetValue(data.LetterDate).SetDataType(XLCellValues.DateTime);
row.Cell(5).SetValue(data.Provider).SetDataType(XLCellValues.Text);
row.Cell(6).SetValue(data.Type).SetDataType(XLCellValues.Text);
row.Cell(7).SetValue(data.From).SetDataType(XLCellValues.Text);
row.Cell(8).SetValue(data.To).SetDataType(XLCellValues.Text);
row.Cell(9).SetValue(data.Money).SetDataType(XLCellValues.Number).Style.NumberFormat.SetFormat("0.00");
row.Cell(10).SetValue(data.Money2).SetDataType(XLCellValues.Number).Style.NumberFormat.SetFormat("0.00");
row.Cell(11).SetValue(data.User).SetDataType(XLCellValues.Text);
row.Cell(12).SetValue(data.Inc).SetDataType(XLCellValues.Number);
}
public Data.Letter[] GetLetters(string _Number, string _Provider, string _Type, string _From, string _To, string _Money, string _Money2, string _User, string _Inc,
DateType _DateType,DateTime startDate, DateTime endDate, DataLocation loc)
{
string query = @"SELECT * FROM #{TABLE}";
if (loc == DataLocation.Current)
query = query.Replace("#{TABLE}", "[Letter]");
else
if (loc == DataLocation.Archive)
query = query.Replace("#{TABLE}", "[Archive2014]");
else
{
query = query.Replace("#{TABLE}", "[Letter]");
query += " UNION SELECT * FROM [Archive2014]";
query = "SELECT * FROM (" + query;
query += ")";
}
query += " WHERE '1=1'";
OleDbCommand command = new OleDbCommand();
command.CommandText = query;
if (!string.IsNullOrEmpty(_Number))
{
command.CommandText += " AND [number] = @_Number";
command.Parameters.Add("@_Number", OleDbType.Integer).Value = _Number;
}
if (!string.IsNullOrEmpty(_Provider))
{
command.CommandText += " AND [provider] = @_Provider";
command.Parameters.Add("@_Provider", OleDbType.WChar).Value = _Provider;
}
if (!string.IsNullOrEmpty(_Type))
{
command.CommandText += " AND [type] = @_Type";
command.Parameters.Add("@_Type", OleDbType.WChar).Value = _Type;
}
if (!string.IsNullOrEmpty(_From))
{
command.CommandText += " AND [from] = @_From";
command.Parameters.Add("@_From", OleDbType.WChar).Value = _From;
}
if (!string.IsNullOrEmpty(_To))
{
command.CommandText += " AND [to] = @_To";
command.Parameters.Add("@_To", OleDbType.WChar).Value = _To;
}
if (!string.IsNullOrEmpty(_Money))
{
command.CommandText += " AND [money] = @_Money";
command.Parameters.Add("@_Money", OleDbType.WChar).Value = _Money;
}
if (!string.IsNullOrEmpty(_Money2))
{
command.CommandText += " AND [money2] = @_Money2";
command.Parameters.Add("@_Money2", OleDbType.WChar).Value = _Money2;
}
if (!string.IsNullOrEmpty(_User))
{
command.CommandText += " AND [user] = @_User";
command.Parameters.Add("@_User", OleDbType.WChar).Value = _User;
}
if (!string.IsNullOrEmpty(_Inc))
{
command.CommandText += " AND [inc] = @_Inc";
command.Parameters.Add("@_Inc", OleDbType.Numeric).Value = _Inc;
}
if (_DateType != DateType.All)
{
switch (_DateType)
{
case DateType.Creation: command.CommandText += " AND [cdate] Between @start AND @end";
command.Parameters.Add("@start", OleDbType.Date).Value = startDate;
command.Parameters.Add("@end", OleDbType.Date).Value = endDate;
break;
case DateType.Event: command.CommandText += " AND [edate] Between @start AND @end";
command.Parameters.Add("@start", OleDbType.Date).Value = startDate;
command.Parameters.Add("@end", OleDbType.Date).Value = endDate;
break;
case DateType.Letter: command.CommandText += " AND [ldate] Between @start AND @end";
command.Parameters.Add("@start", OleDbType.Date).Value = startDate;
command.Parameters.Add("@end", OleDbType.Date).Value = endDate;
break;
}
}
command.CommandText += " ORDER BY [number] DESC";
var table = GetData(command);
List<Data.Letter> list = new List<Data.Letter>();
if (table == null) return list.ToArray();
foreach (DataRow Row in table.Rows)
{
Data.Letter L = new Data.Letter();
L.Number = Convert.ToInt32(Row["number"]);
L.Provider = Row["provider"].ToString();
L.Type = Row["type"].ToString();
L.From = Row["from"].ToString();
L.To = Row["to"].ToString();
L.Money = Row["money"].ToString();
L.Money2 = Row["money2"].ToString();
L.User = Row["user"].ToString();
L.Inc = Row["inc"].ToString();
try { L.CreationDate = Convert.ToDateTime(Row["cdate"].ToString()); }
catch { L.CreationDate = new DateTime(0); }
try { L.EventDate = Convert.ToDateTime(Row["edate"].ToString()); }
catch { L.EventDate = new DateTime(0); }
try { L.LetterDate = Convert.ToDateTime(Row["ldate"].ToString()); }
catch { L.LetterDate = new DateTime(0); }
list.Add(L);
List<OleDbParameter> pars = new List<OleDbParameter>();
}
return list.ToArray();
}
public Data.Letter[] GetLetters(DataLocation loc)
{
string query = @"SELECT * FROM #{TABLE} ORDER BY [number] DESC";
if (loc == DataLocation.Current)
query = query.Replace("#{TABLE}", "[Letter]");
else
if (loc == DataLocation.Archive)
query = query.Replace("#{TABLE}", "[Archive2014]");
else
{
query = query.Replace("#{TABLE}", "[Letter]");
query += " UNION SELECT * FROM [Archive2014]";
}
List<Data.Letter> list = new List<Data.Letter>();
var table = GetData(query);
if (table == null) return list.ToArray();
foreach (DataRow Row in table.Rows)
{
Data.Letter L = new Data.Letter();
L.Number = Convert.ToInt32(Row["number"]);
L.Provider = Row["provider"].ToString();
L.Type = Row["type"].ToString();
L.From = Row["from"].ToString();
L.To = Row["to"].ToString();
L.Money = Row["money"].ToString();
L.Money2 = Row["money2"].ToString();
L.User = Row["user"].ToString();
L.Inc = Row["inc"].ToString();
try { L.CreationDate = Convert.ToDateTime(Row["cdate"].ToString()); }
catch { L.CreationDate = new DateTime(0); }
try { L.EventDate = Convert.ToDateTime(Row["edate"].ToString()); }
catch { L.EventDate = new DateTime(0); }
try { L.LetterDate = Convert.ToDateTime(Row["ldate"].ToString()); }
catch { L.LetterDate = new DateTime(0); }
list.Add(L);
}
return list.ToArray();
}
public bool Equals(Letter L)
{
return ((L.From == this.From) || ((string.IsNullOrEmpty(L.To)) ? false : L.To == this.To));
}
