public ExcelWriter(string filename)
{
template = new OoXml(filename); // Open the template
sheet = template.sheets.Values.First<gSheet>(); // Get the first sheet
GetMapping(); // Get the mapping
sheet.SetSource(OnDataRow, fixedRows); // Set data call back, keep first row of the template
}
void OnDataRow(gSheet sheet)
{
if (!data.Read()) return; // If no data available then exit
sheet.BeginRow(curRow); // This is the row we are going to write
foreach(KeyValuePair<int,string> m in mapping) // For each mapping value
sheet.WriteCell(m.Key, data[m.Value]); // Save it into the Excel
sheet.EndRow(); // Data has been exported
curRow++; // Next record will be placed in this excel row
exportedRecords++; // Increase number of exported records
}
public OoXmlDataStream(gSheet sheet, int Keep,OnNewRow dr)
: base()
{
string separator = Guid.NewGuid().ToString();
sheet.LoadInMemory(); // Make sure that the sheet is loaded in memory
XmlDocument d = sheet.Stream.ReadAsXml(); // Load as XML
XmlNamespaceManager nsmgr = new XmlNamespaceManager(d.NameTable); // Set the name space
nsmgr.AddNamespace("aa", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // To Open XML
XmlNode ws = d.SelectSingleNode("//aa:worksheet/aa:sheetData", nsmgr); // Locate data NODE
if (ws == null) throw new Exception("Could not find sheet data"); // Error if not
XmlNode r = ws.FirstChild; while (Keep > 0 && r != null) { r = r.NextSibling; Keep--; } // Skip rows to keep
if (r != null) InitializeColumns(r); // if present use first row NOT to keep to initialize columns
ws.AppendChild(d.CreateNode(XmlNodeType.Comment, "SEPARATOR", null)).InnerText = separator; // Add separator mark up
InitializeHeaderAndFooter(d.OuterXml, separator); // Get header and footer using separator
rowopen1= ASCIIEncoding.ASCII.GetBytes("<row r=\""); // <row r="
sstpart = ASCIIEncoding.ASCII.GetBytes("\" t=\"s"); // " t="s
rowopen2 = ASCIIEncoding.ASCII.GetBytes("\">\n"); // ">
rowclose = ASCIIEncoding.ASCII.GetBytes("</row>\n"); // </row>
srsheet = sheet; // Sheet this stream is linked to
onrow = dr; // On data callback
}
public OoXmlDataStream(gSheet sheet, int Keep, OnNewRow dr)
: base()
{
string separator = Guid.NewGuid().ToString();
sheet.LoadInMemory(); // Make sure that the sheet is loaded in memory
XmlDocument d = sheet.Stream.ReadAsXml(); // Load as XML
XmlNamespaceManager nsmgr = new XmlNamespaceManager(d.NameTable); // Set the name space
nsmgr.AddNamespace("aa", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // To Open XML
XmlNode ws = d.SelectSingleNode("//aa:worksheet/aa:sheetData", nsmgr); // Locate data NODE
if (ws == null)
{
throw new Exception("Could not find sheet data"); // Error if not
}
XmlNode r = ws.FirstChild; while (Keep > 0 && r != null)
{
r = r.NextSibling; Keep--;
} // Skip rows to keep
if (r != null)
{
InitializeColumns(r); // if present use first row NOT to keep to initialize columns
}
ws.AppendChild(d.CreateNode(XmlNodeType.Comment, "SEPARATOR", null)).InnerText = separator; // Add separator mark up
InitializeHeaderAndFooter(d.OuterXml, separator); // Get header and footer using separator
rowopen1 = ASCIIEncoding.ASCII.GetBytes("<row r=\""); // <row r="
sstpart = ASCIIEncoding.ASCII.GetBytes("\" t=\"s"); // " t="s
rowopen2 = ASCIIEncoding.ASCII.GetBytes("\">\n"); // ">
rowclose = ASCIIEncoding.ASCII.GetBytes("</row>\n"); // </row>
srsheet = sheet; // Sheet this stream is linked to
onrow = dr; // On data callback
}
public ExcelReader(string filename)
{
source = new OoXml(filename); // Open the excel file
sheet = source.sheets.Values.First<gSheet>(); // Get the first sheet
}