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++;
}
}