private void treeView1_AfterSelect(object sender, TreeViewEventArgs e) { Control p = new Control(); switch (e.Node.Name) { case "Node1": p = tabnamepanel; break; case "Node5": p = sqloptionspanel; break; case "Node3": case "Node0": default: p = excelformatpanel; break; } p.Parent = tabControl1.Parent; p.Location = tabControl1.Location; p.Visible = true; foreach (Control a in new Control[] { excelformatpanel, tabnamepanel, sqloptionspanel }) { if (a != p) { a.Visible = false; } } }
/// <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); } } }
private void queryOptionsToolStripMenuItem_Click(object sender, EventArgs e) { FormOptions q = new FormOptions(); if (q.ShowDialog() == DialogResult.OK) { p = q.ExcelXmlQueryResultsParams; } else if (q.c1 != null) { if (q.c1.Data.Contains("key")) { toolStripStatusLabel2.Text = Resources.AppconfigBroken + " Missing key: " + (string)q.c1.Data["key"]; } else { toolStripStatusLabel2.Text = Resources.AppconfigBroken; } LockUnlockGUIControls(true); } }
internal FormOptions() { InitializeComponent(); tabControl1.Visible = false; foreach (TreeNode n in treeView1.Nodes) { n.Expand(); } // populate login method opts comboBox1.Items.Add(Resources.SSPIConnection); comboBox1.Items.Add(Resources.UsernamePasswordConnection); foreach (DataGridViewColumn c1 in resultSetNamesGrid.Columns) { c1.HeaderCell.ToolTipText = Resources.TooltipResultNames; } toolStripStatusLabel1.Text = string.Empty; toolTip1.SetToolTip(label9, Resources.MaxSize); toolTip1.SetToolTip(textBox7, Resources.MaxSize); toolTip2.SetToolTip(label10, Resources.DupeKeyColumnsToolTip); toolTip2.AutoPopDelay = Settings1.Default.toolTipDelayBeforeFade; toolTip2.SetToolTip(textBox8, Resources.DupeKeyColumnsToolTip); ConfigManipulator c = new ConfigManipulator(); try { p = LoadOpts(); textBox1.Text = c.GetValue("Server"); textBox2.Text = c.GetValue("Database"); textBox3.Text = c.GetValue("ConnectionUsername"); textBox4.Text = c.GetValue("ConnectionPassword"); checkBox1.Checked = p.WriteEmptyResultSetColumns; checkBox2.Checked = p.AutoRewriteOverpunch; textBox5.Text = p.QueryTimeout.ToString(); textBox6.Text = p.MaxRowsPerSheet.ToString(); textBox9.Text = p.MaximumResultSetsPerWorkbook.ToString(); var p1 = p.ResultNames; int count = 0; foreach (object o in p1.Keys) { DataGridViewRow r = new DataGridViewRow(); resultSetNamesGrid.Rows.Add(); resultSetNamesGrid.Rows[count].Cells[0].Value = o; resultSetNamesGrid.Rows[count].Cells[1].Value = p1[(int)o]; count++; } if (String.Equals(c.GetValue("ExcelFileType"), Resources.FileTypeXml)) { comboBox3.SelectedIndex = 1; } else { comboBox3.SelectedIndex = 0; } if (String.Equals(c.GetValue("ConnectionMethod"), Resources.SSPIConnection)) { comboBox1.SelectedItem = Resources.SSPIConnection; } else { comboBox1.SelectedItem = Resources.UsernamePasswordConnection; } if (String.Equals(c.GetValue("NewResultSet"), Resources.NewResultSetWorksheet)) { radioButton1.Checked = true; radioButton2.Checked = false; } else { radioButton1.Checked = false; radioButton2.Checked = true; } textBox7.Text = Math.Round((double)p.MaxWorkBookSize / 1024 / 1024 / 1024, 3, MidpointRounding.AwayFromZero).ToString(); if (p.DupeKeysToDelayStartingNewWorksheet != null && p.DupeKeysToDelayStartingNewWorksheet.Length > 0) { textBox8.Text = string.Join(",", p.DupeKeysToDelayStartingNewWorksheet); } } catch (Exceptions.ConfigFileBroken e) { MessageBox.Show(e.Message); if (e.Data.Contains("key")) { toolStripStatusLabel1.Text = Resources.AppconfigBroken + " Missing key: " + (string)e.Data["key"]; } else { toolStripStatusLabel1.Text = Resources.AppconfigBroken; } panel7.Enabled = false; c1 = e; } }
/// <summary> /// Save values to form state and app.config. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { double dd; if (!double.TryParse(textBox7.Text, out dd)) { MessageBox.Show("Error: Maximum workbook filesize must be a numeric entry."); return; } dd = Math.Round(Convert.ToDouble(textBox7.Text) * Math.Pow(1024, 3), 0, MidpointRounding.AwayFromZero); ConfigManipulator c = new ConfigManipulator(); Dictionary <string, string> h = new Dictionary <string, string>(); h.Add("Server", textBox1.Text); h.Add("Database", textBox2.Text); h.Add("ConnectionMethod", String.Equals(comboBox1.SelectedItem.ToString(), Resources.SSPIConnection) ? Resources.SSPIConnection : Resources.UsernamePasswordConnection); h.Add("NewResultSet", radioButton1.Checked ? Resources.NewResultSetWorksheet : Resources.NewResultSetWorkbook); h.Add("ConnectionUsername", textBox3.Text); h.Add("ConnectionPassword", textBox4.Text); h.Add("QueryTimeout", textBox5.Text); h.Add("MaxRowsPerSheet", textBox6.Text); h.Add("MaximumWorkbookSizeInBytes", dd.ToString()); h.Add("MaximumResultSetsPerWorkbook", textBox9.Text); if (Regex.IsMatch(comboBox3.SelectedItem.ToString(), Resources.FileTypeXml, RegexOptions.IgnoreCase)) { h.Add("ExcelFileType", Resources.FileTypeXml); } else { h.Add("ExcelFileType", Resources.FileTypeXlsx); } h.Add("WriteEmptyResultColumnHeaders", checkBox1.Checked.ToString()); h.Add("AutoRewriteOverpunch", checkBox2.Checked.ToString()); string[] a = null; if (!string.IsNullOrEmpty(textBox8.Text)) { a = textBox8.Text.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries); Dictionary <object, object> d1a = new Dictionary <object, object>(); int i = 1; foreach (var entry in a) { if (!string.IsNullOrWhiteSpace(entry)) { d1a.Add("column" + i.ToString(), entry); i++; } } if (d1a.Count > 0) { c.SaveValue(d1a, "ColumnsThatPreventNewWorksheets"); } } Dictionary <object, object> d2 = new Dictionary <object, object>(); foreach (DataGridViewRow d in resultSetNamesGrid.Rows.Cast <DataGridViewRow>().Where(x => x.Cells[0].Value != null)) { d2.Add(Convert.ToInt32(d.Cells[0].Value.ToString()), d.Cells[1].Value.ToString()); } c.SaveValue(d2, "ResultNames"); foreach (string i in h.Keys) { c.SaveValue(i, h[i]); } c.WriteConfig(); p = LoadOpts(); this.DialogResult = DialogResult.OK; }
internal static WorkBookParams LoadOpts() { ConfigManipulator c = new ConfigManipulator(); WorkBookParams a = new WorkBookParams(); a.WriteEmptyResultSetColumns = Convert.ToBoolean(c.GetValue("WriteEmptyResultColumnHeaders")); a.AutoRewriteOverpunch = Convert.ToBoolean(c.GetValue("AutoRewriteOverpunch")); a.BackendMethod = Enum.GetValues(typeof(ExcelBackend)) .Cast <ExcelBackend>() .Where(x => String.Equals(x.ToString(), c.GetValue("ExcelFileType"))).First(); int res = 0; if (!Int32.TryParse(c.GetValue("MaxRowsPerSheet"), out res)) { a.MaxRowsPerSheet = Convert.ToInt32(Resources.DefaultMaxRowsPerSheet); } else { a.MaxRowsPerSheet = Convert.ToInt32(c.GetValue("MaxRowsPerSheet")); } if (Int32.TryParse(c.GetValue("QueryTimeout"), out res)) { a.QueryTimeout = Convert.ToInt32(c.GetValue("QueryTimeout")); } var p1 = c.GetDictionary("ResultNames", typeof(int), typeof(string)); foreach (object o in p1.Keys) { a.ResultNames.Add(Convert.ToInt32(o) , p1[o].ToString()); } var p2 = c.GetDictionary("ColumnsThatPreventNewWorksheets", typeof(string), typeof(string)); string[] aa = null; if (p2.Values.Count > 0) { aa = new string[p2.Values.Count]; a.DupeKeysToDelayStartingNewWorksheet = new string[aa.Length]; for (int i = 0; i < aa.Length; i++) { a.DupeKeysToDelayStartingNewWorksheet[i] = p2.Values.ElementAt(i).ToString(); } } long res2 = 0; if (long.TryParse(c.GetValue("MaximumWorkbookSizeInBytes"), out res2)) { a.MaxWorkBookSize = res2; } int res3 = 0; if (Int32.TryParse(c.GetValue("MaximumResultSetsPerWorkbook"), out res3)) { a.MaximumResultSetsPerWorkbook = res3; } return(a); }
public FormEntrance() { InitializeComponent(); saveFileDialog1.Filter = Resources.SaveDialogFilter; this.Text = Resources.Version; toolStripStatusLabel2.Text = Resources.Waiting; toolTip1.SetToolTip(label3, Resources.TooltipInitialFilename); toolTip1.AutoPopDelay = Settings1.Default.toolTipDelayBeforeFade; toolTip1.SetToolTip(textBox1, Resources.TooltipInitialFilename); ConfigManipulator c = new ConfigManipulator(); try { p = FormOptions.LoadOpts(); } catch (Exception e) { if (e is Exceptions.ConfigFileBroken || e is ArgumentNullException || e is ArgumentException || e is InvalidOperationException) { MessageBox.Show(e.Message); if (e is Exceptions.ConfigFileBroken) { if (e.Data.Contains("key")) { toolStripStatusLabel2.Text = Resources.AppconfigBroken + " Missing key: " + (string)e.Data["key"]; } else { toolStripStatusLabel2.Text = Resources.AppconfigBroken; } } LockUnlockGUIControls(true); } else { MessageBox.Show(e.Message); throw; } } string debugFilePath = Application.ExecutablePath; debugFilePath = Path.GetDirectoryName(debugFilePath); userFileName.Text = debugFilePath + Path.DirectorySeparatorChar.ToString(); textBox1.Text = "a.xlsx"; fileName = userFileName.Text + textBox1.Text; #if DEBUG string path = Assembly.GetExecutingAssembly().Location; if (!string.IsNullOrEmpty(path)) { path = Path.GetDirectoryName(path); } if (!string.IsNullOrEmpty(path)) { path = Path.GetDirectoryName(path); } if (!string.IsNullOrEmpty(path)) { path = Path.GetDirectoryName(path); } if (!string.IsNullOrEmpty(path)) { path = Path.GetDirectoryName(path); } if (!string.IsNullOrEmpty(path)) { path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "Sql exceeds filesize limit.sql"; } if (File.Exists(path)) { FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read); StreamReader sr = new StreamReader(fs); richTextBox1.Text = sr.ReadToEnd(); sr.Close(); fs.Close(); } #endif }
public void IncompleteXlsxFromFileSeparateTabsTest() { 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); path = path + Path.DirectorySeparatorChar.ToString() + "ExcelXmlWriterNTest" + Path.DirectorySeparatorChar.ToString() + "Resources" + Path.DirectorySeparatorChar.ToString() + "Data.xml"; p.Query = path; p.FromFile = true; //p.connStr = connStr; //p.columnTypeMappings = columnTypeMappings; p.MaxRowsPerSheet = 100000; p.ResultNames = new Dictionary <int, string> { { 1, "blah blah" }, { 2, "x" } }; p.QueryTimeout = 0; //p.numberFormatCulture = c1; Workbook target = new Workbook(p); string path1 = Path.GetTempFileName(); if (target.RunQuery()) { target.WriteQueryResults(path1); } //string path2 = Environment.CurrentDirectory; //path2 = Path.GetDirectoryName(path2); //path2 = Path.GetDirectoryName(path2); //path2 = Path.GetDirectoryName(path2); //path2 = path2 + Path.DirectorySeparatorChar.ToString() // + "ExcelXmlWriterTest_vs2008" + Path.DirectorySeparatorChar.ToString() // + "test.xlsx"; //File.Copy(path1, path2, true); //Package pa = Package.Open(path2); ZipFile z = new ZipFile(path1); // broken here, looks like due to reading from file is broken in queryreader var t = z.SelectEntries("xl/worksheets/sheet2_1.xml").First(); MemoryStream ms = new MemoryStream(); t.Extract(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); //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(); //PackagePart strm = pa.GetPart(new Uri("/xl/worksheets/sheet1_1.xml", UriKind.Relative)); //Stream m = t.InputStream; StreamReader sr = new StreamReader(ms, Encoding.UTF8); string b = sr.ReadToEnd(); XDocument x = XDocument.Parse(b, LoadOptions.None); ms.Close(); var asdz = // 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() .First(aaa => aaa.Name.LocalName == "row" && aaa.Elements().Where(bbb => bbb.Name.LocalName == "c") .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").Where(xx => xx.Attributes("s").Any() && Convert.ToInt32(xx.Attribute("s").Value) == 1).First(); // ensure correct xl date value Assert.AreEqual(Convert.ToDouble(asdz2.Elements().First(aaa => aaa.Name.LocalName == "v").Value) , 40165.7634929051); // ensure correct cell counts in 1st row // ensure correct cell counts in 2nd (and last row) // ensure correct shared cell refernce in last row }
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++; } }