public static void HttpRequestPost(string url, string apiId, string from, string to, string timestamp,
string text, string charset, string udh, string moMsgId)
{
const string httpXForwardedForKey = "HTTP_X_FORWARDED_FOR";
const string remoteAddr = "REMOTE_ADDR";
try
{
WebClient myWebClient = new WebClient();
NameValueCollection myNameValueCollection = new NameValueCollection();
myNameValueCollection.Add("api_id", apiId);
myNameValueCollection.Add("from", from);
myNameValueCollection.Add("to", to);
myNameValueCollection.Add("timestamp", timestamp);
myNameValueCollection.Add("text", text);
myNameValueCollection.Add("charset", charset);
myNameValueCollection.Add("udh", udh);
myNameValueCollection.Add("moMsgId", moMsgId);
myWebClient.Headers.Add(httpXForwardedForKey, "64.129.32.194");
myWebClient.Headers.Add(remoteAddr, "192.168.8.2");
//myWebClient.Headers.Add(remoteAddr, "64.129.32.194");
byte[] responseArray = myWebClient.UploadValues(url, "POST", myNameValueCollection);
string response = Encoding.ASCII.GetString(responseArray);
LsiLogger.Trace(string.Format("Response received was:{0}{1}", Environment.NewLine, response));
}
catch (Exception ex)
{
LsiLogger.Trace(string.Format("Error while create request:{0}{1}", Environment.NewLine, ex));
}
}
public static void CreateSpierXls(Spire.Xls.ExcelVersion version, bool withFilter,
DataTable table, string dir)
{
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("SpierXls_{0}", version)));
DateTime begin = DateTime.Now;
Workbook workbook = new Workbook();
workbook.Version = version;
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertDataTable(table, true, 1, 1, -1, -1);
if (withFilter)
{
sheet.AutoFilters.Range = sheet.Range[1, 1, table.Rows.Count, table.Columns.Count];
}
MemoryStream ms = new MemoryStream();
workbook.SaveToStream(ms);
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime end = DateTime.Now;
LsiLogger.Trace(string.Format("Duration of CreateSpierXls(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
}
public static void SaveSmartXLS(bool isXlsx, bool withFilter, DataTable data, string dir)
{
string filePath = Path.Combine(dir, FileName.Replace(NameKey, isXlsx ? "SmartXLSX" : "SmartXLS"));
DateTime begin = DateTime.Now;
//LsiLogger.Trace("Begin of SaveSmartXLS(...)");
WorkBook xls = new WorkBook();
xls.ImportDataTable(data, true, 0, 0, data.Rows.Count, data.Columns.Count);
//xls.write(filePath);
if (withFilter)
{
xls.autoFilter();
}
byte[] xlsData;
using (MemoryStream output = new MemoryStream())
{
if (!isXlsx)
{
xls.write(output);
}
else
{
xls.writeXLSX(output);
}
xlsData = output.ToArray();
}
SaveFile(xlsData, filePath);
//LsiLogger.Trace("End of SaveSmartXLS(...)");
DateTime end = DateTime.Now;
LsiLogger.Trace(string.Format("Duration of SaveSmartXLS(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
}
public int?StartInsertLocation(int clientUid, int lrsRestaurantId, string name)
{
LsiLogger.Trace("Start Inserting location to srv postgre db");
int?id = MyConvert.ToIntN(DoExecuteScalar(locationInsertTemplate, clientUid, lrsRestaurantId, name));
LsiLogger.Trace("End Inserting location to srv postgre db");
return(id);
}
public int?StartInsertClient(string name)
{
LsiLogger.Trace("Start Inserting client to srv postgre db");
int?id = MyConvert.ToIntN(DoExecuteScalar(clientInsertTemplate, name, name));
LsiLogger.Trace("End Inserting client to srv postgre db");
return(id);
}
/*
* //string guid = "123456789";
* //string a = guid.Insert(3, "-").Insert(7, "-");
* //return;
* Dictionary<string, int> dict = new Dictionary<string, int>();
* using (StreamReader sr = new StreamReader(args[0], Encoding.UTF8))
* {
* string line;
* while ((line = sr.ReadLine()) != null)
* {
* // <GetCountriesResponse xmlns="lsisoftware.pl">
* MatchCollection maches = new Regex(@"^<(.+)\s+xmlns=\""lsisoftware.pl\"".*$").Matches(line.Trim());
* if (maches.Count > 0 && maches[0].Groups.Count > 1)
* {
* string ip = maches[0].Groups[1].ToString();
* if (dict.ContainsKey(ip))
* dict[ip]++;
* else
* dict.Add(ip, 1);
* }
* }
* }
*
* StringBuilder sb = new StringBuilder();
* foreach (KeyValuePair<string, int> keyValuePair in dict)
* sb.AppendLine(string.Format("IP:{0}\tCount:{1}", keyValuePair.Key, keyValuePair.Value));
*
* using (StreamWriter sr = File.CreateText(string.Format("{0}_OUT", args[0])))
* {
* sr.Write(sb);
* sr.Flush();
* sr.Close();
* }
*
*
*
* return;*/
//HTTP_X_FORWARDED_FOR:[41.192.255.122]
/**/
//PostgreSqlTest(args[0]);
//return;
/*string dir = @"d:\Projects\Testy\logs\DataExportTest";
* long dataAmount = 10;
* //LsiLogger.Trace(string.Format("Data amount:[{0}]", dataAmount));
* DataTable dt = ResponsesDAL.GetResponseRawDataTF(dataAmount);
* if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
* LsiLogger.Trace(string.Format("Real data amount:[{0}]", dt.Rows.Count));
*
* int type = 6;
* switch (type)
* {
* case 1:
* //ExcelTest.SaveSmartXLS(false, false, dt, dir);
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel97to2003, false, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version97to2003, false, dt, dir);
* break;
* case 2:
* //ExcelTest.SaveSmartXLS(false, true, dt, dir);
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel97to2003, true, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version97to2003, true, dt, dir);
* break;
* case 3:
* //ExcelTest.SaveSmartXLS(true, false, dt, dir);
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel2007, false, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version2007, false, dt, dir);
* break;
* case 4:
* //ExcelTest.SaveSmartXLS(true, true, dt, dir);
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel2007, true, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version2007, true, dt, dir);
* break;
* case 5:
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel2010, false, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version2010, false, dt, dir);
* break;
* case 6:
* ExcelTest.CreateSurveyExcelSyncfusion3(ExcelVersion.Excel2010, true, dt, dir);
* //ExcelTest.CreateSpierXls(Spire.Xls.ExcelVersion.Version2010, true, dt, dir);
* break;
* }*/
//ExcelTest.SaveSmartXLS(false, false, dt, Path.Combine(dir, fileName.Replace(nameKey, "SmartXLS")));
//ExcelTest.SaveSmartXLS(false, true, dt, Path.Combine(dir, fileName.Replace(nameKey, "SmartXLS_Filter")));
//ExcelTest.SaveSmartXLS(true, false, dt, Path.Combine(dir, fileName.Replace(nameKey, "SmartXLSX")));
//ExcelTest.SaveSmartXLS(true, true, dt, Path.Combine(dir, fileName.Replace(nameKey, "SmartXLSX_Filter")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel97to2003, false, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel97to2003")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel97to2003, true, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel97to22003")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel2007, false, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel2007")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel2007, true, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel2007")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel2010, false, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel2010")));
//ExcelTest.CreateSurveyExcelSyncfusion(ExcelVersion.Excel2010, true, dt, Path.Combine(dir, fileName.Replace(nameKey, "Syncfusion_Excel2010")));
//return;
#region Old
/*string message = "TF:[TEST]";
* UnicodeEncoding unicode = new UnicodeEncoding();
* //UTF8Encoding utf8 = new UTF8Encoding();
* Byte[] encodedBytes = unicode.GetBytes(message);
* message = UnicodeEncoding.Unicode.GetString(encodedBytes);
* string xmlData = "<>\"'&[]!@#$%^&*()";
* string escapedXml = SecurityElement.Escape(xmlData);
*
*
* DateTime serverTime = DateTime.Now;
* DateTime myTime = DateTime.Now.AddSeconds(5);
*
* int sec = (int) serverTime.Subtract(myTime).TotalMilliseconds;
* myTime = myTime.AddMilliseconds(sec);
* int sec1 = (int)serverTime.Subtract(myTime).TotalMilliseconds;*/
//dateIn.Subtract(dateOut)
/* LRSTablerTrackerRequest r = new LRSTablerTrackerRequest();
* r.Configuration = new LRSTablerTrackerConfigurationDto();
* r.Configuration.IsSecondWarningTime = 1;
* r.Orders = new LRSTableTrackerOrderDto[1];
* r.Orders[0] = new LRSTableTrackerOrderDto();
* r.Orders[0].StartTime = DateTime.Now;*/
//new TT.TableTracker().SaveDataAndConfig(r);
/*string md5 = GetMD5Hash("31337");
* if ("900150983cd24fb0d6963f7d28e17f72" == md5)
* Console.Out.WriteLine("OK");
* else
* Console.Out.WriteLine("Error");
* return;*/
/*string strDt = "2011-11-11 11:11:11";
* //DateTime dt = new DateTime(2011,11,11,11,11,11);
* DateTime dt = DateTime.Parse(strDt);
* long epoch = Epoch(dt);
* return;*/
/*long locationId = 28;
* int no = 106;
*
*
* WLTableDto dto1 = new WLTableDto();
* dto1.WLLocationId = locationId;
* dto1.Number = no.ToString();
* dto1.WLDictServiceDeviceId = 1;
*
* WLTableDto dto2 = new WLTableDto();
* dto2.WLLocationId = locationId;
* dto2.Number = (no + 1).ToString();
* dto2.WLDictServiceDeviceId = 1;
*
* new WLTableController().DoubleSave(dto1, dto2);*/
/*
* string CurrentStartTime = "2011-11-14 09:45:33";
* DateTime startTime;
* if (!string.IsNullOrEmpty(CurrentStartTime) && DateTime.TryParse(CurrentStartTime, out startTime))
* {
* DateTime dt = startTime;
* Console.Out.WriteLine(dt.ToString());
* }
*/
//string srvTime = DateTime.Now.ToUniversalTime().ToString("s");
/*
* MyTest my = new MyTest();
* my.Date = DateTime.Now;
* my.Id = 1;
* string xml = Serialize(my, true, true, true);
*
* MyTest my1 = (MyTest) Deserialize(@"d:\!TF\______________Pulpit\MyTest.xml",typeof(MyTest));
* DateTime utc = my1.Date.ToUniversalTime();
*
* string date = "2011-11-09T15:59:29.451+01:00";
* DateTime dt = DateTime.Parse(date);
* DateTime dtOk = new DateTime(dt.Ticks, DateTimeKind.Local);
* string time = dt.ToString();
* string timeOk = dtOk.ToString();
*/
//string response = "0;2011-10-04T16:50:55;120;1";
//var timeAndGoalData = response.Split('#')[0].Split(';');
//DateTime? dt = GetDateTimeFromDateString(response);
//var howManyCar = response.Count(character => character.Equals('#')) - 1;
//string[] car = response.Split('#');
//return;
////string a = DateTime.Now.ToUniversalTime().ToString("s");
//return;
//TestTimeZones();
//return;
///string guid = "CB1231250107404AA38D5CC79D6C6758";
//ws.Lenovo srv = new Lenovo();
//ActionReturnOfLocationInfo ret = srv.LocationCheck(guid, "1.0");
//ActionReturnOfLocationConfig config = srv.GetConfig(guid);
/*ManagementObjectSearcher s = new ManagementObjectSearcher("SELECT * FROM Win32_Product");
* foreach (ManagementBaseObject baseObject in s.Get())
* {
* object obj = baseObject;
* }
*
*
* return;
*
*
*
*
*
* //string guid = "0123456789QWERTYUIOPASDFGHJKLZXCVBNM<>{}:<,,./;'[]sdAfsBd-fs!d@f#$%^&*()_+";
* //guid = Regex.Replace(guid, "[^a-zA-Z0-9]", string.Empty);
*
* //string file = @"d:\SmsHub\_logs\LsiSMSServer.log";
* //ReadLockeedFile(file);
*
*
* //new Hub().SendMessage("CAS", "+48788278291", "test 2011-06-02 2-UK", "7", true, "MyMessageId");
*
* //TestTimeZones_KK();
*
* /*string guid = Guid.NewGuid().ToString();
*
* //string filePath = @"d:\Projects\REPO_NET\trunk\LsiLogFileListener\LsiLogFileListener\Logs\Test.log";
* string filePath = @"\\jposkrobko2\c$\smshub\_logs\LsiSMSServer.log";
*
*
* try
* {
* File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.None);
* }
* catch (Exception exp)
* {
* exp.ToString();
*
* }*/
/*using (StreamReader sr = new StreamReader(filePath, Encoding.UTF8))
* {
*
* }*/
/*foreach (TzTimeZone.TzZoneInfo info in TzTimeZone.ZoneList)
* {
* Console.Out.WriteLine(info.ZoneName);
*
* }*/
//TestTimeZones();
////string urlRequest = @"http://smsproxy.pager.net/SmsMailHandler.ashx#%20ERROR:%20In%20Method%20MainLogger;%20after%20time:%2000:00:00.0468750System.Reflection.TargetInvocationExceptio%23%20%23&subject=Error%20in%20TF%20test.log%20-%20ERROR:%20In%20Method%20MainLogger;%20after%20time:%2000:00:00.0468750System.Reflection.TargetInvocationExceptio%23%20ERROR:%20In%20Method%20MainLogger;%20after%20time:%2000:00:00.0468750System.Reflection.TargetInvocationExceptio%23%20%23&[email protected]";
//string urlRequest = "http://smsproxy.pager.net/SmsMailHandler.ashx?guid=9feb3f8bd99a407c86c2e2d14cef3ffa&msg=Error in TF test.log - ERROR: In Method MainLogger; after time: 00:00:00.0468750System.Reflection.TargetInvocationExceptio ERROR: In Method MainLogger; after time: 00:00:00.0468750System.Reflection.TargetInvocationExceptio &subject=Error in TF test.log - ERROR: In Method MainLogger; after time: 00:00:00.0468750System.Reflection.TargetInvocationExceptio ERROR: In Method MainLogger; after time: 00:00:00.0468750System.Reflection.TargetInvocationExceptio &[email protected]";
//Uri uri = new Uri(urlRequest);
//WebClient myWebClient = new WebClient();
//NameValueCollection myNameValueCollection = new NameValueCollection();
//if (uri.Query.Length > 0)
//{
// foreach (string item in uri.Query.Split('&'))
// {
// string[] parts = item.Replace("?", string.Empty).Split('=');
// if (parts.Length == 2)
// {
// //LsiLogger.Trace(string.Format("Add(Name:[{0}],Value:[{1}]);", parts[0], parts[1]));
// myNameValueCollection.Add(parts[0], parts[1]);
// }
// }
//}
////string a = Guid.NewGuid().ToString();
//DateTime lastMody = new DateTime(2011, 05, 03, 21, 15, 59, 120);
//DateTime creation = new DateTime(2011, 05, 03, 21, 15, 59, 121);
//double milisek = lastMody.Subtract(creation).TotalMilliseconds;
//if (lastMody.Subtract(creation).TotalMilliseconds < 0)
//{
// string a = "set Offset =0";
//}
///*string file1 = @"d:\!TF\Logs\2169_Calls\test\OUT\20110415_2169_LRSDB2_Requests.log";
//string file2 = @"d:\!TF\Logs\2169_Calls\test\OUT\20110415_2169_LRSDB2_Responses.log";
//string dest = @"d:\!TF\Logs\2169_Calls\test\OUT\20110415_2169_LRSDB2.log";
//JoinFiles(file1, file2, dest);*/
#endregion Old
private static void GetComTransactionconfig(string path)
{
path = @"d:\Projects\REPO_LRS\trunk\LrsPlayPaq\PlaypaqWeb\PlayPaqWebBll";
if (Directory.Exists(path))
{
//string fileMask = "LRSCASCallController.cs";
string fileMask = "*.cs";
string[] files = Directory.GetFiles(path, fileMask, SearchOption.AllDirectories);
StringBuilder info = new StringBuilder();
foreach (string file in files)
{
bool isFileNameAdded = false;
if (File.Exists(file))
{
string[] lines = File.ReadAllLines(file);
foreach (string line in lines)
{
Match match = new Regex(@"^.*(\[Transaction\(.+\)\]).*$").Match(line);
if (match != null && match.Groups != null && match.Groups.Count > 1 &&
!string.IsNullOrEmpty(match.Groups[1].Value))
{
if (!isFileNameAdded)
{
info.AppendLine();
info.AppendLine(Path.GetFileName(file));
isFileNameAdded = true;
}
info.AppendLine(match.Groups[1].Value);
}
}
}
if (isFileNameAdded)
{
info.AppendLine();
}
}
LsiLogger.Trace(info.ToString());
}
}
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir)
{
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version)));
DateTime begin = DateTime.Now;
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = version;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Header
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
headerStyle.Color = Color.FromArgb(239, 243, 247);
//headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 12;
headerStyle.Color = Color.FromArgb(192, 192, 192);
headerStyle.Locked = true;
headerStyle.Font.FontName = "Arial";
headerStyle.Font.Color = ExcelKnownColors.Black;
headerStyle.Borders.LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
headerStyle.EndUpdate();
worksheet.SetDefaultRowStyle(1, headerStyle);
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.RowHeight = 36.75;
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Body
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204));
bodyStyle.Color = Color.FromArgb(255, 255, 204);
bodyStyle.Font.Size = 10;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Font.FontName = "Arial";
bodyStyle.Font.Bold = false;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
//bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
data.RowHeight = 31.50;
//IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle;
//DateTime beginFillPattern = DateTime.Now;
//data.CellStyle.FillPattern = ExcelPattern.Solid;
//DateTime endFillPattern = DateTime.Now;
//messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern)));
//worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm";
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i);
}
worksheet.SetColumnWidth(5, 18.00);
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb));
}
public static void CreateSurveyExcelSyncfusion0(DataTable table, string dir)
{
DateTime begin = DateTime.Now;
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, "Syncfusion"));
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//GlobalStyles
DateTime beginDateTimeFormat = DateTime.Now;
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
//dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt";
dateTimeColl.NumberFormat = "mm/dd/yyyy hh:m am/pm";
messages.Add(string.Format("DateTimeFormat:\t{0}", (DateTime.Now - beginDateTimeFormat)));
//Header
DateTime beginHeaderFormat = DateTime.Now;
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.CellStyle.Font.Bold = true;
header.CellStyle.Font.Size = 12;
header.CellStyle.Font.FontName = "Arial";
header.CellStyle.Font.RGBColor = Color.Black;
header.CellStyle.Color = Color.FromArgb(192, 192, 192);
header.CellStyle.Locked = true;
header.RowHeight = 36.75;
header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
messages.Add(string.Format("HeaderFormat:\t{0}\n", (DateTime.Now - beginHeaderFormat)));
//Body
DateTime beginBodyFormat = DateTime.Now;
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
data.CellStyle.Font.Size = 10;
data.RowHeight = 31.50;
data.CellStyle.Color = Color.FromArgb(255, 255, 204);
data.VerticalAlignment = ExcelVAlign.VAlignCenter;
data.CellStyle.Font.FontName = "Arial";
data.CellStyle.Font.RGBColor = Color.Black;
data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
messages.Add(string.Format("BodyFormat:\t{0}\n", (DateTime.Now - beginBodyFormat)));
DateTime beginAutoFilters = DateTime.Now;
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
messages.Add(string.Format("AutoFilters:\t{0}", (DateTime.Now - beginAutoFilters)));
DateTime beginImport = DateTime.Now;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
messages.Add(string.Format("Import:\t{0}", (DateTime.Now - beginImport)));
DateTime beginAutofitColumn = DateTime.Now;
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i);
}
messages.Add(string.Format("AutofitColumn:\t{0}", (DateTime.Now - beginAutofitColumn)));
MemoryStream ms = new MemoryStream();
DateTime beginSaveAsMemoryStream = DateTime.Now;
workbook.SaveAs(ms);
DateTime endSaveAsMemoryStream = DateTime.Now;
messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream)));
//workbook.Close();
//excelEngine.ThrowNotSavedOnDestroy = false;
//excelEngine.Dispose();
DateTime beginSaveFile = DateTime.Now;
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime endSaveFile = DateTime.Now;
messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile)));
//LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)");
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion steps\n{0}", sb));
}
public static void CreateSurveyExcelSyncfusion2(ExcelVersion version, bool withFilter, DataTable table, string dir)
{
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version)));
DateTime begin = DateTime.Now;
//LsiLogger.Trace("Begin of CreateSurveyExcelSyncfusion(...)");
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = version;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime beginColumnNames = DateTime.Now;
worksheet.Range["A1"].Text = "Uid";
worksheet.Range["B1"].Text = "Suid";
worksheet.Range["C1"].Text = "Survey";
worksheet.Range["D1"].Text = "Location";
worksheet.Range["E1"].Text = "Date / Time Name";
worksheet.Range["F1"].Text = "Prompt 1";
worksheet.Range["G1"].Text = "Prompt 2";
worksheet.Range["H1"].Text = "Prompt 3";
worksheet.Range["I1"].Text = "Prompt 4";
worksheet.Range["J1"].Text = "Prompt 5";
worksheet.Range["K1"].Text = "Duration (sec)";
worksheet.Range["L1"].Text = "Expired";
DateTime endColumnNames = DateTime.Now;
messages.Add(string.Format("ColumnNames:\t{0}", (endColumnNames - beginColumnNames)));
DateTime beginDateTimeFormat = DateTime.Now;
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
//dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt";
dateTimeColl.NumberFormat = "mm/dd/yyyy hh:mm";
DateTime endDateTimeFormat = DateTime.Now;
messages.Add(string.Format("DateTimeFormat:\t{0}", (endDateTimeFormat - beginDateTimeFormat)));
//Header
DateTime beginHeaderFormat = DateTime.Now;
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.CellStyle.Font.Bold = true;
header.CellStyle.Font.Size = 12;
header.RowHeight = 36.75;
header.CellStyle.Color = Color.FromArgb(192, 192, 192);
header.CellStyle.Locked = true;
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
header.CellStyle.Font.FontName = "Arial";
header.CellStyle.Font.RGBColor = Color.Black;
header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
DateTime endHeaderFormat = DateTime.Now;
messages.Add(string.Format("HeaderFormat:\t{0}\n", (endHeaderFormat - beginHeaderFormat)));
//Body
DateTime beginBodyFormat = DateTime.Now;
for (int i = 0; i < table.Rows.Count + 1; i++)
{
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2 + i, 1, 2 + i, table.Columns.Count]);
//IRanges data = worksheet.CreateRangesCollection();
//data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
DateTime beginLineStyle = DateTime.Now;
data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
DateTime endLineStyle = DateTime.Now;
messages.Add(string.Format("\tLineStyle:\t{0}", (endLineStyle - beginLineStyle)));
DateTime beginDiagonalDown = DateTime.Now;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
DateTime endDiagonalDown = DateTime.Now;
messages.Add(string.Format("\tDiagonalDown:\t{0}", (endDiagonalDown - beginDiagonalDown)));
DateTime beginDiagonalUp = DateTime.Now;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
DateTime endDiagonalUp = DateTime.Now;
messages.Add(string.Format("\tDiagonalUp:\t{0}", (endDiagonalUp - beginDiagonalUp)));
IFont font = data.CellStyle.Font;
DateTime beginFontName = DateTime.Now;
//data.CellStyle.Font.FontName = "Arial";
font.FontName = "Arial";
DateTime endFontName = DateTime.Now;
messages.Add(string.Format("\tFontName:\t{0}", (endFontName - beginFontName)));
DateTime beginFontSize = DateTime.Now;
//data.CellStyle.Font.Size = 10;
font.Size = 10;
DateTime endFontSize = DateTime.Now;
messages.Add(string.Format("\tFontSize:\t{0}", (endFontSize - beginFontSize)));
DateTime beginFontRGBColor = DateTime.Now;
//data.CellStyle.Font.RGBColor = Color.Black;
font.Color = ExcelKnownColors.Black;
DateTime endFontRGBColor = DateTime.Now;
messages.Add(string.Format("\tFontRGBColor:\t{0}", (endFontRGBColor - beginFontRGBColor)));
DateTime beginVerticalAlignment = DateTime.Now;
data.VerticalAlignment = ExcelVAlign.VAlignCenter;
DateTime endVerticalAlignment = DateTime.Now;
messages.Add(string.Format("\tVerticalAlignment:\t{0}", (endVerticalAlignment - beginVerticalAlignment)));
DateTime beginFillPattern = DateTime.Now;
data.CellStyle.FillPattern = ExcelPattern.Solid;
DateTime endFillPattern = DateTime.Now;
messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern)));
DateTime beginRowHeight = DateTime.Now;
//data.RowHeight = 31.50;
worksheet.SetRowHeight(i + 2, 31.5);
DateTime endRowHeight = DateTime.Now;
messages.Add(string.Format("\tRowHeight:\t{0}", (endRowHeight - beginRowHeight)));
DateTime beginColor = DateTime.Now;
data.CellStyle.Color = Color.FromArgb(255, 255, 204);
DateTime endColor = DateTime.Now;
messages.Add(string.Format("\tColor:\t{0}", (endColor - beginColor)));
}
DateTime endBodyFormat = DateTime.Now;
messages.Add("\t---------------------------");
messages.Add(string.Format("BodyFormat:\t{0}\n", (endBodyFormat - beginBodyFormat)));
if (withFilter)
{
DateTime beginAutoFilters = DateTime.Now;
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
DateTime endAutoFilters = DateTime.Now;
messages.Add(string.Format("AutoFilters:\t{0}", (endAutoFilters - beginAutoFilters)));
}
DateTime beginImport = DateTime.Now;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
DateTime endImport = DateTime.Now;
messages.Add(string.Format("Import:\t{0}", (endImport - beginImport)));
DateTime beginAutofitColumn = DateTime.Now;
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i); //10000-7sec.
}
DateTime endAutofitColumn = DateTime.Now;
messages.Add(string.Format("AutofitColumn:\t{0}", (endAutofitColumn - beginAutofitColumn)));
MemoryStream ms = new MemoryStream();
DateTime beginSaveAsMemoryStream = DateTime.Now;
workbook.SaveAs(ms);
DateTime endSaveAsMemoryStream = DateTime.Now;
messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream)));
//workbook.Close();
//excelEngine.ThrowNotSavedOnDestroy = false;
//excelEngine.Dispose();
DateTime beginSaveFile = DateTime.Now;
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime endSaveFile = DateTime.Now;
messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile)));
//LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)");
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion2(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion2 steps\n{0}", sb));
}
public static bool Srv2SoapParser(string filePath)
{
Collection <InfoObj> infos = new Collection <InfoObj>();
int lineCouinter = 1;
bool retValue = false;
if (File.Exists(filePath))
{
const string newLinePattern = @"^\[Trace\]\[\d{4}-\d{2}-\d{2}\s{1}\d{2}:\d{2}:\d{2}.\d{4}\].*";
const string startRequestStr = "<SaveSurveyResult xmlns=\"Srv2\">";
const string endRequestStr = "<SaveSurveyResultResponse xmlns=\"Srv2\">";
Collection <string> origLines = ReadFileLines(filePath);
Collection <string> lines = SplitFileByRegEx(origLines, newLinePattern);
/*string fileBody = File.ReadAllText(filePath);
* string[] lines = new Regex(newLinePattern, RegexOptions.Multiline).Split(fileBody);*/
if (lines != null && lines.Count > 0)
//if (lines != null && lines.Length > 0)
{
InfoObj info = null;
int loopCounter = 0;
foreach (string line in lines)
{
if (line.ToUpper().Contains(startRequestStr.ToUpper()))
{
if (info != null)
{
infos.Add(info);
}
info = new InfoObj();
info.Line = lineCouinter;
DateTime?startDate = GetDateFromLine(line);
if (startDate.HasValue)
{
info.Start = startDate.Value;
}
loopCounter++;
if (loopCounter == lines.Count && info != null)
{
infos.Add(info);
}
continue;
}
if (line.ToUpper().Contains(endRequestStr.ToUpper()))
{
if (info != null)
{
DateTime?endDate = GetDateFromLine(line);
if (endDate.HasValue)
{
info.End = endDate.Value;
}
int?result = GetResultFromLine(line);
if (result.HasValue)
{
info.Result = result.Value;
}
}
loopCounter++;
lineCouinter++;
if (loopCounter == lines.Count && info != null)
{
infos.Add(info);
}
continue;
}
}
StringBuilder sb = new StringBuilder();
if (infos.Count > 0)
{
foreach (InfoObj obj in infos)
{
sb.AppendLine(obj.ToString());
}
}
LsiLogger.Trace(sb.ToString(), "SOPA_Result");
retValue = true;
}
}
else
{
retValue = false;
}
return(retValue);
}
public void StartInsertBases(int serialNo, int locationUid)
{
LsiLogger.Trace("Start Inserting Bases to srv postgre db");
DoExecuteScalar(baseInsertTemplate, serialNo, locationUid);
LsiLogger.Trace("End Inserting Bases to srv postgre db");
}