//Exporting data to word public void ExportToWord() { //A new document is created. WordDocument document = new WordDocument(); //Adding new table to the document WTable doctable = new WTable(document); //Adding a new section to the document. WSection section = document.AddSection() as WSection; //Set Margin of the section section.PageSetup.Margins.All = 72; //Set page size of the section section.PageSetup.PageSize = new SizeF(800, 792); //Create Paragraph styles WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle; style.CharacterFormat.FontName = "Calibri"; style.CharacterFormat.FontSize = 11f; WCharacterFormat charFormat = new WCharacterFormat(document); charFormat.TextColor = System.Drawing.Color.White; charFormat.Bold = true; doctable.AddRow(true, false); WTableCell cell = new WTableCell(document); cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat); cell.Width = 90; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat); cell.Width = 90; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat); cell.Width = 90; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat); cell.Width = 180; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat); cell.Width = 90; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat); cell.Width = 90; doctable.Rows[0].Cells.Add(cell); //Reading each row from the fetched result for (int i = 0; i < result.Count(); i++) { HiveRecord records = result[i]; doctable.AddRow(true, false); //Reading each field from the row for (int j = 0; j < records.Count; j++) { Object fields = records[j]; //Adding new cell to the document cell = new WTableCell(document); //Adding each field to the cell cell.AddParagraph().AppendText(fields.ToString()); if (j == 3) { cell.Width = 180; } else { cell.Width = 90; } //Adding cell to the table doctable.Rows[i + 1].Cells.Add(cell); doctable.Rows[0].Cells[j].CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51); } } //Adding table to the section section.Tables.Add(doctable); //Save as word 2003 format if (rdbWord2003.IsChecked.Value) { //Saving the document to disk. document.Save("Sample.doc"); //Message box confirmation to view the created document. if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //Launching the MS Word file using the default Application.[MS Word Or Free WordViewer] System.Diagnostics.Process.Start("Sample.doc"); //Exit this.Close(); } } //Save as word 2007 format else if (rdbWord2007.IsChecked.Value) { //Saving the document as .docx document.Save("Sample.docx", FormatType.Word2007); //Message box confirmation to view the created document. if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { try { //Launching the MS Word file using the default Application.[MS Word Or Free WordViewer] System.Diagnostics.Process.Start("Sample.docx"); //Exit this.Close(); } catch (Win32Exception ex) { MessageBox.Show("Word 2007 is not installed in this system"); Console.WriteLine(ex.ToString()); } } } //Save as word 2010 format else if (rdbWord2010.IsChecked.Value) { //Saving the document as .docx document.Save("Sample.docx", FormatType.Word2010); //Message box confirmation to view the created document. if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { try { //Launching the MS Word file using the default Application.[MS Word Or Free WordViewer] System.Diagnostics.Process.Start("Sample.docx"); //Exit this.Close(); } catch (Win32Exception ex) { MessageBox.Show("Word 2010 is not installed in this system"); Console.WriteLine(ex.ToString()); } } } //Save as word 2013 format else if (rdbWord2013.IsChecked.Value) { //Saving the document as .docx document.Save("Sample.docx", FormatType.Word2013); //Message box confirmation to view the created document. if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { try { //Launching the MS Word file using the default Application.[MS Word Or Free WordViewer] System.Diagnostics.Process.Start("Sample.docx"); //Exit this.Close(); } catch (Win32Exception ex) { MessageBox.Show("Word 2013 is not installed in this system"); Console.WriteLine(ex.ToString()); } } } else { // Exit this.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { ErrorMessage.InnerText = ""; if (hdnGroup.Value == "Word") { CheckFileStatus FileStatus = new CheckFileStatus(); string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None)); ErrorMessage.InnerText = FileStatus.CheckFile(path); if (ErrorMessage.InnerText == "") { try { //Create a new document WordDocument document = new WordDocument(); //Adding new table to the document WTable doctable = new WTable(document); //Adding a new section to the document. WSection section = document.AddSection() as WSection; //Set Margin of the section section.PageSetup.Margins.All = 72; //Set page size of the section section.PageSetup.PageSize = new SizeF(800, 792); //Create Paragraph styles WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle; style.CharacterFormat.FontName = "Calibri"; style.CharacterFormat.FontSize = 11f; //Create a character format for declaring font color and style for the text inside the cell WCharacterFormat charFormat = new WCharacterFormat(document); charFormat.TextColor = System.Drawing.Color.White; charFormat.Bold = true; //Initializing the hive server connection HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2); //To initialize a Hive server connection with secured cluster //HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>"); //To initialize a Hive server connection with Azure cluster //HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>"); con.Open(); //Create table for adventure person contacts HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con); createCommand.ExecuteNonQuery(); HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con); //Executing the query HqlDataReader reader = command.ExecuteReader(); reader.FetchSize = 100; //Fetches the result from the reader and store it in HiveResultSet HiveResultSet result = reader.FetchResult(); //Adding headertext for the table doctable.AddRow(true, false); //Creating new cell WTableCell cell = new WTableCell(document); cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat); cell.Width = 75; //Adding cell to the row doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat); cell.Width = 75; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat); cell.Width = 75; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat); cell.Width = 75; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat); cell.Width = 75; doctable.Rows[0].Cells.Add(cell); cell = new WTableCell(document); cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat); cell.Width = 75; doctable.Rows[0].Cells.Add(cell); //Reading each row from the fetched result for (int i = 0; i < result.Count(); i++) { HiveRecord records = result[i]; doctable.AddRow(true, false); //Reading each data from the row for (int j = 0; j < records.Count; j++) { Object fields = records[j]; //Adding new cell to the document cell = new WTableCell(document); //Adding each data to the cell cell.AddParagraph().AppendText(fields.ToString()); cell.Width = 75; //Adding cell to the table doctable.Rows[i + 1].Cells.Add(cell); doctable.Rows[0].Cells[j].CellFormat.BackColor = Color.FromArgb(51, 153, 51); } } //Adding table to the section section.Tables.Add(doctable); //Save as word 2007 format if (rBtnWord2003.Checked == true) { document.Save("Sample.doc", FormatType.Doc, Response, HttpContentDisposition.Attachment); } else if (rBtnWord2007.Checked == true) { document.Save("Sample.docx", FormatType.Word2007, Response, HttpContentDisposition.Attachment); } //Save as word 2010 format else if (rbtnWord2010.Checked == true) { document.Save("Sample.docx", FormatType.Word2010, Response, HttpContentDisposition.Attachment); } //Save as word 2013 format else if (rbtnWord2013.Checked == true) { document.Save("Sample.docx", FormatType.Word2013, Response, HttpContentDisposition.Attachment); } //Closing the hive connection con.Close(); } catch (HqlConnectionException) { ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard."; } } } else if (hdnGroup.Value == "Excel") { ErrorMessage.InnerText = ""; CheckFileStatus FileStatus = new CheckFileStatus(); string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None)); ErrorMessage.InnerText = FileStatus.CheckFile(path); if (ErrorMessage.InnerText == "") { try { //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Instantiate the excel application object. IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; //Adding header text for worksheet worksheet[1, 1].Text = "ContactID"; worksheet[1, 2].Text = "FullName"; worksheet[1, 3].Text = "Age"; worksheet[1, 4].Text = "EmailAddress"; worksheet[1, 5].Text = "PhoneNo"; worksheet[1, 6].Text = "ModifiedDate"; //Initializing the hive server connection HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2); //To initialize a Hive server connection with secured cluster //HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>"); con.Open(); //Create table for adventure person contacts HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con); createCommand.ExecuteNonQuery(); //Passing the hive query HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con); //Executing the query HqlDataReader reader = command.ExecuteReader(); reader.FetchSize = 100; //Fetches the result from the reader and store it in a seperate set HiveResultSet result = reader.FetchResult(); //Reading each row from the fetched result for (int i = 0; i < result.Count(); i++) { HiveRecord records = result[i]; //Reading each field from the row for (int j = 0; j < records.Count; j++) { Object fields = records[j]; //Assigning each field value to the worksheet based on index worksheet[i + 2, j + 1].Text = fields.ToString(); } } worksheet.Range["A1:F1"].CellStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White; worksheet.Range["A1:F1"].CellStyle.Font.Bold = true; worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(51, 153, 51); worksheet.UsedRange.AutofitColumns(); //Saving workbook on user relevant name string fileName = "Sample.xlsx"; //conditions for selecting version //Save as Excel 97to2003 format if (rBtn2003.Checked == true) { workbook.Version = ExcelVersion.Excel97to2003; workbook.SaveAs("Sample.xls", Response, ExcelDownloadType.PromptDialog); } //Save as Excel 2007 formt else if (rBtn2007.Checked == true) { workbook.Version = ExcelVersion.Excel2007; workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog); } //Save as Excel 2010 format else if (rbtn2010.Checked == true) { workbook.Version = ExcelVersion.Excel2010; workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog); } //Save as Excel 2013 format else if (rbtn2013.Checked == true) { workbook.Version = ExcelVersion.Excel2013; workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog); } //closing the workwook workbook.Close(); //Closing the excel engine excelEngine.Dispose(); //Closing the hive connection con.Close(); } catch (HqlConnectionException) { ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard."; } } } }
//Exporting data to excel public void ExportToExcel() { //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Instantiate the excel application object. IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; worksheet[1, 1].Text = "Contact Id"; worksheet[1, 2].Text = "Full Name"; worksheet[1, 3].Text = "Age"; worksheet[1, 4].Text = "Email Id"; worksheet[1, 5].Text = "Phone Number"; worksheet[1, 6].Text = "Modified Date"; //Reading each row from the fetched result for (int i = 0; i < result.Count(); i++) { HiveRecord records = result[i]; //Reading each field from the row for (int j = 0; j < records.Count; j++) { Object fields = records[j]; //Assigning each field value to the worksheet based on index worksheet[i + 2, j + 1].Text = fields.ToString(); } } worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(51, 153, 51); worksheet.Range["A1:F1"].CellStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White; worksheet.Range["A1:F1"].CellStyle.Font.Bold = true; worksheet.UsedRange.AutofitColumns(); string fileName = ""; //Save as Excel 97 format if (rdbExcel97.IsChecked.Value) { fileName = "Sample.xls"; workbook.SaveAs(fileName); } // Save as Excel 2007 format else if (rdbExcel2007.IsChecked.Value) { fileName = "Sample.xlsx"; workbook.Version = ExcelVersion.Excel2007; workbook.SaveAs(fileName); } //Save as Excel 2010 format else if (rdbExcel2010.IsChecked.Value) { fileName = "Sample.xlsx"; workbook.Version = ExcelVersion.Excel2010; workbook.SaveAs(fileName); } //Save as Excel 2013 format else if (rdbExcel2013.IsChecked.Value) { fileName = "Sample.xlsx"; workbook.Version = ExcelVersion.Excel2013; workbook.SaveAs(fileName); } //Save as CSV format else if (rdbCsv.IsChecked.Value) { fileName = "Sample.csv"; worksheet.SaveAs(fileName, ","); } //Close the workbook. workbook.Close(); //Closing the excel engine excelEngine.Dispose(); if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { try { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] System.Diagnostics.Process.Start(fileName); //Exit this.Close(); } catch (Win32Exception ex) { MessageBox.Show("Ms Excel is not installed in this system"); Console.WriteLine(ex.ToString()); } } }
protected void Page_Load(object sender, EventArgs e) { ErrorMessage.InnerText = ""; string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None)); CheckFileStatus FileStatus = new CheckFileStatus(); ErrorMessage.InnerText = FileStatus.CheckFile(path); if (ErrorMessage.InnerText == "") { try { //Initializing the hive server connection HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2); //To initialize a Hive server connection with secured cluster //HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>"); //To initialize a Hive server connection with Azure cluster //HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>"); con.Open(); //Create table for adventure person contacts HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con); createCommand.ExecuteNonQuery(); HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con); //Executing the query HqlDataReader reader = command.ExecuteReader(); reader.FetchSize = 100; //Fetches the result from the reader and store it in a HiveResultSet set HiveResultSet result = reader.FetchResult(); StringWriter stringWriter = new StringWriter(); //Htmlwriter for creating table to append the HiveResults using (HtmlTextWriter writer = new HtmlTextWriter(stringWriter)) { writer.AddAttribute(HtmlTextWriterAttribute.Border, "1"); writer.AddStyleAttribute(HtmlTextWriterStyle.BorderCollapse, "collapse"); writer.RenderBeginTag(HtmlTextWriterTag.Table); writer.RenderBeginTag(HtmlTextWriterTag.Tr); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Contact ID"); writer.RenderEndTag(); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Full Name"); writer.RenderEndTag(); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Age"); writer.RenderEndTag(); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Email Address"); writer.RenderEndTag(); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Phone No"); writer.RenderEndTag(); writer.RenderBeginTag(HtmlTextWriterTag.Th); writer.Write("Modified Date"); writer.RenderEndTag(); writer.RenderEndTag(); int count = result.Count(); for (int i = 0; i < count; i++) { writer.RenderBeginTag(HtmlTextWriterTag.Tr); HiveRecord records = result[i]; for (int j = 0; j < records.Count; j++) { Object fields = records[j]; writer.RenderBeginTag(HtmlTextWriterTag.Td); writer.Write(fields); writer.RenderEndTag(); } writer.RenderEndTag(); } writer.RenderEndTag(); } //Binding the result to the RTE control string results = stringWriter.ToString(); rteControl.RTEContent.InnerHtml = results; //Closing the hive connection con.Close(); } catch (HqlConnectionException) { ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard."; } } }