public static void TestToPDF() { string strFileName = @"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/03_XXX_ABC株式会社(レポート).xls"; Action <Excel.Application, Excel.Workbook> action = (Excel.Application app, Excel.Workbook wb) => { //wb.Worksheets.Select();//导出全部sheet //wb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, // @"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/20180227_01.pdf", // Excel.XlFixedFormatQuality.xlQualityStandard); Excel.Workbook tmpWb = app.Workbooks.Add(); for (int i = 3; i <= 25; i++) { Excel.Worksheet copysheet = (Excel.Worksheet)wb.Sheets[i]; copysheet.Copy(tmpWb.Worksheets[tmpWb.Worksheets.Count]); } Excel.Worksheet lastsheet = (Excel.Worksheet)tmpWb.Worksheets[tmpWb.Worksheets.Count]; Log.Println("最后一个sheet名: " + lastsheet.Name); if (lastsheet.Name.Equals("Sheet1")) { lastsheet.Delete(); //删除最后一个名字是Sheet1的sheet } tmpWb.Worksheets.Select(); tmpWb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, @"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/20180227_01.pdf", Excel.XlFixedFormatQuality.xlQualityStandard); Log.Println("PDF文件导出成功。"); //tmpWb.SaveAs(@"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/tmp20180227_01.xls"); //tmpWb.Close(); tmpWb.Close(false, Type.Missing, Type.Missing); }; ExcelUtil.GetExcelWorkbook(strFileName, action); }
private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheet, string sheetName, string sourceBeginCell, string sourceEndCell, string destBeginCell, string reportFile, int sequence) { if (File.Exists(reportFile)) { Excel.Workbook sourceBook = null; try { sourceBook = excel.Workbooks.Open(reportFile); Excel.Worksheet workingSheet = sourceBook.Worksheets[1]; workingSheet.Name = sheetName + sequence; Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet; workingSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence]; ExcelUtilies.CopyRange(copySourceSheet, sourceBeginCell, sourceEndCell, copyDestSheet, destBeginCell); } catch (Exception ex) { this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace)); throw; } finally { FunnelReportHelper.CloseWorkingWorkbook(sourceBook); } } }
private void addNewSheetForm4A() { currentRow = 12; mainSheet = false; currentSheetNumber++; // TODO: Перейти на новый лист или скопировать если необходимо int sheetCount = excelWBook.Worksheets.Count; Excel.Worksheet newSheet = excelWBook.Worksheets[sheetCount]; // Снять защиту с листа if (sheetCount == 2) { newSheet.Unprotect("rogachev"); } // Копируем на перед один чистый лист newSheet.Copy(misValue, excelWBook.Worksheets[sheetCount]); excelWSheet = excelWBook.Sheets[currentSheetNumber]; // TODO: Печатать номер текущего листа fillTotalPageNumber(); fillPageNumber(); }
static void copypaste(Excel.Application excelapp, Excel.Workbook source, Excel.Workbook destination, string worksheetname, string filter, int filtercolumn, string lastcolumn) { Excel.Worksheet sourceworksheet = source.Worksheets[worksheetname]; sourceworksheet.Copy(destination.Worksheets[1]); Excel.Worksheet destinationworksheet = destination.Worksheets[worksheetname]; long rows = sourceworksheet.UsedRange.Rows.Count; destinationworksheet.Rows["2:" + rows + 1].Delete(Excel.XlDeleteShiftDirection.xlShiftUp); string filterrange = "A1:" + lastcolumn + rows; string copyrange = "A2:" + lastcolumn + rows; try { sourceworksheet.Range[filterrange].AutoFilter(filtercolumn, "=*" + filter + "*", Excel.XlAutoFilterOperator.xlAnd); sourceworksheet.Range[copyrange].SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy(); destinationworksheet.Range[copyrange].PasteSpecial(); //destinationworksheet.Rows["2"].Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } catch (Exception) { destinationworksheet.Delete(); Console.WriteLine("Cannot filter " + worksheetname + ", sheet removed for " + destination.Path); } }
static void Main(string[] args) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Factory\Documents\scope\ControlesDegradesAvecDirections T2 2019 - Copie - Copie.xlsx"); Excel.Range oRng = null; Excel.Worksheet worksheet1 = (xlWorkBook.Worksheets[1]); Excel.Worksheet worksheet3 = (xlWorkBook.Worksheets[1]); worksheet1.Copy(worksheet3); xlWorkBook.Worksheets[1].Columns["B"].Insert(); xlWorkBook.Worksheets[1].Columns["D"].Insert(); xlWorkBook.Worksheets[1].Columns["F"].Insert(); xlWorkBook.Worksheets[1].Columns["H"].Insert(); xlWorkBook.Worksheets[1].Columns["J"].Insert(); xlWorkBook.Worksheets[1].Columns["L"].Insert(); xlWorkBook.Worksheets[1].Columns["N"].Insert(); xlWorkBook.Worksheets[1].Columns["P"].Insert(); oRng = xlWorkBook.Worksheets[1].UsedRange; calculateWorkSheet1(oRng, 1, 2); //calculateWorkSheet1(oRng, 3, 4); /*calculateWorkSheet1(oRng, 5, 6); * calculateWorkSheet1(oRng, 7, 8); * calculateWorkSheet1(oRng, 9, 10); * calculateWorkSheet1(oRng, 11, 12); * calculateWorkSheet1(oRng, 13, 14);*/ xlWorkBook.Saved = true; xlWorkBook.Save(); xlApp.Quit(); }
private void Btn_nuevo_Click(object sender, EventArgs e) { Excel.Worksheet sheet = Sheet_List[Sheet_List.Count - 1]; sheet.Copy(Type.Missing, sheet); var copySheetIndex = sheet.Index + 1; Excel.Worksheet copySheet = ExcelSheets.get_Item(copySheetIndex); int aux_mes = 99; for (int i = 0; i < Months.Length; i++) { if (ExcelBooks.Sheets[ExcelBooks.Sheets.Count - 1].Name.Contains(Months[i])) { if ((i + 1) == 12) { aux_mes = 0; } else { aux_mes = i + 1; } break; } } copySheet.Name = Months[aux_mes] + " " + Initial_Year.ToString(); ExcelBooks.Save(); Sheet_List.Add(copySheet); sheet_names.Add(copySheet.Name); Read_Data_Worksheet(); pending_grid_refresh = true; }
/// <summary> /// 复制并添加一个工作表 /// </summary> /// <param name="OldSheetName"> 被复制工作表</param> /// <param name="NewSheetName">新表</param> public void CloneSheet(string OldSheetName, string NewSheetName) { Microsoft.Office.Interop.Excel.Worksheet oldSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName]; oldSheet.Copy(oldSheet, Type.Missing); Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName + " (2)"]; s.Name = NewSheetName; }
//按模板生成报表 private void reportAccordTemplet(Excel.Worksheet templet, DataTable recordTb) { this.Application.ScreenUpdating = false; //复制模板到新的工作簿 Excel.Workbook newWorkbook = this.Application.Workbooks.Add(); templet.Copy(newWorkbook.Worksheets[1]); //newWorkbook.Application.Caption = templetComBox.Text; Excel.Worksheet newWorkSheet = newWorkbook.Worksheets[templetComBox.Text]; //删除自动生成的SHEET1 newWorkbook.Worksheets["Sheet1"].Delete(); newWorkSheet.Activate(); for (int row = 0; row < recordTb.Rows.Count; row++) { int startRow = newWorkSheet.UsedRange.Rows.Count + 1; //在基础数据表中查找对应站址编码的位置 int baseTableIndex = FindBaseTableIndex(recordTb.Rows[row]["站址编码"].ToString()); for (int col = 1; col <= templet.UsedRange.Columns.Count; col++) { string tempString = templet.Cells[1, col].value.ToString(); switch (tempString) { case "常量": newWorkSheet.Cells[startRow, col] = templet.Cells[2, col]; break; case "计算": newWorkSheet.Cells[2, col].copy(); newWorkSheet.Cells[startRow, col].PasteSpecial(); break; case "发电记录": newWorkSheet.Cells[startRow, col] = recordTb.Rows[row][templet.Cells[2, col].value]; break; case "基础信息": newWorkSheet.Cells[startRow, col] = baseTable.Rows[baseTableIndex][templet.Cells[2, col].value.ToString()]; break; default: break; } } } //删除模板上面两行 newWorkSheet.Rows[1].Delete(); newWorkSheet.Rows[1].Delete(); //if (templetComBox.Text != "日报表模板") this.Application.ScreenUpdating = true; }
private void button4_Click(object sender, EventArgs e) { string _strFileName = @"r:\abcd.xls"; MSOffice.Application App = new MSOffice.Application(); MSOffice.Workbook Book = App.Workbooks.Add(MSOffice.XlWBATemplate.xlWBATWorksheet); MSOffice.Worksheet Sheet = (MSOffice.Worksheet)Book.ActiveSheet; MSOffice.Worksheets sheets = (MSOffice.Worksheets)Book.Sheets; App.Visible = false; App.DisplayAlerts = false; object obj = App.Caller[1]; obj = App.Charts; obj = App.ClipboardFormats; obj = App.CutCopyMode; obj = App.StatusBar; obj = App.Workbooks[1]; obj = Book.ActiveChart; obj = Sheet.PageSetup.FirstPage.CenterFooter.Picture.Application; obj = Sheet.Range["", ""].Font.Application; obj = Sheet.Range["", ""].Borders[MSOffice.XlBordersIndex.xlDiagonalUp].Application; obj = App.Windows[""].SheetViews[""]; obj = App.Charts; obj = Sheet.Names; obj = Sheet.Names.Item(1); Sheet.Copy(); MSOffice.Areas a = null; obj = a.Application; // MSOffice.Hyperlink App.Workbooks.Open(""); // _xlsApp.Selection App.Windows[1].Zoom = 150; //xlsSheet.Rows[1, 1]; MSOffice.Range rng = Sheet.Range[1, 2]; MSOffice.Shape shape = Sheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 0, 48, 72, 60); shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse; shape.Shadow.Obscured = Microsoft.Office.Core.MsoTriState.msoCTrue; shape.Shadow.Type = Microsoft.Office.Core.MsoShadowType.msoShadow18; shape.Fill.UserPicture(@"r:\test.jpg"); //xlsSheet.Shapes.AddOLEObject //shape.GroupItems.Item //shape.Glow.Color Sheet.get_Range(""); App.Windows[1].Height = 100; textBox1.Text = App.Version; App.Quit(); }
private void CopyWorksheet() { //<Snippet12> Excel.Worksheet worksheet1 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[1]); Excel.Worksheet worksheet3 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[3]); worksheet1.Copy(worksheet3); //</Snippet12> }
/// <summary> /// 拷贝一个工作表 /// </summary> /// <param name="sourceSheet"></param> public void CopySheetToEnd(Excel.Worksheet sourceSheet, string newSheetName) { int sheetCount = _wb.Sheets.Count; Excel.Worksheet targetSheet = _wb.Sheets[sheetCount] as Excel.Worksheet; sourceSheet.Copy(Type.Missing, targetSheet); Excel.Worksheet newSheet = _wb.Sheets[sheetCount + 1] as Excel.Worksheet; newSheet.Name = newSheetName; }
private void saveWorksheet(string iFilename, string iWorksheetName, Excel.XlFileFormat iFileFormat) { Excel.Worksheet aTempSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.get_Item(iWorksheetName) as Excel.Worksheet; Excel.Workbook aTempWorkbook = Globals.ThisAddIn.Application.Workbooks.Add(Type.Missing) as Excel.Workbook; aTempSheet.Copy(Type.Missing, aTempWorkbook.Worksheets.get_Item(1)); aTempWorkbook.SaveAs(iFilename, iFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); aTempWorkbook.Close(false, Type.Missing, Type.Missing); }
public Excel.Application Copy(Excel.Application file) { Excel.Application copy = new Excel.Application(); Excel.Workbook xlWb = file.ActiveWorkbook as Excel.Workbook; Excel.Worksheet xlSht = xlWb.Sheets[1]; xlSht.Copy(Type.Missing, xlWb.Sheets[xlWb.Sheets.Count]); // copy xlWb.Sheets[xlWb.Sheets.Count].Name = file.ActiveWorkbook.Name; copy.Workbooks.Add(xlWb); return(copy); }
public void RefreshWR(String LRC) { WinRes WR = new WinRes(); WinResFile WRFile = WR.Files.Open(@"U:\Actuary\Planning\2019\LRC\new LRC 2019\" + LRC + @".pjx"); //WinResFile WRFile = WR.Files.Open(@"U:\Actuary\Planning\2019\LRC\review LRC 2019\" + LRC + @".pjx"); SelectedUltimate sel = null; //write array to worksheet Excel.Application myApp = Globals.ThisAddIn.Application; Excel.Workbook myWkbk = myApp.ActiveWorkbook; Excel.Worksheet templateWksht = myWkbk.Sheets["Template"]; int WkshtIndex = templateWksht.Index; MessageBox.Show(WkshtIndex.ToString()); templateWksht.Copy(templateWksht); Excel.Worksheet mySheet = myWkbk.Worksheets[WkshtIndex]; mySheet.Name = LRC; mySheet.Range["B1"].Value2 = LRC; Excel.Range myRng = mySheet.Range["B6"]; WRFile.SelectedUltimates.TryGet("Premium", out sel); for (Int16 intUWY = 0; intUWY < 22; intUWY++) { myRng.Offset[intUWY, 0].Value = sel[intUWY]; } sel = null; WRFile.SelectedUltimates.TryGet("Claim", out sel); for (Int16 intUWY = 0; intUWY < 22; intUWY++) { myRng.Offset[intUWY, 2].Value = sel[intUWY]; } sel = null; for (Int16 intUWY = 0; intUWY < 22; intUWY++) { myRng.Offset[intUWY, 1].Value = WRFile.AnalysedTriangles["Incurred"].LeadingDiagonal[intUWY]; } WR.Files.Remove(WRFile); WR = null; }
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string mergedFile, out string outputFile) { try { Excel.Workbook bookDest = excel.Workbooks.Add(Missing.Value); //create a new work sheet Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet; foreach (var item in MergedFiles.Keys) { if (!File.Exists(MergedFiles[item].OutputFullName)) { continue; } this.Logger.Message("Merge " + item); sourceBook = excel.Workbooks.Open(MergedFiles[item].OutputFullName); Excel.Worksheet sheet = sourceBook.Worksheets[1]; sheet.Name = MergedFiles[item].Description; sheet.Copy(Missing.Value, sheetDest); Excel.Worksheet copysheet = bookDest.Worksheets[MergedFiles[item].Description]; FunnelReportHelper.SaveTempWorkbook(sourceBook); } foreach (var item in bookDest.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (!MergedFiles.Values.Select(x => x.Description).Contains(sheet.Name)) { sheet.Delete(); } } outputFile = mergedFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xls"); bookDest.SaveAs(outputFile); bookDest.Close(); ZipHelper.ZipFile(outputFile, outputFile.Replace(".xls", ".zip"), 1); outputFile = outputFile.Replace(".xls", ".zip"); } catch (Exception ex) { this.Logger.Error(ex.Message + ex.StackTrace); throw; } return(true); }
public void btnCopySheet_Click(IRibbonControl e) { //open template sheet Excel.Workbook book = Utilities.ObjectModel.OpenWorkbook(@"C:\Users\grins\Documents\Custom Office Templates\TestTemplate.xlsx", ThisAddIn.MyApp, false); Excel.Worksheet sheet = book.Worksheets["ABC"]; Excel.Worksheet copyTo = book.Worksheets["DEF"]; Excel.Worksheet copyTwo = ThisAddIn.MyApp.Worksheets["Sheet1"]; sheet.Copy(copyTwo); book.Save(); book.Close(); }
/// <summary> /// テンプレートから新しいシートを作成する /// </summary> /// <param name="sheetName"></param> /// <param name="templateSheet"></param> /// <returns></returns> public Excel.Worksheet CreateSheet(string sheetName, Excel.Worksheet templateSheet) { templateSheet.Copy(Before: templateSheet); Excel.Worksheet sheet = this._wk.Sheets[templateSheet.Name + " (2)"]; sheet.Name = sheetName; if (sheet.Visible != Excel.XlSheetVisibility.xlSheetVisible) { sheet.Visible = Excel.XlSheetVisibility.xlSheetVisible; } return(sheet); }
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string mergedFile, out string outputFile) { Excel.Workbook bookDest = null; try { bookDest = excel.Workbooks.Add(Missing.Value); //create a new work sheet Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet; if (MergedFiles.Count > 0) { this.Logger.Message("Merge " + MergedFiles.First().Key); sourceBook = excel.Workbooks.Open(MergedFiles.First().Value.OutputFullName); Excel.Worksheet sheet = sourceBook.Worksheets[1]; sheet.Name = "Summary"; sheet.Copy(Missing.Value, sheetDest); Excel.Worksheet copysheet = bookDest.Worksheets["Summary"]; this.FormatSummary(copysheet); FunnelReportHelper.CloseWorkingWorkbook(sourceBook); } foreach (var item in bookDest.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (sheet.Name != "Summary") { sheet.Delete(); } } //outputFile = "ChinaDash_" + DateTime.Now.ToString("yyyMMdd") + ".xls"; outputFile = mergedFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyMMdd") + ".xls"); if (File.Exists(outputFile)) { File.Delete(outputFile); } bookDest.SaveAs(outputFile); bookDest.Close(); } catch (Exception ex) { throw; } return(true); }
/// <summary> /// Производит запрос параметров /// Вывод в Excel /// отчета "Сверка остатков" /// </summary> public static void LvOrclStock_StockBulkMismatch_Report() { //1.Check if process is already started string ErrMsg = null; if (isStockBulk_LvOrclLotMismatch_Running) { MessageBox.Show("Обработка уже идет!"); return; } isStockBulk_LvOrclLotMismatch_Running = true; Cursor.Current = Cursors.WaitCursor; frmFileParameter form = new frmFileParameter(); form.ShowDialog(); if (fileName == null) { isStockBulk_LvOrclLotMismatch_Running = false; return; } //Чтение разбор файла, загрузка данных в базу LvOrclStock_StockBulkMismatch_ParseFile(); //Вставка в докмуент листа Excel из ресурсов Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; string tmpFile = Path.GetTempFileName(); File.WriteAllBytes(tmpFile, Properties.Resources.LvOrclStock); Excel.Workbook wbT = Globals.ThisAddIn.Application.Workbooks.Add(tmpFile); Excel.Worksheet wsT = wbT.Worksheets["Сверка остатков"]; wsT.Copy(After: wb.Worksheets[wb.Worksheets.Count]); Excel.Worksheet ws = wb.Worksheets[wb.Worksheets.Count]; Clipboard.Clear(); wbT.Close(); File.Delete(tmpFile); //Получаем данные для отчета DataSet ds = null; ds = LvOrclStock_StockBulkMismatch_GetData(); //Грузим их в Excel LvOrclStock_StockBulkMismatch_FillExcel(ws, ds); Cursor.Current = Cursors.Default; isStockBulk_LvOrclLotMismatch_Running = false; Clipboard.Clear(); }
private void ThisAddIn_Startup(object sender, System.EventArgs e) { Excel.Workbook newWorkbook = this.Application.Workbooks.Add(); Excel.Worksheet worksheet = newWorkbook.ActiveSheet; Excel.Range cells = worksheet.Cells; cells.set_Item(1, 1, "Some Text"); Excel.Worksheet newWorksheet; newWorksheet = (Excel.Worksheet)newWorkbook.Worksheets.Add(); worksheet.Copy(newWorksheet); }
public bool MonthlyLog() { if (!File.Exists(file)) { MessageBox.Show("Database can not be found"); return(false); } if (File.Exists(file)) { Excel.Application xlApp = new Excel.Application(); var date = DateTime.Now; var datex = $"{date.Day}_{date.Month}_{ date.Year}_{date.Minute}_{date.Hour}"; var newbook = $@"C:\Users\kyle.marshall\Documents\Projects\Inventory\Monthly_Log_{datex}.xlsx"; Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Excel.Workbook xlWorkBook = xlWorkBooks.Open(file); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4); object misValue = System.Reflection.Missing.Value; xlWorkSheet.Copy(misValue, misValue); var x = Task.Run(async delegate { await Task.Delay(5000); }); x.Wait(); xlApp.Workbooks[1].SaveAs(Filename: newbook); var t = Task.Run(async delegate { await Task.Delay(5000); }); t.Wait(); foreach (Excel.Workbook book in xlWorkBooks) { foreach (Excel.Worksheet sheet in book.Sheets) { Marshal.ReleaseComObject(sheet); } book.Close(false); Marshal.ReleaseComObject(book); } xlWorkBooks.Close(); Marshal.ReleaseComObject(xlWorkBooks); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); GC.Collect(); return(true); } return(false); }
static void Main(string[] args) { Excel.Application app = new Excel.Application(); Excel.Worksheet newSht; app.Visible = true; newSht = app.Workbooks.Add().ActiveSheet; Excel.Workbook wbk = app.Workbooks.Open(@"C:\Users\Administrator\Desktop\测试.xlsx"); Excel.Worksheet sht = wbk.Sheets["Sheet1"]; //sht.Range["a1:h10"].Copy(newSht.Range["a1:h1"]); sht.Copy(newSht); //app.Workbooks.Add(); //newSht = app.ActiveSheet; //newSht.PasteSpecial(); }
static void Main(string[] args) { Excel.Application exApp = new Excel.Application(); Excel.Workbook wb1 = exApp.Workbooks.Open(@"C:\wb1.xls"); Excel.Workbook wb2 = exApp.Workbooks.Open(@"C:\wb2.xls"); Excel.Worksheet worksheet1 = wb1.Worksheets[1]; Excel.Worksheet worksheet2 = wb2.Worksheets[1]; worksheet1.Copy(worksheet2); wb2.SaveAs(@"C:\wb3.xls"); wb1.Close(false); wb2.Close(false); exApp.Quit(); }
private void MakeNewPage() { _pageNumber++; _countPerPage = 1; _costPerPage = 0; _pageHeight = 0; _ws3.Copy(Type.Missing, _ws3); Worksheet copySheet = _wb.Sheets.get_Item(_ws3.Index + 1); copySheet.Name = $"стр.{_pageNumber + 2}"; _currentWS = _ws3; _currentWS.Range["DD1"].Value = $"Форма 0504144 с. {_pageNumber+1}"; _ws3 = copySheet; _currentWS.Select(); }
public void CopyWorksheet(int SourceWorksheetIndex, int DesWorksheetIndex) { try { Excel.Worksheet sheetSource = (Excel.Worksheet)excelWorkBook.Worksheets[SourceWorksheetIndex]; sheetSource.Select(Missing.Value); Excel.Worksheet sheetDest = (Excel.Worksheet)excelWorkBook.Worksheets[DesWorksheetIndex]; sheetSource.Copy(Missing.Value, sheetDest); } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } }
private void GenerateObjRepoCsvFile(string csvFilePath, string sheetName) { Excel.Worksheet EW = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Workbook newWorkbook = Globals.ThisAddIn.Application.Workbooks.Add(); EW.Copy(newWorkbook.Sheets[1]); string newWbSheetName = sheetName; if (sheetName == "Sheet1") { newWbSheetName = sheetName + " (2)"; } Globals.ThisAddIn.Application.DisplayAlerts = false; //to overwrite existing file without promopting user newWorkbook.Worksheets[newWbSheetName].SaveAs(csvFilePath, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); newWorkbook.Close(true); }
private static void CreateWS(Excel.Workbook wb, List <InvoiceDPD> Invoices) { string tmpFile = Path.GetTempFileName(); File.WriteAllBytes(tmpFile, Properties.Resources.DPD); Excel.Workbook wbT = Globals.ThisAddIn.Application.Workbooks.Add(tmpFile); Excel.Worksheet wsT = wbT.Worksheets["Отчет Филуэт"]; int totalSheets = wb.Worksheets.Count; wsT.Copy(After: wb.Worksheets[totalSheets]); Excel.Worksheet ws = wb.Worksheets[wsT.Name]; wbT.Close(); File.Delete(tmpFile); List <InvoiceDPD> _invoices = new List <InvoiceDPD>(); _invoices = Invoices.OrderBy(i => i.Branch).ThenBy(i => i.OrderDate).ToList(); int row = 2; Excel.Range rng = ws.Rows[row].EntireRow; foreach (InvoiceDPD inv in _invoices) { rng = ws.Rows[row].EntireRow; rng.Copy(ws.Rows[row + 1]); //rng.Insert(Excel.XlInsertShiftDirection.xlShiftDown,true); ws.Cells[row, 1].Value2 = inv.Branch; ws.Cells[row, 2].Value2 = inv.InvoiceNo; ws.Cells[row, 3].Value2 = inv.OrderNo; ws.Cells[row, 4].Value2 = inv.OrderDate; ws.Cells[row, 5].Value2 = inv.BoxQty; ws.Cells[row, 6].Value2 = inv.Weight; ws.Cells[row, 7].Value2 = inv.DeliveryCost; ws.Cells[row, 8].Value2 = inv.DeliveryCostVAT; ws.Cells[row, 9].Value2 = inv.PickCost; ws.Cells[row, 10].Value2 = inv.PickCostVAT; ws.Cells[row, 11].Value2 = inv.DeliveryCost + inv.PickCost; ws.Cells[row, 12].Value2 = inv.DeliveryCostVAT + inv.PickCostVAT; row++; } rng = ws.Rows[row].EntireRow; rng.Delete(); }
public void ExportWorksheetAsExcel(Excel.Worksheet wsTarget, string targetFolderPath , string targetWsName, string docProperty = "INTERNAL") { var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); //var app = Globals.ThisWorkbook.Application; app.DisplayAlerts = false; wsTarget.Copy(); var currentWb = app.ActiveWorkbook; currentWb.BuiltinDocumentProperties("Comments").Value = "INTERNAL"; currentWb.SaveAs($"{targetFolderPath}\\{targetWsName}.xlsx"); currentWb.Close(); app.DisplayAlerts = true; }
public void ExportWorksheetAsPDF(Excel.Worksheet wsTarget, string targetFolderPath , string outputFileName) { //var app = Globals.ThisWorkbook.Application; var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); app.DisplayAlerts = false; wsTarget.Copy(); var currentWb = app.ActiveWorkbook; currentWb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF , $"{targetFolderPath}\\{outputFileName}"); currentWb.Close(); app.DisplayAlerts = true; }
public static void PostImport() { if (isPostImport_Running) { MessageBox.Show("Обработка уже работает!"); return; } isPostImport_Running = true; Cursor.Current = Cursors.WaitCursor; PostDatePick.DateStart = DateTime.Today.AddDays(-7); PostDatePick.DateEnd = DateTime.Today; PostDatePickForm form = new PostDatePickForm(); form.ShowDialog(); if (PostDatePick == null) { return; } Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; string tmpFile = Path.GetTempFileName(); File.WriteAllBytes(tmpFile, Properties.Resources.Post); Excel.Workbook wbT = Globals.ThisAddIn.Application.Workbooks.Add(tmpFile); Excel.Worksheet wsT = wbT.Worksheets["Отчет Почта"]; wsT.Copy(After: wb.Worksheets[wb.Worksheets.Count]); ws = wb.Worksheets[wb.Worksheets.Count]; Clipboard.Clear(); wbT.Close(); File.Delete(tmpFile); ThreadStart bts = new ThreadStart(StartImport); Thread bt = new Thread(bts); bt.Start(); formProgress.ShowDialog(); Cursor.Current = Cursors.Default; isPostImport_Running = false; Clipboard.Clear(); }
private unsafe void CreateExcelItem() { // Создаем документ с 16 страницами excelapp = new Excel.Application(); //excelapp.Visible=true; excelapp.SheetsInNewWorkbook=1; Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing); String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" }; excelsheets = excelappworkbook.Worksheets; // определяем имена страницам и переходим на страницу excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1); excelworksheet.Name = SheetsName[0]; excelworksheet.Activate(); excelworksheet.Application.ActiveWindow.SplitColumn = 3; excelworksheet.Application.ActiveWindow.SplitRow = 2; excelworksheet.Application.ActiveWindow.FreezePanes = true; // заполнение Index (0.1.2.3...) excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = 0; excelcells = excelworksheet.get_Range("B4", Type.Missing); excelcells.Value2 = 1; excelcells = excelworksheet.get_Range("B3", "B4"); Excel.Range dest = excelworksheet.get_Range("B3", "B514"); excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault); // сворачиваем для увеличения скорости excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized; excelworksheet.Application.Visible = false; // оцентровываем первую строку excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing]; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; // зажирняем и оцентровываем вторую строку excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing]; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; // устанавливаем размер колонок excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing]; excelcells.ColumnWidth = 5; excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing]; excelcells.ColumnWidth = 5; excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing]; excelcells.ColumnWidth = 30; for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing]; excelcells.ColumnWidth = MyItemColumns[j].Width; } // заполняем первую строку границами как называется не помню excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.Value2 = "Char[30]"; excelcells.Activate(); for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = excelapp.ActiveCell.get_Offset(0, 1); excelcells.Value2 = MyItemColumns[j].ColSize; excelcells.Activate(); } // заполняем вторую строку названиями excelcells = excelworksheet.get_Range("A2", Type.Missing); excelcells.Value2 = "Type"; excelcells = excelworksheet.get_Range("B2", Type.Missing); excelcells.Value2 = "Index"; excelcells = excelworksheet.get_Range("C2", Type.Missing); excelcells.Value2 = "Item Name"; excelcells.Activate(); for (int j = 0; j < MyItemColumns.Length; j++) { excelcells = excelapp.ActiveCell.get_Offset(0, 1); excelcells.Value2 = MyItemColumns[j].Name; excelcells.Activate(); } // обнуляем все ячейки кроме названия excelcells = excelworksheet.get_Range("D3", "AR514"); excelcells.Value2 = 0; // number format 12 232 232 для zen excelcells = excelworksheet.get_Range("AB3", "AB514"); excelcells.NumberFormat = "# ##0"; // копируем листы for (int i = 0; i < 15; i++) { excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1); excelworksheet.Copy(Type.Missing, excelworksheet); excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2); excelworksheet.Name = SheetsName[i + 1]; } // заполняем ячейки for (int i = 0; i < 16; i++) { // выделяем нужный лист excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1); excelworksheet.Activate(); excelcells = excelworksheet.get_Range("A3", "A514"); excelcells.Value2 = i; progressBar3.Value = i; // поехали по строкам for (int j = 0; j < 512; j++) { progressBar2.Value = j; if (Items[i,j].ItemName[0] != '\0') { excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3]; excelcells.Value2 = Items[i, j].ItemName; excelcells.Select(); } fixed (Int64* buff = Items[i, j].Numbers) { for (int k = 0; k < MyItemColumns.Length; k++) { if (buff != null && *(buff + k) != 0.0f) { excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4]; excelcells.Value2 = *(buff + k); } } } } } // показываем готовый файл excelapp.Visible = true; progressBar2.Value = 0; progressBar3.Value = 0; MessageBox.Show("All Done!"); }
/// <summary> /// 将二维数组数据写入Excel文件(自动分页,并指定要合并的列索引) /// </summary> /// <param name="arr">二维数组</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> /// <param name="mergeColumnIndex">数组的二维索引,相当于DataTable的列索引,索引从0开始</param> public void ArrayToExcel(string[,] arr, int rows, int top, int left, int mergeColumnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } //将二维数组数据写入Excel for (int i = sheetCount; i >= 1; i--) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将二维数组中的数据写入WorkSheet for (int j = 0; j < endRow - startRow; j++) { for (int k = 0; k < colCount; k++) { workSheet.Cells[top + j, left + k] = arr[startRow + j, k]; } } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = arr[startRow + j, k]; } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; //合并相同行 this.MergeRows(workSheet, left + mergeColumnIndex, top, rows); } }
/// <summary> /// 将DataTable数据写入Excel文件(自动分页,并指定要合并的列索引) /// </summary> /// <param name="dt">DataTable</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> /// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引,从0开始</param> public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex) { int rowCount = dt.Rows.Count; //源DataTable行数 int colCount = dt.Columns.Count; //源DataTable列数 sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 // StringBuilder sb; //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将dt中的数据写入WorkSheet // for(int j=0;j<endRow-startRow;j++) // { // for(int k=0;k<colCount;k++) // { // workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString(); // } // } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = dt.Rows[startRow + j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; //合并相同行 this.MergeRows(workSheet, left + mergeColumnIndex, top, rows); } }
/// <summary> /// 将DataTable数据写入Excel文件(自动分页) /// </summary> /// <param name="dt">DataTable</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> public void DataTableToExcel(DataTable dt, int rows, int top, int left) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 // StringBuilder sb; //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将dt中的数据写入WorkSheet // for(int j=0;j<endRow-startRow;j++) // { // for(int k=0;k<colCount;k++) // { // workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString(); // } // } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = dt.Rows[startRow + j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; #region 利用Windwo粘贴板批量拷贝数据(在Web下面行不通) /*sb = new StringBuilder(); for(int j=0;j<endRow-startRow;j++) { for(int k=0;k<colCount;k++) { sb.Append( dt.Rows[startRow + j][k].ToString() ); sb.Append("\t"); } sb.Append("\n"); } System.Windows.Forms.Clipboard.SetDataObject(sb.ToString()); range = (Excel.Range)workSheet.Cells[top,left]; workSheet.Paste(range,false);*/ #endregion } }