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(); }
}
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(); }
}