/// <summary> /// Write the query result set(s) to the specified filename, starting a new worksheet for each resultset. /// Will exit with the query still open if MaximumResultSetsPerWorkbook is exceeded. /// </summary> public WorkBookStatus WriteQueryResults(string path) { using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.None)) { WorkBookStatus t = WriteResults(fs); return(t); } }
/// <summary> /// Write results to separate tabs. /// If MaximumResultSetsPerWorkbook > count of select statements, it'll create auto-numbered output files. /// </summary> /// <param name="p"></param> void WriteResultsToSeparateTabs(ExcelXmlQueryResultsParams p) { WorkBookParams workbookParams = p.e; string destinationFileName = p.filenm; // open wb Workbook wb = new Workbook(workbookParams); // subscribe to progress events wb.ReaderFinished += new EventHandler <ReaderFinishedEvents>(wb_ReaderFinished); wb.QueryStarted += new EventHandler <EventArgs>(wb_QueryStarted); wb.QueryException += new EventHandler <QueryExceptionEvents>(wb_QueryError); wb.QueryRowsOverTime += new EventHandler <QueryRowsOverTimeEvents>(wb_QueryRowsOverTime); wb.SaveFile += new EventHandler <SaveFileEvent>(wb_SaveBegan); if (wb.RunQuery()) { int currentFileCount = 0; var r = new Regex(@"(select)\s+", RegexOptions.IgnoreCase); var m = r.Match(workbookParams.Query); var countOfResultSets = r.Matches(workbookParams.Query).Count; // append "_000..." to the filename if we're writing more than workbook // if we're writing < 10 results, we'll rename them _0, _1, ... up to _9 // if we're writing > 10 but < 100 results, we'll rename them _00, _01, ... up to _99 // etc. var padleft = 0; var modifiableFileName = destinationFileName; if (workbookParams.MaximumResultSetsPerWorkbook < countOfResultSets) { padleft = countOfResultSets.ToString().Length + 1; currentFileCount = currentFileCount + 1; modifiableFileName = Utility.getIncrPaddedFileName(currentFileCount, destinationFileName, padleft); } WorkBookStatus status = wb.WriteQueryResults(modifiableFileName); while (status != WorkBookStatus.Completed) { currentFileCount = currentFileCount + 1; if (workbookParams.MaximumResultSetsPerWorkbook < countOfResultSets) { modifiableFileName = Utility.getIncrPaddedFileName(currentFileCount, destinationFileName, padleft); } else { modifiableFileName = Utility.getIncrFileName(currentFileCount, destinationFileName); } status = wb.WriteQueryResults(modifiableFileName); } } }
public void WorkbookQueryMaxResultsPerWorkbookTest() { WorkBookParams p = new WorkBookParams(); string path = Assembly.GetExecutingAssembly().Location; path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); string tempOutputPath = Path.Combine(path, "ExcelXmlWriterNTest", "bin", "Debug"); //tempOutputPath = Path.Combine(path, Utility.getIncrFileName(1, System.Reflection.MethodInfo.GetCurrentMethod().Name)); path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "SQL exceeds filesize limit 5 result sets.sql"; StreamReader sr = new StreamReader(path); p.Query = sr.ReadToEnd(); sr.Close(); p.FromFile = false; SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder { DataSource = ConfigurationManager.AppSettings["datasource"], InitialCatalog = ConfigurationManager.AppSettings["database"], IntegratedSecurity = false, UserID = ConfigurationManager.AppSettings["username"], Password = ConfigurationManager.AppSettings["password"] }; p.ConnectionString = sb.ConnectionString; //p.columnTypeMappings = columnTypeMappings; //p.resultNames = resultNames; //p.de = ExcelDataType.General; p.QueryTimeout = 0; p.WriteEmptyResultSetColumns = false; //p.numberFormatCulture = c1; // should break into 3 workbooks, 961 rows total, 2 result sets per workbook except for last WB // 256, 256, 90, 103, 256 p.MaxRowsPerSheet = 150000; //p.MaxWorkBookSize = 1000000; p.DupeKeysToDelayStartingNewWorksheet = new string[] { "a1" }; p.MaximumResultSetsPerWorkbook = 2; Workbook target = new Workbook(p); Dictionary <string, FileStream> fs1 = new Dictionary <string, FileStream>(); //string tempOutputPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); int i = 1; // run query and save results if (target.RunQuery()) { string pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fs = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fs); WorkBookStatus status = target.WriteQueryResults(fs); while (status != WorkBookStatus.Completed) { i = i + 1; pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fsa = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fsa); status = target.WriteQueryResults(fsa); fsa.Close(); } fs.Close(); } // make sure 3 files have been created Assert.AreEqual(fs1.Count, 3); int currentStream = 1; int worksheet = 1; foreach (var fs in fs1) { fs.Value.Close(); //fs.Value.Flush(); //fs.Value.Seek(0, SeekOrigin.Begin); for (int ii = 1; ii < 3; ii++) { if (worksheet < 6) { // open the xlsx ZipFile z = new ZipFile(fs.Key); // pick the first worksheet var t = z.SelectEntries("xl/worksheets/sheet" + worksheet.ToString() + "_1.xml").First(); // read results MemoryStream ms = new MemoryStream(); t.Extract(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); StreamReader sr1 = new StreamReader(ms, Encoding.UTF8); string b1 = sr1.ReadToEnd(); XDocument x = XDocument.Parse(b1, LoadOptions.None); ms.Close(); var countOfRows = // Xml is e.g. <worksheet><sheetData><row>...</row><row><c s="1">12345.2423</c>... x.Elements().First(aa => aa.Name.LocalName == "worksheet") .Elements().First(aa => aa.Name.LocalName == "sheetData").Elements() .Count(aaa => aaa.Name.LocalName == "row"); switch (currentStream) { case 1: if (worksheet == 1 || worksheet == 2) { Assert.AreEqual(countOfRows, 257); } break; case 2: if (worksheet == 3) { Assert.AreEqual(countOfRows, 91); } else if (worksheet == 4) { Assert.AreEqual(countOfRows, 104); } break; case 3: Assert.AreEqual(countOfRows, 257); break; } worksheet++; } } currentStream++; } }
public void WorkbookProcessBatchesTest() { WorkBookParams p = new WorkBookParams(); string[] tt = new string[2]; string path = Assembly.GetExecutingAssembly().Location; path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); string tempOutputPath = Path.Combine(path, "ExcelXmlWriterNTest", "bin", "Debug"); //tempOutputPath = Path.Combine(path, Utility.getIncrFileName(1, System.Reflection.MethodInfo.GetCurrentMethod().Name)); path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "SQL exceeds filesize limit 5 result sets.sql"; tt[0] = path; path = Assembly.GetExecutingAssembly().Location; path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); tempOutputPath = Path.Combine(path, "ExcelXmlWriterNTest", "bin", "Debug"); //tempOutputPath = Path.Combine(path, Utility.getIncrFileName(1, System.Reflection.MethodInfo.GetCurrentMethod().Name)); path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "SQL exceeds filesize limit 5 result sets.sql"; tt[1] = path; Dictionary <string, FileStream> fs1 = new Dictionary <string, FileStream>(); int i = 1; // we re-create the code rather than make the original static, or have to instantiate a FormEntrance foreach (var qry in tt) { using (StreamReader sr = new StreamReader(new FileStream(qry, FileMode.Open, FileAccess.Read))) { p.Query = sr.ReadToEnd(); p.FromFile = false; SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder { DataSource = ConfigurationManager.AppSettings["datasource"], InitialCatalog = ConfigurationManager.AppSettings["database"], IntegratedSecurity = false, UserID = ConfigurationManager.AppSettings["username"], Password = ConfigurationManager.AppSettings["password"] }; p.ConnectionString = sb.ConnectionString; p.MaxRowsPerSheet = 150000; //p.MaxWorkBookSize = 1000000; p.DupeKeysToDelayStartingNewWorksheet = new string[] { "a1" }; p.MaximumResultSetsPerWorkbook = 2; Workbook target = new Workbook(p); if (target.RunQuery()) { i = i + 1; string pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, System.Reflection.MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fs = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fs); WorkBookStatus status = target.WriteQueryResults(fs); while (status != WorkBookStatus.Completed) { i = i + 1; pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, System.Reflection.MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fsa = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fsa); status = target.WriteQueryResults(fsa); fsa.Close(); } fs.Close(); } } } Assert.AreEqual(fs1.Count, 6); int currentStream = 1; int worksheet = 1; int resetVariable = 1; foreach (var fs in fs1) { // the first 3 entries in fs1 are from the same query, the next 3 are from the next queyr if (resetVariable == 4) { currentStream = 1; worksheet = 1; } resetVariable++; fs.Value.Close(); //fs.Value.Flush(); //fs.Value.Seek(0, SeekOrigin.Begin); for (int ii = 1; ii < 3; ii++) { if (worksheet < 6) { ZipFile z = new ZipFile(fs.Key); var t = z.SelectEntries("xl/worksheets/sheet" + worksheet.ToString() + "_1.xml").First(); MemoryStream ms = new MemoryStream(); t.Extract(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); //PackagePart strm = pa.GetPart(new Uri("/xl/worksheets/sheet1_1.xml", UriKind.Relative)); //Stream m = t.InputStream; StreamReader sr1 = new StreamReader(ms, Encoding.UTF8); string b1 = sr1.ReadToEnd(); XDocument x = XDocument.Parse(b1, LoadOptions.None); ms.Close(); var asdza = // Xml is e.g. <worksheet><sheetData><row>...</row><row><c s="1">12345.2423</c>... x.Elements().First(aa => aa.Name.LocalName == "worksheet") .Elements().First(aa => aa.Name.LocalName == "sheetData").Elements() .Where(aaa => aaa.Name.LocalName == "row").Count(); if (currentStream == 1) { if (worksheet == 1 || worksheet == 2) { Assert.AreEqual(asdza, 257); } } else if (currentStream == 2) { if (worksheet == 3) { Assert.AreEqual(asdza, 91); } else if (worksheet == 4) { Assert.AreEqual(asdza, 104); } } else if (currentStream == 3) { Assert.AreEqual(asdza, 257); } worksheet++; } } currentStream++; } }
public void WorkbookQueryWriteResultsOverSizeTest() { WorkBookParams p = new WorkBookParams(); string path = Assembly.GetExecutingAssembly().Location; path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); path = Path.GetDirectoryName(path); string tempOutputPath = Path.Combine(path, "ExcelXmlWriterNTest", "bin", "Debug"); //tempOutputPath = Path.Combine(path, Utility.getIncrFileName(1, System.Reflection.MethodInfo.GetCurrentMethod().Name)); path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "SQL exceeds filesize limit.sql"; StreamReader sr = new StreamReader(path); p.Query = sr.ReadToEnd(); sr.Close(); p.FromFile = false; SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder { DataSource = ConfigurationManager.AppSettings["datasource"], InitialCatalog = ConfigurationManager.AppSettings["database"], IntegratedSecurity = false, UserID = ConfigurationManager.AppSettings["username"], Password = ConfigurationManager.AppSettings["password"] }; p.ConnectionString = sb.ConnectionString; //p.columnTypeMappings = columnTypeMappings; //p.resultNames = resultNames; //p.de = ExcelDataType.General; p.QueryTimeout = 0; p.WriteEmptyResultSetColumns = false; //p.numberFormatCulture = c1; // breaks into 6 workbooks // workbook 1 & 2: 4097 rows, all in 1 sheet // workbook 3: 8193 rows, all in 1 sheet // workbook 4: 16385 rows, all in 1 sheet // workbook 5: 32769 rows, all in 1 sheet // workbook 6: 65537 rows, all in 1 sheet p.MaxRowsPerSheet = 38641; p.MaxWorkBookSize = 1000000; p.DupeKeysToDelayStartingNewWorksheet = new string[] { "a1" }; Workbook target = new Workbook(p); Dictionary <string, FileStream> fs1 = new Dictionary <string, FileStream>(); //string tempOutputPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); int i = 1; if (target.RunQuery()) { string pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fs = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fs); WorkBookStatus status = target.WriteQueryResults(fs); while (status != WorkBookStatus.Completed) { i = i + 1; pht = Path.Combine(tempOutputPath , Utility.getIncrFileName(i, MethodBase.GetCurrentMethod().Name) + ".xlsx"); FileStream fsa = new FileStream(pht , FileMode.Create, FileAccess.ReadWrite, FileShare.None); fs1.Add(pht, fsa); status = target.WriteQueryResults(fsa); } } int currentStream = 1; foreach (var fs in fs1) { fs.Value.Close(); //fs.Value.Flush(); //fs.Value.Seek(0, SeekOrigin.Begin); ZipFile z = new ZipFile(fs.Key); var t = z.SelectEntries("xl/worksheets/sheet1_1.xml").First(); MemoryStream ms = new MemoryStream(); t.Extract(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); StreamReader sr1 = new StreamReader(ms, Encoding.UTF8); string b1 = sr1.ReadToEnd(); XDocument x = XDocument.Parse(b1, LoadOptions.None); ms.Close(); var countRows = // Xml is e.g. <worksheet><sheetData><row>...</row><row><c s="1">12345.2423</c>... x.Elements().First(aa => aa.Name.LocalName == "worksheet") .Elements().First(aa => aa.Name.LocalName == "sheetData").Elements() .Count(aaa => aaa.Name.LocalName == "row"); switch (currentStream) { case 1: Assert.AreEqual(countRows, 4097); break; case 2: Assert.AreEqual(countRows, 4097); break; case 3: Assert.AreEqual(countRows, 8193); break; case 4: Assert.AreEqual(countRows, 16385); break; case 5: Assert.AreEqual(countRows, 32769); break; case 6: Assert.AreEqual(countRows, 65537); break; } //var asdz = //// Xml is e.g. <worksheet><sheetData><row>...</row><row><c s="1">12345.2423</c>... //// get the first worksheet (well, there is only one) //x.Elements().First(aa => aa.Name.LocalName == "worksheet") //// get the first "sheetdata" element, again there is only one, and get all its elements //.Elements().First(aa => aa.Name.LocalName == "sheetData").Elements() //// get first row //.First(aaa => aaa.Name.LocalName == "row" //// get all cells (denoted as "c" in Excel-speak) //&& aaa.Elements().Where(bbb => bbb.Name.LocalName == "c") // // get those where attribute is set to "s=1" (means date) // .Any(xx => xx.Attributes("s").Any() && Convert.ToInt32(xx.Attribute("s").Value) == 1)) //.Elements() // .First(ccc => ccc.Name.LocalName == "c" //&& ccc.Attributes("s").Any() && Convert.ToInt32(ccc.Attribute("s").Value) == 1).Value; // ensure correct xl date value //Assert.AreEqual(Convert.ToDouble(asdz) //, 40155.7633988426); //var asdz2 = x.Elements().First(aa => aa.Name.LocalName == "worksheet") //.Elements().First(aa => aa.Name.LocalName == "sheetData").Elements() //.Last(aaa => aaa.Name.LocalName == "row") //.Elements().Where(aaa => aaa.Name.LocalName == "c") //.First(xx => xx.Attributes("s").Any() && Convert.ToInt32(xx.Attribute("s").Value) == 1); //sr = new StreamReader(fs, Encoding.UTF8); //XDocument x = XDocument.Parse(sr.ReadToEnd(), LoadOptions.PreserveWhitespace); //fs.Seek(0, SeekOrigin.Begin); //string[] a = sr.ReadToEnd().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries); //fs.Close(); //sr.Close(); //XElement x2 = x.Elements("{urn:schemas-microsoft-com:office:spreadsheet}Workbook") // .Elements("{urn:schemas-microsoft-com:office:spreadsheet}Worksheet") // .Where(x1 => x1 // .Attribute("{urn:schemas-microsoft-com:office:spreadsheet}Name").Value == "Sheet1_1") // .Elements("{urn:schemas-microsoft-com:office:spreadsheet}Table") // .Elements("{urn:schemas-microsoft-com:office:spreadsheet}Row").Last(); //// first element //XElement x3 = x2 // .Elements("{urn:schemas-microsoft-com:office:spreadsheet}Cell") // .Elements("{urn:schemas-microsoft-com:office:spreadsheet}Data").First(); //int len = a.Length; ////int aaa = currentStream; ////int dfjkla = Convert.ToInt32(x3.Value); //if (currentStream == 1) //{ // Assert.AreEqual(len, 14968); // Assert.AreEqual(Convert.ToInt32(x3.Value), 1063); //} //else if (currentStream == 5) //{ // Assert.AreEqual(len, 14940); // Assert.AreEqual(Convert.ToInt32(x3.Value), 5307); //} //else if (currentStream == 22) //{ // Assert.AreEqual(len, 14926); // Assert.AreEqual(Convert.ToInt32(x3.Value), 23331); //} //else if (currentStream == 31) //{ // Assert.AreEqual(len, 13484); // Assert.AreEqual(Convert.ToInt32(x3.Value), 32768); //} //else if (currentStream > 31) // Assert.Fail("Too many streams initiated"); currentStream++; } }