Пример #1
0
        public static void Append(RequestPackage rqp)
        {
            try
            {
                Guid guid = Guid.NewGuid();
                if (!String.IsNullOrWhiteSpace(rqp["f0"] as String))
                {
                    Guid.TryParse((rqp["f0"] as String), out guid);
                }

                String f0 = System.Convert.ToBase64String(guid.ToByteArray()).Substring(0, 22);

                XlConnection.Open();
                Excel.Worksheet sheet = XlConnection.Sheet;
                // ищем первую пустую строку начиная с третьей
                int ri;
                for (ri = 3; ri < 10000; ri++)
                {
                    if (sheet.Cells[ri, 1].Value == null)
                    {
                        break;
                    }
                }
                if (ri < 10000)
                {
                    // добавляем ячейки
                    Excel.Range row = sheet.Range[sheet.Cells[ri, 1], sheet.Cells[ri, 19]];

                    sheet.Unprotect();
                    XlConnection.App.EnableEvents = false;
                    row[1, 1] = f0;
                    XlConnection.App.EnableEvents = true;
                    sheet.Protect();

                    row[1, 4] = rqp["f3"] as String;
                    //row[1, 5] = rqp["f4"] as String; // сама обновится через макрос Exel
                    row[1, 6]  = rqp["f5"] as String;
                    row[1, 7]  = rqp["f6"] as String;
                    row[1, 8]  = rqp["f7"] as String;
                    row[1, 9]  = XlConvert.ToXlDatetime(rqp["f8"] as String);
                    row[1, 10] = XlConvert.ToXlDatetime(rqp["f9"] as String);
                    row[1, 11] = XlConvert.ToXlDatetime(rqp["f10"] as String);
                    row[1, 12] = XlConvert.ToXlDatetime(rqp["f11"] as String);
                    row[1, 13] = rqp["f12"] as String;
                    row[1, 14] = rqp["f13"] as String;
                    row[1, 15] = rqp["f14"] as String;
                    row[1, 16] = XlConvert.ToXlFloat(rqp["f15"] as String);
                    row[1, 17] = XlConvert.ToXlInt(rqp["f16"] as String);
                    row[1, 18] = rqp["f17"] as String;
                    row[1, 19] = rqp["f18"] as String;
                    XlConnection.App.DisplayAlerts = false;
                    XlConnection.Book.Save();
                }
            }
            catch (Exception ex)
            {
                Log.Write("XlStoredProcedure.Append(): " + ex.ToString());
            }
            finally { XlConnection.Close(); }
        }
Пример #2
0
        public static void Update(RequestPackage rqp)
        {
            try
            {
                Guid   guid = Guid.Parse(rqp["f0"] as String);
                String f0   = System.Convert.ToBase64String(guid.ToByteArray()).Substring(0, 22);

                XlConnection.Open();

                Excel.Worksheet sheet     = XlConnection.Sheet;
                Excel.Range     usedRange = sheet.UsedRange;
                Excel.Range     cell      = usedRange.Find(f0);
                if (cell != null) // нашли - исправляем уже существующую строку
                {
                    Excel.Range row = sheet.Range[sheet.Cells[cell.Row, 1], sheet.Cells[cell.Row, 19]];
                    row[1, 4]  = rqp["f3"] as String;
                    row[1, 5]  = rqp["f4"] as String;
                    row[1, 6]  = rqp["f5"] as String;
                    row[1, 7]  = rqp["f6"] as String;
                    row[1, 8]  = rqp["f7"] as String;
                    row[1, 9]  = XlConvert.ToXlDatetime(rqp["f8"] as String);
                    row[1, 10] = XlConvert.ToXlDatetime(rqp["f9"] as String);
                    row[1, 11] = XlConvert.ToXlDatetime(rqp["f10"] as String);
                    row[1, 12] = XlConvert.ToXlDatetime(rqp["f11"] as String);
                    row[1, 13] = rqp["f12"] as String;
                    row[1, 14] = rqp["f13"] as String;
                    row[1, 15] = rqp["f14"] as String;
                    row[1, 16] = XlConvert.ToXlFloat(rqp["f15"] as String);
                    row[1, 17] = XlConvert.ToXlInt(rqp["f16"] as String);
                    row[1, 18] = rqp["f17"] as String;
                    row[1, 19] = rqp["f18"] as String;
                    XlConnection.App.DisplayAlerts = false;
                    XlConnection.Book.Save();
                }
            }
            catch (Exception ex)
            {
                Log.Write("XlStoredProcedure.Update(): " + ex.ToString());
            }
            finally { XlConnection.Close(); }
        }