/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, string progtype, string user, int No_of_individual_participants)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = true;
excel.DisplayAlerts = true;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = "Report Name - " + ReporType;
excelSheet.Cells[2, 1] = "Date of Report Generation : " + General_methods.get_current_date();
excelSheet.Cells[3, 1] = "Time of Report Generation :" + General_methods.get_current_time();
excelSheet.Cells[4, 1] = "Report Created By :" + user;
excelSheet.Cells[5, 1] = "Program Type :" + progtype;
// loop through each row and add values to our sheet
int rowcount = 6;
foreach (DataRow datarow in dataTable.Rows)
{
//adding one to rowcount
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 7)
{
excelSheet.Cells[6, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 7)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 4 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[5, dataTable.Columns.Count + 4]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelSheet.Cells[1, 1].Font.Size = 20;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartRange = excelSheet.Range[excelSheet.Cells[7, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/*
* @brief Btn_RdExcel_Click
* @note Test1.xlsx を読み込み、折れ線グラフを作成して、保存後、
* Excelを起動して表示
*/
private void Btn_RdExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Workbooks objBooks;
Microsoft.Office.Interop.Excel.Application objApp = null;
Microsoft.Office.Interop.Excel._Workbook objBook = null;
try
{
// 読み込み
objApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Open("C:\\TEMP\\Test1.xlsx");
// グラフを書く
Microsoft.Office.Interop.Excel.Worksheet thisWorksheet;
thisWorksheet = objBook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.ChartObjects charts =
(Microsoft.Office.Interop.Excel.ChartObjects)thisWorksheet.ChartObjects(Type.Missing);
// チャート作成（x = 100, y = 100, 幅500 高さ 300）
Microsoft.Office.Interop.Excel.ChartObject chartObj = charts.Add(100, 100, 500, 300);
Microsoft.Office.Interop.Excel.Chart chart = chartObj.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "LineMarker";
// データをセット.
Microsoft.Office.Interop.Excel.Range chartRange = thisWorksheet.get_Range("A1", "D5");
chart.SetSourceData(chartRange, Type.Missing);
// 折れ線グラフのチャート指定
// 参考 → http://home.att.ne.jp/zeta/gen/excel/c04p63.htm
//
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers; // 折れ線指定
chart.PlotBy = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns; // グラフのデータ系列を列方向
// ファイル保存
//objBook.SaveAs("C:\\TEMP\\Test1.xlsx");
objBook.Save();
// クローズ処理
objBook.Close();
objBooks.Close();
objApp.Quit();
// 作成した Excel 起動
System.Diagnostics.Process p =
System.Diagnostics.Process.Start("C:\\TEMP\\Test1.xlsx");
}
catch (Exception theException)
{
// エラーメッセージ出力
Console.Write(theException.ToString());
if (objBook != null)
{
objBook.Close();
}
if (objApp != null)
{
objApp.Quit();
}
}
}
public static void generateExcel()
{
packages = DBConnector.getInstance().getPackages();
buckets = DBConnector.getInstance().getBuckets();
generatePackageList();
checkProcess();
generateBucketList();
/******************** create a workbook *************************/
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing);
/********************* create new sheet (Activity List) ***************************/
excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheetAll.Name = "Activity List";
int row = 1;
int tb1_start_x = row;
int tb1_start_y = 1;
excelSheetAll.Cells[row, 1] = "Process Name";
excelSheetAll.Cells[row, 2] = "Duration";
excelSheetAll.Cells[row, 3] = "Main Window Title";
row++;
foreach (KeyValuePair<string, Activity> pair in activityList)
{
excelSheetAll.Cells[row, 1] = pair.Value.processName;
excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g");
excelSheetAll.Cells[row, 3] = pair.Key;
row++;
}
int tb1_end_x = row - 1;
int tb1_end_y = 3;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
/*************************** create new sheet (Packaged Activity List) ****************************/
excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetPackaged.Name = "Packaged Activity List";
row = 1;
int tb2_start_x = row;
int tb2_start_y = 1;
excelSheetPackaged.Cells[row, 1] = "Package Name";
excelSheetPackaged.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in packagedList)
{
excelSheetPackaged.Cells[row, 1] = pair.Key;
excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb2_end_x = row - 1;
int tb2_end_y = 2;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/************************* create new sheet (Bucketed Activity List) ******************************/
excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetBucketed.Name = "Bucketed Activity List";
row = 1;
int tb3_start_x = row;
int tb3_start_y = 1;
excelSheetBucketed.Cells[row, 1] = "Bucket Name";
excelSheetBucketed.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in bucketedList)
{
excelSheetBucketed.Cells[row, 1] = pair.Key;
excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb3_end_x = row - 1;
int tb3_end_y = 2;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/*************** save excel *******************/
//UserPrincipal.Current.DisplayName
String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//excelworkBook.SaveAs(filePath);
excelworkBook.Close();
excel.Quit();
Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------");
Console.WriteLine("Export to Excel");
Thread.Sleep(1000);
System.Diagnostics.Process.Start(filePath);
}
public static void ExportFile(Dictionary <AttributeTypeCode, List <entityParam> > entityParam, EntityInfo entityInfo)
{
SaveFileDialog sfd = null;
DataColumns[] fromatedList = FormatDataForExport(entityParam);
String[] columnsHeaderName = new string[] {
"Display Name", "Schema Name", "Type", "Target", "Managed/Unmanaged", "IsAuditable", "IsSearchable", "Required Level", "Introduced Version", "CreatedOn", "Percentage Of Use"
};
int headerIndex = 9;
int lineIndex = headerIndex + 1;
if (fromatedList.Length > 0)
{
sfd = new SaveFileDialog();
sfd.Filter = "Excel (.xlsx)| *.xlsx;*.xls;";
sfd.FileName = entityInfo.entityName + "_EntityKPIsExport_" + DateTime.Now.ToShortDateString().Replace('/', '-') + ".xlsx";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
}
}
if (!fileError)
{
try
{
Microsoft.Office.Interop.Excel._Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
#region DataGrid
worksheet = workbook.ActiveSheet;
if (entityInfo.entityName.Length > 21)
{
worksheet.Name = entityInfo.entityName.Substring(0, 21) + "_MetaData";
}
else
{
worksheet.Name = entityInfo.entityName + "_MetaData";
}
if (entityInfo != null)
{
for (int i = 1; i <= 6; i++)
{
worksheet.Cells[i, 1].Font.Bold = true;
worksheet.Cells[i, 1].Interior.Color = Color.Wheat;
worksheet.Cells[i, 1].Font.Size = 12;
}
worksheet.Cells[1, 1] = "Entity Display Name";
worksheet.Cells[1, 2] = entityInfo.entityName;
worksheet.Cells[2, 1] = "Entity Technical Name";
worksheet.Cells[2, 2] = entityInfo.entityTechnicalName;
worksheet.Cells[3, 1] = "CreatedOn";
worksheet.Cells[3, 2] = entityInfo.entityDateOfCreation != DateTime.MinValue ? entityInfo.entityDateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[4, 1] = "Number Of Fields";
worksheet.Cells[4, 2] = entityInfo.entityFieldsCount;
worksheet.Cells[5, 1] = "Number Of Records";
worksheet.Cells[5, 2] = entityInfo.entityRecordsCount;
worksheet.Cells[6, 1] = "Entity Fields Volume Usage";
worksheet.Cells[6, 2] = ((entityInfo.entityTotalUseOfColumns * 100) / entityInfo.entityDefaultColumnSize).ToString("0.##\\%");
}
for (int i = 1; i < columnsHeaderName.Length + 1; i++)
{
worksheet.Cells[headerIndex, i] = columnsHeaderName[i - 1];
worksheet.Cells[headerIndex, i].Font.NAME = "Calibri";
worksheet.Cells[headerIndex, i].Font.Bold = true;
worksheet.Cells[headerIndex, i].Interior.Color = Color.Wheat;
worksheet.Cells[headerIndex, i].Font.Size = 12;
}
for (int i = 0; i < fromatedList.Length; i++)
{
worksheet.Cells[i + lineIndex, 1] = fromatedList[i].displayName;
worksheet.Cells[i + lineIndex, 2] = fromatedList[i].fieldName;
worksheet.Cells[i + lineIndex, 3] = fromatedList[i].fieldType;
worksheet.Cells[i + lineIndex, 4] = fromatedList[i].target;
worksheet.Cells[i + lineIndex, 5] = fromatedList[i].isManaged;
worksheet.Cells[i + lineIndex, 6] = fromatedList[i].isAuditable;
worksheet.Cells[i + lineIndex, 7] = fromatedList[i].isSearchable;;
worksheet.Cells[i + lineIndex, 8] = fromatedList[i].requiredLevel;;
worksheet.Cells[i + lineIndex, 9] = fromatedList[i].introducedVersion;
worksheet.Cells[i + lineIndex, 10] = fromatedList[i].dateOfCreation != DateTime.MinValue ? fromatedList[i].dateOfCreation.ToShortDateString() : String.Empty;
worksheet.Cells[i + lineIndex, 11] = fromatedList[i].percentageOfUse.Replace(",", ".");
if (fromatedList[i].target == String.Empty)
{
worksheet.Cells[i + lineIndex, 4].Interior.Color = Color.Gainsboro;
}
}
worksheet.Columns.AutoFit();
#endregion
var xlSheets = workbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[1], Type.Missing, Type.Missing);
xlNewSheet.Name = "Charts";
#region chartManagedUnmanaged
//add data
xlNewSheet.Cells[2, 2] = "Managed";
xlNewSheet.Cells[2, 2].Font.Color = Color.White;
xlNewSheet.Cells[2, 3] = entityInfo.managedFieldsCount;
xlNewSheet.Cells[2, 3].Font.Color = Color.White;
xlNewSheet.Cells[3, 2] = "Unmanaged";
xlNewSheet.Cells[3, 2].Font.Color = Color.White;
xlNewSheet.Cells[3, 3] = entityInfo.unmanagedFieldsCount;
xlNewSheet.Cells[3, 3].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
chartPage.HasTitle = true;
chartPage.ChartTitle.Text = @"Managed\Unmanaged Fields";
chartRange = xlNewSheet.get_Range("B2", "C3");
chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region EntityFieldsCreated
//add data
xlNewSheet.Cells[2, 10] = "Available Fields To Create";
xlNewSheet.Cells[2, 10].Font.Color = Color.White;
xlNewSheet.Cells[2, 11] = entityInfo.entityDefaultColumnSize - entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[2, 11].Font.Color = Color.White;
xlNewSheet.Cells[3, 10] = "Created Fields";
xlNewSheet.Cells[3, 10].Font.Color = Color.White;
xlNewSheet.Cells[3, 11] = entityInfo.entityTotalUseOfColumns;
xlNewSheet.Cells[3, 11].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeTotaluse;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsTotalUse = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject totalUseChartChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsTotalUse.Add(510, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageTotalUse = totalUseChartChart.Chart;
chartPageTotalUse.HasTitle = true;
chartPageTotalUse.ChartTitle.Text = @"Entity Fields Created";
chartRangeTotaluse = xlNewSheet.get_Range("J2", "K3");
chartPageTotalUse.SetSourceData(chartRangeTotaluse, System.Reflection.Missing.Value);
chartPageTotalUse.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region CustomStandar
//add data
xlNewSheet.Cells[2, 20] = "Standard Fields";
xlNewSheet.Cells[2, 20].Font.Color = Color.White;
xlNewSheet.Cells[2, 21] = entityInfo.entityStandardFieldsCount;
xlNewSheet.Cells[2, 21].Font.Color = Color.White;
xlNewSheet.Cells[3, 20] = "Custom Fields";
xlNewSheet.Cells[3, 20].Font.Color = Color.White;
xlNewSheet.Cells[3, 21] = entityInfo.entityCustomFieldsCount;
xlNewSheet.Cells[3, 21].Font.Color = Color.White;
Microsoft.Office.Interop.Excel.Range chartRangeCustomStandard;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsCustomStandard = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject customStandardChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsCustomStandard.Add(1010, 10, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPageCustomStandard = customStandardChart.Chart;
chartPageCustomStandard.HasTitle = true;
chartPageCustomStandard.ChartTitle.Text = @"Custom\Standard Fields";
chartRangeCustomStandard = xlNewSheet.get_Range("T2", "U3");
chartPageCustomStandard.SetSourceData(chartRangeCustomStandard, System.Reflection.Missing.Value);
chartPageCustomStandard.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
#region FieldsType
//add data
int indicator = 0;
foreach (var item in entityParam)
{
xlNewSheet.Cells[indicator + 21, 10] = item.Key.ToString();
xlNewSheet.Cells[indicator + 21, 10].Font.Color = Color.White;
xlNewSheet.Cells[indicator + 21, 11] = item.Value.Count;
xlNewSheet.Cells[indicator + 21, 11].Font.Color = Color.White;
indicator++;
}
Microsoft.Office.Interop.Excel.Range chartRangeFieldType;
Microsoft.Office.Interop.Excel.ChartObjects xlChartsFieldTypes = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject fieldTypes = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(485, 270, 350, 300);
Microsoft.Office.Interop.Excel.Chart chartPageFieldTypes = fieldTypes.Chart;
chartPageFieldTypes.HasTitle = true;
chartPageFieldTypes.ChartTitle.Text = @"Entity Fields Types";
chartRangeFieldType = xlNewSheet.get_Range("J21", ("K" + (21 + (indicator - 1))).ToString());
chartPageFieldTypes.SetSourceData(chartRangeFieldType, System.Reflection.Missing.Value);
chartPageFieldTypes.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut;
#endregion
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets[1];
sheet.Activate();
workbook.SaveAs(sfd.FileName);
XcelApp.Quit();
ReleaseObject(worksheet);
ReleaseObject(xlNewSheet);
ReleaseObject(workbook);
ReleaseObject(XcelApp);
if (MessageBox.Show("Would you like to open it?", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
Process.Start(sfd.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message);
}
}
}
}
}
private void buttonExportarExcel_Click(object sender, EventArgs e)
{
//FrmGrafico grafico = new FrmGrafico(frecuenciasObservada, frecuenciaEsperada);
//Chart chart1 = grafico.devolverGrafico();
//grafico.Dispose();
//chart1.SaveImage(".\\hola.png", ChartImageFormat.Png);
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Hoja1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Lista Aleatoria";
// storing header part in Excel
for (int i = 1; i < this.dgvMuestra.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = this.dgvMuestra.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < this.dgvMuestra.Rows.Count - 1; i++)
{
for (int j = 0; j < this.dgvMuestra.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = this.dgvMuestra.Rows[i].Cells[j].Value.ToString();
}
}
worksheet.Cells[1, 5] = "Observada";
worksheet.Cells[1, 6] = "Esperada";
worksheet.Cells[2, 4] = "Media";
worksheet.Cells[3, 4] = "Varianza";
worksheet.Cells[2, 5] = this.labelMediaObservada.Text;
worksheet.Cells[2, 6] = this.labelVarianzaObservada.Text;
worksheet.Cells[3, 5] = "0.5";
worksheet.Cells[3, 6] = "0.0833";
worksheet.Cells[2, 9] = "Estadístico de Prueba";
worksheet.Cells[2, 10] = this.labelChiCuadrado.Text;
//worksheet.Columns.Width = 100;
for (int i = 1; i < this.dgvFrecuencia.Columns.Count + 1; i++)
{
worksheet.Cells[5, i + 3] = this.dgvFrecuencia.Columns[i - 1].HeaderText;
}
for (int i = 0; i < this.dgvFrecuencia.Rows.Count - 1; i++)
{
for (int j = 0; j < this.dgvFrecuencia.Columns.Count; j++)
{
worksheet.Cells[i + 6, j + 4] = this.dgvFrecuencia.Rows[i].Cells[j].Value.ToString();
}
}
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(500, 100, 500, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
int cantIntervalos = dgvFrecuencia.Rows.Count;
chartRange = worksheet.get_Range("F5", "G" + (cantIntervalos + 5).ToString());
chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
//myChart.TopLeftCell = worksheet.Cells[3, 10];
//worksheet.Shapes.AddPicture(".\\hola.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 45);
// save the application
//workbook.SaveAs(".\\output"+documentoExcel.ToString()+".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//documentoExcel += 1;
// Exit from the application
//app.Quit();
}
