/// <summary> /// 将数据写入Microsoft.Office.Interop.Excel /// </summary> /// <param name="data">要写入的字符串</param> /// <param name="starRow">写入的行</param> /// <param name="startColumn">写入的列</param> public void WriteData(string data, string fileName, string sheetName, int row, int column) { try { Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkBook; myExcel = new Microsoft.Office.Interop.Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); myExcel.Cells[row, column] = data; myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } //Microsoft.Office.Interop.Excel.Range rng3 = xSheet.get_Range("C6", System.Reflection.Missing.Value); //rng3.Cells.FormulaR1C1 = txtCellText.Text; // rng3.Interior.ColorIndex = 6; //设置Range的背景色 }
public bool ConvertSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, string exportDir) { if (sheet == null) { return(false); } sheet.Activate(); if (sheet.Cells == null || string.IsNullOrEmpty(exportDir) || sheet.UsedRange == null || sheet.UsedRange.Rows.Count < 3 || sheet.UsedRange.Columns.Count <= 0) { return(false); } // 转换 if (!ConvertSheetToCs(sheet, exportDir)) { return(false); } if (!ConvertSheetToJson(sheet, exportDir)) { return(false); } return(true); }
public static void exportToExcel(DataGridView dgv) { Application.UseWaitCursor = true; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int ColumnIndex = 0; foreach (DataGridViewColumn col in dgv.Columns) { ColumnIndex++; excel.Cells[1, ColumnIndex] = col.HeaderText; } int rowIndex = 0; foreach (DataGridViewRow row in dgv.Rows) { rowIndex++; ColumnIndex = 0; foreach (DataGridViewColumn col in dgv.Columns) { ColumnIndex++; excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value; } } excel.Visible = true; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet; worksheet.Activate(); Application.UseWaitCursor = false; }
//_______________________________________添加自定义函数_________________________________________________________________ /// <summary> /// 将数据写入Microsoft.Office.Interop.Excel /// </summary> /// <param name="data">要写入的二维数组数据</param> /// <param name="startRow">Microsoft.Office.Interop.Excel中的起始行</param> /// <param name="startColumn">Microsoft.Office.Interop.Excel中的起始列</param> public void WriteData(string[,] data, string fileName, string sheetName, int startRow, int startColumn) { try { Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkBook; myExcel = new Microsoft.Office.Interop.Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); int rowNumber = data.GetLength(0); int columnNumber = data.GetLength(1); for (int i = 0; i < rowNumber; i++) { for (int j = 0; j < columnNumber; j++) { //在Microsoft.Office.Interop.Excel中,如果某单元格以单引号“'”开头,表示该单元格为纯文本,因此,我们在每个单元格前面加单引号。 myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j]; } } myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } }
cmdOpenWS_Click(System.Object eventSender, System.EventArgs eventArgs) { string strPath = BaseObjs.docFullName; string strJN = BaseObjs.docName.Substring(1, 4); string strFN = strJN + "EW" + ".xlsx"; string strFullPath = strPath + "\\" + strFN; Microsoft.Office.Interop.Excel.Application objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelApp.Visible = true; Microsoft.Office.Interop.Excel.Workbook objWB = objExcelApp.Workbooks.Open(strFullPath); Microsoft.Office.Interop.Excel.Worksheet objWS = objExcelApp.Worksheets["SUMMARY"]; objWS.Activate(); }
/// <summary> /// 读取指定单元格数据 /// </summary> /// <param name="row">行序号</param> /// <param name="column">列序号</param> /// <returns>该格的数据</returns> public string ReadData(string fileName, string sheetName, int row, int column) { Microsoft.Office.Interop.Excel.Application myExcel; Microsoft.Office.Interop.Excel.Workbook myWorkBook; myExcel = new Microsoft.Office.Interop.Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); Microsoft.Office.Interop.Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]); string str = range.Text.ToString(); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); return(str); }
private bool WriteHeaderOfResExcel(Microsoft.Office.Interop.Excel.Application excelTo, Microsoft.Office.Interop.Excel.Application excelFrom, int iStartRowID) { if (excelTo == null) { return(false); } if (excelFrom == null) { return(false); } try { Microsoft.Office.Interop.Excel.Worksheet fromSheet = excelFrom.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; Microsoft.Office.Interop.Excel.Worksheet toSheet = excelTo.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; object MissingValue = Type.Missing; int ColCnt = fromSheet.UsedRange.Columns.Count; // 选择 fromSheet.Select(MissingValue); // 复制. fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[iStartRowID - 1, ColCnt]).Copy(MissingValue); toSheet.Activate(); toSheet.Select(MissingValue); // 粘贴格式. toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue); // 粘贴数据. toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue); fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[1, 1]).Copy(MissingValue); } catch { return(false); } return(true); }
/// <summary> /// Click event to handle the export to excel /// </summary> /// <param name="sender">Sender object</param> /// <param name="e">event data</param> private void Export_Click(object sender, EventArgs e) { try { var table = (DataTable)dataGridViewMembers.DataSource; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; excel.Cells[1, columnIndex] = col.ColumnName; } int rowIndex = 0; foreach (DataRow row in table.Rows) { rowIndex++; columnIndex = 0; foreach (DataColumn col in table.Columns) { columnIndex++; if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6) { if (columnIndex == 4) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(Occupation), row[col.ColumnName]); } if (columnIndex == 5) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(MaritalStatus), row[col.ColumnName]); } if (columnIndex == 6) { excel.Cells[rowIndex + 1, columnIndex] = Enum.GetName(typeof(HealthStatus), row[col.ColumnName]); } } else { excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString(); } } } excel.Visible = true; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet; worksheet.Activate(); } catch (Exception ex) { this.ShowErrorMessage(ex); } }
private static void CreateChart(List <SignalGenerator.Day> data, string symbol, string templatePath) { Console.WriteLine("Generating..."); var app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel.Workbook book = null; try { books = app.Workbooks; book = books.Open(templatePath); app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual; app.ScreenUpdating = false; Microsoft.Office.Interop.Excel.Chart sheetG = book.Charts[1]; Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[1]; sheet.Activate(); var mindate = data.Min(d => d.Date); var maxdate = data.Max(d => d.Date); var lowest = data.Min(d => d.LowestValue()); var highest = data.Max(d => d.HighestValue()); var range = highest - lowest; lowest -= (range * 0.05); highest += (range * 0.05); if (lowest < 0) { lowest = 0; } var title = $"{symbol.ToUpper()}: {mindate:yyyy-MM-dd} - {maxdate:yyyy-Mm-dd}"; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 10]).Value = title; var row = 1; var isin = data[0].In; var p = 0; foreach (var day in data) { var np = row * 100 / data.Count; if (np != p) { Console.WriteLine($"{np}%"); p = np; } row++; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 1]).Value = day.Date; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 2]).Value = day.Open; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 3]).Value = day.High; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 4]).Value = day.Low; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 5]).Value = day.Close; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 6]).Value = day.BuyLine; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 7]).Value = day.SellLine; if (day.StopLoss > 0) { ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 8]).Value = day.StopLoss; } if (day.In != isin) { ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 9]).Value = day.In ? 1 : 0; isin = day.In; } } sheetG.Activate(); Console.WriteLine("Done"); app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic; app.ScreenUpdating = true; var axis = sheetG.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Microsoft.Office.Interop.Excel.Axis; lowest -= lowest % axis.MajorUnit; highest += axis.MajorUnit; highest -= highest % axis.MajorUnit; axis.MinimumScale = lowest; axis.MaximumScale = highest; app.Visible = true; Console.WriteLine("Press any key when ready"); Console.ReadKey(); } catch (Exception ex) { Console.WriteLine($"Exception: {ex.Message}"); } try { if (book != null) { book.Close(false); } if (books != null) { books.Close(); } app.Quit(); for (var i = 0; i < 10; i++) { GC.Collect(); } } catch { } }
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); } } } } }
public void ConvertToExcel(DataSet ds, string strFilenamewithpath) { FnWriteLogFile_Log("", "ConvertToExcel1"); //Instance reference for Excel Application Microsoft.Office.Interop.Excel.Application objXL = null; //Workbook refrence Microsoft.Office.Interop.Excel.Workbook objWB = null; objXL = new Microsoft.Office.Interop.Excel.Application(); // Find the Excel Process Id (ath the end, you kill him int id; GetWindowThreadProcessId(objXL.Hwnd, out id); Process excelProcess = Process.GetProcessById(id); FnWriteLogFile_Log("", "ConvertToExcel3"); objWB = objXL.Workbooks.Open(Server.MapPath("~/Uploads/") + ds.Tables[0].Rows[0][0].ToString());// Server.MapPath("\Log\Feet on Street Report.xlsx"); try { //Instancing Excel using COM services //Adding WorkBook //FnWriteLogFile_Log("", "objXL.Workbooks.Add(1)"); //objWB = objXL.Workbooks.Add(1);//ds.Tables.Count // Server.MapPath("~/ExcelFile/Log/myexcel.xlsx") + "" //Variable to keep sheet count int sheetcount = 1; //Do I need to explain this ??? If yes please close my website and learn abc of .net . //oSheet = oWorkBook.Sheets.Item(1) FnWriteLogFile_Log("", "add sheet"); Microsoft.Office.Interop.Excel.Worksheet objSHT = null; //foreach (System.Data.DataTable dt in ds.Tables) objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[4]; DataTable dt = ds.Tables[2]; //Adding sheet to workbook for each datatable //Naming sheet as SheetData1.SheetData2 etc.... FnWriteLogFile_Log("", "Naming sheet"); objSHT.Name = ds.Tables[1].Rows[0][0].ToString(); for (int i = 0; i < dt.Columns.Count; i++) { objSHT.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString(); objSHT.Range[objSHT.Cells[1, i + 1], objSHT.Cells[1, i + 1]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); //# d9d9d9 //objSHT.Cells[2, i + 1].Interior.Color = System.Drawing.ColorTranslator.FromHtml("#d9d9d9"); //# d9d9d9 //objSHT.Cells[1, i + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver); } Int32 rows; Int32 columns; rows = dt.Rows.Count; columns = dt.Columns.Count; var Data = new object[rows - 1 + 1, columns - 1 + 1]; for (var i = 0; i <= dt.Rows.Count - 1; i += 1) { for (var j = 0; j <= dt.Columns.Count - 1; j += 1) { Data[i, j] = dt.Rows[i][j]; } } var startCell = objSHT.Cells[2, 1]; var endCell = objSHT.Cells[1 + dt.Rows.Count, dt.Columns.Count]; var writeRange = objSHT.Range[startCell, endCell]; writeRange.Value = Data; //Incrementing sheet count //Saving the work book objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[1]; objSHT.Activate(); //objSHT.Select(Type.Missing); objWB.Saved = true; objWB.SaveCopyAs(Server.MapPath("~/Uploads/New/") + ds.Tables[0].Rows[0][0].ToString()); //Closing work book FnWriteLogFile_Log("", "objWB.Close()"); objWB.Close(); //Closing excel application objXL.Quit(); //Marshal.ReleaseComObject(objSHT); FnWriteLogFile_Log("", "set null"); objSHT = null; objWB = null; objXL = null; GC.Collect(); GC.WaitForPendingFinalizers(); if (objSHT != null) { Marshal.ReleaseComObject(objSHT); } if (objWB != null) { Marshal.ReleaseComObject(objWB); } if (objXL != null) { Marshal.ReleaseComObject(objXL); } FnWriteLogFile_Log("", "complete"); //HttpContext.Current.Response.End(); } catch (Exception ex) { FnWriteLogFile_Log("", "Error:" + ex.StackTrace.ToString()); if (objWB != null) { objWB.Close(); //Closing excel application objXL.Quit(); //Marshal.ReleaseComObject(objSHT); // objSHT = null; objWB = null; objXL = null; GC.Collect(); GC.WaitForPendingFinalizers(); //if (objSHT != null) //{ // Marshal.ReleaseComObject(objSHT); //} if (objWB != null) { Marshal.ReleaseComObject(objWB); } if (objXL != null) { Marshal.ReleaseComObject(objXL); } //Response.Write("Illegal permission"); } } finally { FnWriteLogFile_Log("", "Check HasExited"); if (!excelProcess.HasExited) { FnWriteLogFile_Log("", "kill process"); excelProcess.Kill(); FnWriteLogFile_Log("", "end kill process"); } } }
/// <summary> /// 激活一个工作表 /// </summary> /// <param name="strName"></param> public void ActiveSheet(string strName) { Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)m_pWb.Worksheets[strName]; ws.Activate(); }
private void bomoToexcel() { DataTable _px = new DataTable(); var query = new BmobQuery(); query.WhereGreaterThan("indexID", 2651); //分数大于60岁 query.OrderBy("indexID"); query.Limit(600); var childfuture = Bmob.FindTaskAsync <computerTiKu>("computerTiKu", query); if (childfuture.Result.results.Count != 0) { _px = ToDataTable <computerTiKu>(childfuture.Result.results); // MessageBox.Show("总行数" + _px.Rows.Count); } //_dspx = AdDispatchManage.GetValue(sqlstr); int row; row = 2; int b; b = 0; string PathStr; string SourceFileName; string DestinationFileName; PathStr = Application.StartupPath.Trim(); // MakeBigWord.MakeBigWord.KillExcelProcess(); DestinationFileName = PathStr + @"\plantmp.xls"; SourceFileName = PathStr + @"\yhygzpx.xls"; try { File.Delete(DestinationFileName); File.Copy(SourceFileName, DestinationFileName); } catch (Exception ee) { MessageBox.Show(ee.Message); return; } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(DestinationFileName); Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Worksheets[1]; xlSheet.Activate(); xlSheet.Application.Visible = true; try { for (int i = 0; i < _px.Rows.Count; i++) { if (_px.Rows[i]["titleSubject"].ToString() != null) { xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString(); } else { MessageBox.Show(i + "titleSubject"); } if (_px.Rows[i]["titleSubject"].ToString() != null) { xlSheet.Cells[i + row, 1] = _px.Rows[i]["titleSubject"].ToString(); } else { MessageBox.Show(i + "titleSubject"); } if (_px.Rows[i]["optionA"].ToString() != null && _px.Rows[i]["optionB"].ToString() != null && _px.Rows[i]["optionC"].ToString() != null && _px.Rows[i]["optionD"].ToString() != null) { Choice[] student = new Choice[4]; student[0] = new Choice(); student[0].key = "A"; student[0].content = _px.Rows[i]["optionA"].ToString(); student[1] = new Choice(); student[1].key = "B"; student[1].content = _px.Rows[i]["optionB"].ToString(); student[2] = new Choice(); student[2].key = "C"; student[2].content = _px.Rows[i]["optionC"].ToString(); student[3] = new Choice(); student[3].key = "D"; student[3].content = _px.Rows[i]["optionD"].ToString(); string aa = Newtonsoft.Json.JsonConvert.SerializeObject(student); xlSheet.Cells[i + row, 2] = aa; //设置一个Person类 //Choice p = new Choice(); //p.key = _px.Rows[i]["optionA"].ToString(); //p.content = _px.Rows[i]["optionB"].ToString(); //p.C = _px.Rows[i]["optionC"].ToString(); //p.D = _px.Rows[i]["optionD"].ToString(); //string json1 = JsonConvert.SerializeObject(p); //Console.WriteLine(json1 + "\n"); ////缩进输出 //string json2 = JsonConvert.SerializeObject(p, Formatting.Indented); //Console.WriteLine(json2 + "\n"); // {"A":"+价格发现功能+","B":"+套利功能+","C":"+投机功能+","D":"+套期保值功能"} } else { MessageBox.Show(i + "A=" + _px.Rows[i]["optionA"].ToString() + "B=" + _px.Rows[i]["optionB"].ToString() + "C=" + _px.Rows[i]["optionC"].ToString() + "D=" + _px.Rows[i]["optionD"].ToString()); } xlSheet.Cells[i + row, 3] = "3RcsJ77J"; if (_px.Rows[i]["answer"].ToString() != null) { String title = _px.Rows[i]["answer"].ToString().Substring(4, _px.Rows[i]["answer"].ToString().Length - 4); ///去掉两个字符串 xlSheet.Cells[i + row, 4] = "[\"" + title + "\"]"; } else { MessageBox.Show(i + "answer"); } if (_px.Rows[i]["explain"].ToString() != null) { xlSheet.Cells[i + row, 5] = _px.Rows[i]["explain"].ToString(); } else { MessageBox.Show(i + "explain"); } xlSheet.Cells[i + row, 6] = 500 + i; xlSheet.Cells[i + row, 7] = "P0Vm666E"; b = i; //xlSheet.Cells[i + row, 2] = _px.Rows[i]["indexID"].ToString(); //xlSheet.Cells[i + row, 3] = _px.Rows[i]["titleSubject"].ToString(); //xlSheet.Cells[i + row, 4] = _px.Rows[i]["answer"].ToString(); //xlSheet.Cells[i + row, 5] = _px.Rows[i]["optionA"].ToString(); //xlSheet.Cells[i + row, 6] = _px.Rows[i]["chapterflag"].ToString(); //xlSheet.Cells[i + row, 7] = _px.Rows[i]["core"].ToString(); //xlSheet.Cells[i + row, 8] = _px.Rows[i]["answerCode"].ToString(); //xlSheet.Cells[i + row, 9] = "'" + _px.Rows[i]["optionB"].ToString() + "/" + _px.Rows[i]["optionC"].ToString(); //xlSheet.Cells[i + row, 10] = _px.Rows[i]["optionD"].ToString(); //xlSheet.Cells[i + row, 11] = _px.Rows[i]["questiontype"].ToString(); //xlSheet.Cells[i + row, 12] = _px.Rows[i]["subjectType"].ToString() + "/" + _px.Rows[i]["explain"].ToString(); } } catch { } try { Microsoft.Office.Interop.Excel.Range r1 = xlSheet.Range[xlSheet.Cells[3, 1], xlSheet.Cells[b + row + 4, 6]]; r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = 7; r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = 7; r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = 7; r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = 7; r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = 7; } catch { } }