예제 #1
0
 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); }
 }
예제 #2
0
 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);
 }
예제 #3
0
        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();
        }
예제 #4
0
 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();
 }
예제 #5
0
 public bool Equals(Letter L)
 {
     return ((L.From == this.From) || ((string.IsNullOrEmpty(L.To)) ? false : L.To == this.To));
 }