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
}
}
