public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath)
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Your file is saved" + SaveFilePath);
}
public Export(string embedded)
{
string tmp = String.Empty;
try
{
//получаем шаблон из прикладных ресурсов
Stream template = GetResourceFileStream(embedded);
//создаем временный файл
tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
//сохраняем шаблон во временный файл
using (var fileStream = File.Create(tmp))
{
template.CopyTo(fileStream);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Создаем приложение и открываем в нём временный файл
objApp = new Excel.Application();
objBook = objApp.Workbooks.Open(tmp);
objBook.Activate();
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public ExportData(System.Data.DataTable dt, string location)
{
//instantiate excel objects (application, workbook, worksheets)
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
//run through datatable and assign cells to values of datatable
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(location);
}
public getData readData()
{
getData.dateT.Clear();
getData.prj.Clear();
getData.descr.Clear();
getData.taskTime.Clear();
notes.Clear();
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
//excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1
// WorkSheetExcel.Cells[i, 1].Text.ToString() != ""
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString());
getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString());
getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString());
getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString());
}
WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя
ExcelApp.Quit();
GC.Collect();
getData.dateT.Reverse();
getData.prj.Reverse();
getData.descr.Reverse();
getData.taskTime.Reverse();
return getData;
}
public static void exportDataGridView(DataGridView dv, String fileName)
{
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
app.UserControl = false;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
worksheet.Name = "Sheet1";
Boolean bl = dv.AllowUserToAddRows;
dv.AllowUserToAddRows = false;
for (int i = 0; i < dv.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dv.Columns[i].HeaderText;
}
for (int i = 0; i < dv.Rows.Count; i++)
{
for (int j = 0; j < dv.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dv.Rows[i].Cells[j].Value;
}
}
dv.AllowUserToAddRows = bl;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
workbook.Save();
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.Quit();
app = null;
}
public DocSoc()
{
string reportsFile = System.Configuration.ConfigurationManager.AppSettings["ReportsFile"];
string macrosFile = System.Configuration.ConfigurationManager.AppSettings["MacrosFile"];
System.IO.FileInfo macrosFileInfo = new System.IO.FileInfo(macrosFile);
Hashtable reportsHash = readReportsXML(reportsFile,macrosFileInfo.Name);
string[] reportsSelected = getSelectedReports(reportsHash);
Excel.Application xlApp;
Excel.Workbook macroBook = null;
xlApp = new Excel.Application();
Console.Write("Excel opened");
try
{
macroBook = xlApp.Workbooks.Open(macrosFileInfo.FullName);
Console.WriteLine("MacroFile opened: " + macrosFileInfo.FullName);
this.processReports(xlApp, reportsHash, reportsSelected);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
System.Windows.Forms.MessageBox.Show("DocToSoc failed while processing reports.", "DocToSoc Failed", System.Windows.Forms.MessageBoxButtons.OK);
}
finally
{
macroBook.Close();
Console.WriteLine("MacroWorkBook closed: " + macrosFile);
xlApp.Quit();
Console.WriteLine("Excel Closed");
}
}
static void ExportToExcel(List<Car> carsInStock)
{
// Load up Excel, then make a new empty workbook.
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// Establish column headings in cells.
workSheet.Cells[1, "A"] = "Make";
workSheet.Cells[1, "B"] = "Color";
workSheet.Cells[1, "C"] = "Pet Name";
// Now, map all data in List<Car> to the cells of the spread sheet.
int row = 1;
foreach (Car c in carsInStock)
{
row++;
workSheet.Cells[row, "A"] = c.Make;
workSheet.Cells[row, "B"] = c.Color;
workSheet.Cells[row, "C"] = c.PetName;
}
// Give our table data a nice look and feel.
workSheet.Range["A1"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
// Save the file, quit Excel and display message to user.
workSheet.SaveAs(string.Format(@"{0}\Inventory.xlsx", Environment.CurrentDirectory));
excelApp.Quit();
MessageBox.Show("The Inventory.xslx file has been saved to your app folder", "Export complete!");
}
public static new Boolean Convert(String inputFile, String outputFile) {
Microsoft.Office.Interop.Excel.Application app;
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
try {
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
workbooks = app.Workbooks;
workbook = workbooks.Open(inputFile, true, true, oMissing, oMissing, oMissing, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
// Try and avoid xls files raising a dialog
tmpFile = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls" + (workbook.HasVBProject ? "m" : "x");
workbook.SaveAs(tmpFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
workbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
outputFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, Type.Missing, false, Type.Missing, Type.Missing, false, Type.Missing);
workbooks.Close();
app.Quit();
return true;
} catch (Exception e) {
Console.WriteLine(e.Message);
return false;
} finally {
if (tmpFile != null) {
System.IO.File.Delete(tmpFile);
}
app = null;
}
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
public bool exportTable()
{
try
{
//Подготовка
excel= new InteropExcel.ApplicationClass ();
if (excel==null) return false;
excel.Visible=false;
InteropExcel.Workbook workbook=excel.Workbooks.Add();
if(workbook==null) return false;
InteropExcel.Worksheet sheet=(InteropExcel.worksheet) workbook.worksheets[1]
sheet.Name="Таблица1"
//Попълване на таблицата
//Запаметяване и затваряне
workbook.SaveCopyAs(getPath());
excel.DisplayAlerts=false; //изключване на всички съобщения на Excel
workbook.Close();
excel.Quit ();
return true;
}catch{
}
return false;
}
private void btnGenerate_Click(object sender, EventArgs e)
{
//string filename = "Azure_Pass_Account" +/* datePicker.ToString() + */inputApplicant.Text;
Excel.Application excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook wb = excel.Workbooks.Add(1);
Excel.Worksheet sh = wb.Sheets.Add();
datePicker.Format = DateTimePickerFormat.Custom;
datePicker.CustomFormat = "yyMMdd";
sh.Name = "Azure Account";
sh.Cells[1, "A"].Value2 = "Index";
sh.Cells[1, "B"].Value2 = "Account";
sh.Cells[1, "C"].Value2 = "Password";
sh.Cells[1, "D"].Value2 = "Applicant";
for(int index = 1; index <= Int32.Parse( inputAmount.Text); index ++)
{
sh.Cells[index + 1 ,"A"].Value2 = index;
sh.Cells[index + 1, "B"].Value2 = "MS" + datePicker.Text + index.ToString("000") + "@outlook.com";
sh.Cells[index + 1, "C"].Value2 = "MS" + datePicker.Text;
sh.Cells[index + 1, "D"].Value2 = inputApplicant.Text;
}
string filename = "Azure_Pass_Account_" + datePicker.Text + "_" + inputApplicant.Text + "_Auto_Generate";
SaveFileDialog mySaveFileDialog = new SaveFileDialog();
mySaveFileDialog.FileName = filename;
mySaveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
mySaveFileDialog.ShowDialog();
excel.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(path);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
BindingList<Dompet> DompetList = new BindingList<Dompet>();
for (int index = 2; index <= lastrow; index++)
{
System.Array MyValues =
(System.Array)MySheet.get_Range
("A" + index.ToString(),"F" + index.ToString()).Cells.Value;
DompetList.Add(new Dompet {
JmlPemesanan = MyValues.GetValue(1,1).ToString(),
JmlPekerja = MyValues.GetValue(1,2).ToString(),
Peralatan = MyValues.GetValue(1,3).ToString(),
JenisKulit = MyValues.GetValue(1,4).ToString(),
ModelDompet = MyValues.GetValue(1,5).ToString(),
Prediksi = MyValues.GetValue(1,6).ToString()
});
}
dataGridView1.DataSource = (BindingList<Dompet>)DompetList;
dataGridView1.AutoResizeColumns();
}
/// <summary/>
public PluginManager( MSExcel.Application application )
{
myApplication = application;
myPluginContext = new PluginContext( myApplication );
myPlugins = new List<AbstractPlugin>();
}
public LoadandMatchMetricsNames(String dashboardFile)
{
try
{
app = new Excel.Application();
app.Visible = true;
dashboard = app.Workbooks.Open(dashboardFile,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
/* for (int i = 0; i < metricsFiles.Length; i++)
{
metrics = app.Workbooks.Open(metricsFiles[i],
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//this is where we read all the data into some sort of array
//ExcelScanInternal(metrics);
metrics.Close(false, metricsFiles[i], null);
} */
//cleanup
//dashboard.Close(false, dashboardFile, null);
}
catch (Exception ex) { Console.WriteLine(ex.Message); }
}
public excel()
{
var excel = new Excel.Application();
//OR
/*
Type ExcelType = Type.GetTypeFromProgID("Excel.Application");
dynamic excel = Activator.CreateInstance(ExcelType);
*/
var workbook = excel.Workbooks.Add();
var sheet = excel.ActiveSheet;
excel.Visible = true;
excel.Cells[1, 1] = "Hi from Me";
excel.Columns[1].AutoFit();
excel.Cells[2,1]=10;
excel.Cells[3, 1] = 10;
excel.Cells[4, 1] = 20;
excel.Cells[5, 1] = 30;
excel.Cells[6, 1] = 40;
excel.Cells[7, 1] = 50;
excel.Cells[8, 1] = 60;
var chart = workbook.Charts.Add(After:sheet);
chart.ChartWizard(Source:sheet.Range("A2","A7"));
}
//C# 4.0 미만에서 COM컴포넌트 사용하는 코드
public static void OldWay(string[,] data, string savePath)
{
Excel.Application excelApp = new Excel.Application();
//의미없는 매개변수 입력
excelApp.Workbooks.Add(Type.Missing);
//형변환
Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
for (int i = 0; i < data.GetLength(0); i++)
{
//형변환
((Excel.Range)workSheet.Cells[i + 1, 1]).Value2 = data[i, 0];
((Excel.Range)workSheet.Cells[i + 1, 2]).Value2 = data[i, 1];
}
//의미없는 매개변수 입력
workSheet.SaveAs(savePath + "\\addressbook-old.xlsx",
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
excelApp.Quit();
}
// Create a method that is called when the testExcel button is pushed
private void CreateExcel(object sender, EventArgs e)
{
Excel.Application excel = new Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
Excel.Workbook wb = excel.Workbooks.Add();
Excel.Worksheet sh = wb.Sheets.Add();
sh.Name = "JPMorgan";
sh.Cells[1, "A"].Value = "Vendor Name";
sh.Cells[1, "B"].Value = "City";
sh.Cells[1, "C"].Value = "Zip Code";
sh.Cells[1, "D"].Value = "Tax ID";
sh.Cells[1, "E"].Value = "Phone";
sh.Cells[1, "F"].Value = "Email";
sh.Cells[1, "G"].Value = "Currency ID";
sh.Cells[2, "A"].Value = this.pmTrxForm.PmVendorMaintenance.VendorName.Value;
sh.Cells[2, "B"].Value = this.pmTrxForm.PmVendorMaintenance.City.Value;
sh.Cells[2, "C"].Value = this.pmTrxForm.PmVendorMaintenance.ZipCode.Value;
sh.Cells[2, "D"].Value = this.pmTrxForm.PmVendorMaintenanceAdditionalInformation.TaxIdNumber.Value;
sh.Cells[2, "E"].Value = this.pmTrxForm.PmVendorMaintenance.PhoneNumber1.Value;
sh.Cells[2, "F"].Value = this.pmTrxForm.PmVendorMaintenance.Comment1.Value;
sh.Cells[2, "G"].Value = this.pmTrxForm.PmVendorMaintenanceAdditionalInformation.CurrencyId.Value;
wb.SaveAs(@"D:\Excel\testdata.xlsx");
wb.Close(true);
excel.Quit();
}
public void read(ref List<string> grpname , ref List<string> path)
{
createfile objcf = new createfile();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
object missing = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(@"d:\database\Group.xlsx", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
int i = 0;
string str,str1;
for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
str = (string)(range.Cells[rCnt, 1] as Excel.Range).Value2;
str1 = (string)(range.Cells[rCnt, 2] as Excel.Range).Value2;
grpname.Add(str);
path.Add(str1);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
objcf.releaseObject(xlWorkSheet);
objcf.releaseObject(xlWorkBook);
objcf.releaseObject(xlApp);
}
// 打开Excel文件
public void OpenExcelFile()
{
OpenFileDialog opd = new OpenFileDialog();
if (opd.ShowDialog() == DialogResult.OK)
excelOpenFileName = opd.FileName;
textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName);
if (excelOpenFileName !="")
{
try
{
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex];
excelApplication.Visible = false;
rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数
stateOfSave = false;
}
catch (Exception e)
{
closeApplication();
MessageBox.Show("(1)没有安装Excel；(2)或没有安装.NET 可编程性支持；\n详细信息："
+ e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("未选择文件！");
closeApplication();
}
}
/// <summary>
/// Создает общий шаблон для таблицы
/// </summary>
/// <param name="Parent">Родительская форма</param>
/// <param name="Table">Исходная таблица</param>
public static void CreateTemplate(BankLab Parent, DataGridView Table)
{
Excel.Application ExcelApp;
ExcelApp = new Excel.Application();
ResFile Maket = new ResFile(bl_res_doc.Resource1.Maket, AppDomain.CurrentDomain.BaseDirectory + "~tmp.xlsx");
Maket.CreateFile();
Parent.CurrentProgressBar.SetProgressBarValue(35);
ExcelApp.Workbooks.Open(Maket.FileName);
Parent.CurrentProgressBar.SetProgressBarValue(50);
Table.Columns.Add("Year", ExcelApp.Cells[1, 1].Value);
Table.ColumnCount = 19;
Parent.CurrentProgressBar.SetProgressBarValue(60);
for (int i = 1; i <= 18; i++) {
Table.Columns[i].Name = "X" + i.ToString();
Table.Columns[i].HeaderText = ExcelApp.Cells[1, i + 1].Value;
Table.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
}
Parent.CurrentProgressBar.SetProgressBarValue(90);
SetTablePropertyToCreateTamplate(Table);
Parent.CurrentSideBar.AddTables();
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
Maket.FreeFile();
Parent.CurrentMenu.ChangeMenuItemsEnable(true);
}
/// <summary>
/// Импортирует Excell документ в таблицу
/// </summary>
/// <param name="Parent">Родительская форма</param>
/// <param name="Table">Таблица назначения</param>
/// <returns></returns>
public static int ImportExcelFile(BankLab Parent, DataGridView Table)
{
String FileName = Parent.ShowOpenDialog("Файлы Excel (*.xls; *.xlsx)|*.xls;*.xlsx");
if (FileName == String.Empty) {
Parent.CurrentProgressBar.HideProgressBar();
return 0;
}
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Workbooks.Open(FileName);
Table.Visible = false;
Parent.CurrentProgressBar.SetProgressBarValue(10);
Table = Parent.CurrentDataTable.ReinitializeDataTable();
ExcelFunctions.CreateTemplate(Parent, Table);
for (int i = 0; i < ExcelApp.Sheets[1].UsedRange.Rows.Count - 1; i++) {
Table.Rows.Add();
for (int j = 1; j <= 18; j++) {
Table.Rows[i].Cells[j].Value = ExcelApp.Cells[i + 2, j + 1].Value;
}
}
Parent.CurrentProgressBar.SetProgressBarValue(90);
Parent.CurrentDataTable.FillTableIntervalColumnByFirstYear(Convert.ToInt32(ExcelApp.Cells[2, 1].value));
Table.Visible = true;
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
Parent.CurrentProgressBar.SetProgressBarValue(100);
return 1;
}
public Workbook(Excel.Workbook wb, Excel.Application app, Action dispose_callback)
{
_app = app;
_wb = wb;
_wb_name = wb.Name;
_dispose_callback = dispose_callback;
}
public void CallMacro(string file)
{
Excel.Application xlApp;
Workbook xlWorkBook;
Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(file);//, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
RunMacro(
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
// Application.Run "PERSONAL.XLSB!CleanDocket"
//Application.Run "PERSONAL.XLSB!Create_Upcoming_Docket"
//Sheets("Upcoming Hearings").Select
//Sheets("Upcoming Hearings").Move Before:=Sheets(1)
/*
*
*
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);*/
}
private void CopyToClipboardButton_Click(object sender, EventArgs e)
{
if (EmailResultTextBox.Text.Length == 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
_Workbook workbook = (_Workbook)(excelApp.Workbooks.Add(Type.Missing));
_Worksheet worksheet = (_Worksheet)workbook.ActiveSheet;
TicketRepository ticketRepo = new TicketRepository();
List<TicketResource> listOfTickets = ticketRepo.GetDistinctEmailAddressBetweenDates(StartingDatePicker.Value, EndingDatePicker.Value);
for (int i = 0; i < listOfTickets.Count; i++)
{
TicketResource ticket = listOfTickets[i];
int row = i + 1;
worksheet.Cells[row, "A"] = ticket.LastName;
worksheet.Cells[row, "B"] = ticket.FirstName;
worksheet.Cells[row, "C"] = ticket.Email;
}
worksheet.Columns["A:C"].AutoFit();
}
public ReportDataSet ReadDataSet(string file, int headerRowNumber)
{
ReportDataSet dataSet = new ReportDataSet();
try
{
excel = new Excel.Application();
Excel.Workbook tempBook = excel.Workbooks.Open(file);
foreach (var item in tempBook.Sheets)
{
Excel.Worksheet sheet = item as Excel.Worksheet;
ReportDataTable table = this.GetDataTable(sheet, headerRowNumber);
dataSet.Tables.Add(table.Name, table);
}
tempBook.Save();
tempBook.Close();
tempBook = null;
excel.Application.Quit();
}
catch (Exception ex)
{
excel.Application.Quit();
}
return dataSet;
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
SaveFileDialog sfd = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application myExcelFile = new Microsoft.Office.Interop.Excel.Application();
sfd.Title = "Save As";
sfd.Filter = "Microsoft Excel|*.xlsx";
sfd.DefaultExt = "xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
Workbook myWorkBook = myExcelFile.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet myWorkSheet = (Worksheet)myExcelFile.ActiveSheet;
// don't open excel file in windows during building
myExcelFile.Visible = false;
label1.Text = "Building the excel file. Please wait...";
// set the first row cells as column names according to the names of data grid view columns names
foreach (DataGridViewColumn dgvColumn in dataGridView.Columns)
{
// dataGridView columns is a zero-based array, while excel sheet is a 1-based array
// so, first row of excel sheet has index 1
// set columns of first row as titles of columns
myWorkSheet.Cells[1, dataGridView.Columns.IndexOf(dgvColumn) + 1] = dgvColumn.HeaderText;
}
// since, first row has titles that are set above, start from row-2 and fill each row of excel file.
for (int i = 2; i <= dataGridView.Rows.Count; i++)
{
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
myWorkSheet.Cells[i, j + 1] = dataGridView.Rows[i - 2].Cells[j].Value;
}
}
// set the font style of first row as Bold which has titles of each column
myWorkSheet.Rows[1].Font.Bold = true;
myWorkSheet.Rows[1].Font.Size = 12;
// after filling, save the file to the specified location
string savePath = sfd.FileName;
myWorkBook.SaveCopyAs(savePath);
}
label1.Text = "File saved successfully.";
if (MessageBox.Show("File saved successfully. Do you want to open it now?", "File Saved!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
myExcelFile.Visible = true;
}
}
catch(Exception exc)
{
label1.Text = exc.Message;
}
}
public void DTToExcel(DataTable dt, string filename)
{
object[,] t = DataTableTo2DTable(dt);
excel.Application eapp = new excel.Application();
excel.Workbook book = eapp.Workbooks.Add();
excel.Worksheet sheet = book.Worksheets[1];
sheet.Range["A1", sheet.Cells[t.GetLength(0), t.GetLength(1)]].Value = t;
if (t.GetLength(0) > 1)
{
for (int j = 0; j < t.GetLength(1); j++)
{
if (dt.Columns[j].DataType == typeof(DateTime))
{
sheet.Range[sheet.Cells[2, j + 1], sheet.Cells[t.GetLength(0), j + 1]].NumberFormat = "yyyy/m/d h:mm";
}
}
}
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// sheet.Cells[j + 1][i + 1] = dt.Rows[i][j];
// }
//}
book.SaveAs(filename);
eapp.Visible = true;
}
private void Init()
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
private void btnExport_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i <= dgSelectedParameters.RowCount - 1; i++)
{
for (int j = 0; j <= dgSelectedParameters.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgSelectedParameters[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("QPR2015_Parameters.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
MessageBox.Show("The Excel file has been saved, and can be found here: c:\\Users\\..\\Documents\\QPR2015_Parameters.xls", "File Saved");
}
private void button1_Click(object sender, EventArgs e)
{
try
{
MessageBox.Show("" + dateTimePicker1.Text);
string sql = null;
string data = null;
int i = 0;
int j = 0;
Exce.Application xlApp;
Exce.Workbook xlWorkBook;
Exce.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Exce.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1);
connection.Open();
sql = "SELECT or_no, or_date, ref_no, ref_date, d_date, c_name, net_amount, notes FROM main_sales WHERE or_date BETWEEN '" + dateTimePicker1.Text + "' AND '" + dateTimePicker2.Text + "'";
OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection);
DataSet ds = new DataSet();
dscmd.Fill(ds);
xlWorkSheet.Cells[1, 1] = "Order No";
xlWorkSheet.Cells[1, 2] = "Order Date";
xlWorkSheet.Cells[1, 3] = "Reference No";
xlWorkSheet.Cells[1, 4] = "Reference Date";
xlWorkSheet.Cells[1, 5] = "Delivery Date";
xlWorkSheet.Cells[1, 6] = "Customer Name";
xlWorkSheet.Cells[1, 7] = "Net Amount";
xlWorkSheet.Cells[1, 8] = "Notes";
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 2, j + 1] = data;
}
}
xlWorkBook.SaveAs("Sales Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. Sales Report.xls");
}
catch (Exception u)
{
MessageBox.Show("" + u);
}
finally
{
connection.Close();
}
}
private void создатьСметуToolStripMenuItem1_Click(object sender, EventArgs e)
{
try
{
var excelApp = new Excel.Application();
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook sheet = books.Open(mySheet);
Worksheet workSheet = (Worksheet)excelApp.ActiveSheet;
countRows = dataGridView1.SelectedRows.Count;
excelApp.Cells[1, 1] = comboBoxType.Text + " смета: Сумма трудоемкости работ, выполняемых ГАУ РМ «Госинформ», связанных с обслуживанием АИС «" + comboBoxAIS.Text + "»";
Excel.Range oRange;
oRange = workSheet.Range[workSheet.Cells[4, 1], workSheet.Cells[4, 9]];
oRange.Merge(Type.Missing);
oRange = workSheet.Range[workSheet.Cells[i + 5, 2], workSheet.Cells[i + 5, 6]];
oRange.Merge(Type.Missing);
(workSheet.Cells[i + 3, 2] as Excel.Range).Font.Name = "Times new roman";
(workSheet.Cells[i + 3, 2] as Excel.Range).Font.Bold = true;
(workSheet.Cells[i + 3, 2] as Excel.Range).Font.Size = 11;
(workSheet.Cells[i + 4, 2] as Excel.Range).Font.Name = "Times new roman";
(workSheet.Cells[i + 4, 2] as Excel.Range).Font.Bold = true;
(workSheet.Cells[i + 4, 2] as Excel.Range).Font.Size = 12;
excelApp.Cells[i + 4, 2] = "ИТОГО: сумма трудоемкости работ, выполняемых ГАУ РМ «Госинформ», связанных с обслуживанием АИС «" + comboBoxAIS.Text + "»";
(workSheet.Cells[i + 4, 8] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelApp.Cells[i + 4, 8] = itog;
(workSheet.Cells[i + 4, 9] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[i + 4, 9] as Excel.Range).Font.Name = "Times new roman";
excelApp.Cells[i + 4, 9] = "X";
int rowExcel = 4;
for (int j = 0; j < dataGridView2.Rows.Count; j++)
{
if (dataGridView2.Rows[j].Cells[0].Value.ToString() == "NewRazdel")
{
oRange = workSheet.Range[workSheet.Cells[rowExcel, 1], workSheet.Cells[rowExcel, 9]];
(workSheet.Cells[rowExcel, 1] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Bold = true;
(workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Size = 11;
(workSheet.Cells[rowExcel, 1] as Excel.Range).Font.Name = "Times new roman";
oRange.Merge(Type.Missing);
workSheet.Cells[rowExcel, 1] = dataGridView2.Rows[j].Cells[1].Value.ToString();
++rowExcel;
}
else
{
code = dataGridView2.Rows[j].Cells[0].Value.ToString();
string rabota = dataGridView2.Rows[j].Cells[1].Value.ToString();
string edIzmer = dataGridView2.Rows[j].Cells[2].Value.ToString();
string norma = dataGridView2.Rows[j].Cells[3].Value.ToString();
string godSredneStat = dataGridView2.Rows[j].Cells[4].Value.ToString();
string godNormaZatrat = dataGridView2.Rows[j].Cells[5].Value.ToString();
string kol = dataGridView2.Rows[j].Cells[6].Value.ToString();
string god = dataGridView2.Rows[j].Cells[7].Value.ToString();
string comment = dataGridView2.Rows[j].Cells[8].Value.ToString();
if (dataGridView2.Rows[j].Cells[9].Value != null)
{
naCoef = dataGridView2.Rows[j].Cells[9].Value.ToString();
}
(workSheet.Cells[rowExcel, "A"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "A"] = code;
(workSheet.Cells[rowExcel, "B"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "B"] = rabota;
(workSheet.Cells[rowExcel, "C"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "C"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "C"] = edIzmer;
(workSheet.Cells[rowExcel, "D"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "D"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "D"] = norma;
(workSheet.Cells[rowExcel, "E"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "E"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "E"] = godSredneStat;
(workSheet.Cells[rowExcel, "F"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "F"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "F"] = godNormaZatrat;
(workSheet.Cells[rowExcel, "G"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "G"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "G"] = kol;
(workSheet.Cells[rowExcel, "H"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "H"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "H"] = god;
(workSheet.Cells[rowExcel, "I"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "I"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "I"] = comment;
(workSheet.Cells[rowExcel, "J"] as Excel.Range).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
(workSheet.Cells[rowExcel, "J"] as Excel.Range).Font.Name = "Times new roman";
workSheet.Cells[rowExcel, "J"] = naCoef;
++rowExcel;
}
}
excelApp.Visible = true;
}
catch (Exception ex) { MessageBox.Show("Ошибка! " + Environment.NewLine + ex.ToString()); }
}
/// <summary>
/// Get last used column for a row
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="row"></param>
/// <returns></returns>
public int LastColumnForRow(string fileName, string sheetName, int row)
{
int lastColumn = -1;
if (File.Exists(fileName))
{
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(fileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
if (xlWorkSheet.Name == sheetName)
{
Excel.Range xlCells = null;
xlCells = xlWorkSheet.Cells;
Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
Excel.Range xlColumns = xlWorkSheet.Columns;
int count = xlColumns.Count;
Marshal.FinalReleaseComObject(xlColumns);
xlColumns = null;
Excel.Range xlLastRange = (Excel.Range)xlWorkSheet.Cells[row, count];
Excel.Range xlDirRange = xlLastRange.End[Excel.XlDirection.xlToLeft];
Marshal.FinalReleaseComObject(xlLastRange);
xlLastRange = null;
lastColumn = xlDirRange.Column;
Marshal.FinalReleaseComObject(xlDirRange);
xlDirRange = null;
Marshal.FinalReleaseComObject(workRange);
workRange = null;
Marshal.FinalReleaseComObject(xlCells);
xlCells = null;
break;
}
Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
Release(xlWorkSheets);
Release(xlWorkSheet);
Release(xlWorkBook);
Release(xlWorkBooks);
Release(xlApp);
return(lastColumn);
}
else
{
throw new Exception("'" + fileName + "' not found.");
}
}
private void btnxuatexcel_Click(object sender, EventArgs e)
{
moFile.ShowDialog();
string file = moFile.FileName;
if (string.IsNullOrEmpty(file))
{
return;
}
if (file == "openFileDialog1")
{
return;
}
else
{
img = moFile.FileName.ToString();
}
Console.WriteLine(img);
try
{
string saveExcelFile = img;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Lỗi không thể sử dụng được thư viện EXCEL");
return;
}
xlApp.Visible = false;
object misValue = System.Reflection.Missing.Value;
Workbook wb = xlApp.Workbooks.Add(misValue);
Worksheet ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
MessageBox.Show("Không thể tạo được WorkSheet");
return;
}
int row = 1;
string fontName = "Times New Roman";
int fontSizeTieuDe = 18;
int fontSizeTenTruong = 14;
int fontSizeNoiDung = 12;
//Xuất dòng tiêu đề của file
Range row1_TieuDe_ThongKeSanPham = ws.get_Range("A1", "E1");
row1_TieuDe_ThongKeSanPham.Merge();
row1_TieuDe_ThongKeSanPham.Font.Size = fontSizeTieuDe;
row1_TieuDe_ThongKeSanPham.Font.Name = fontName;
row1_TieuDe_ThongKeSanPham.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
row1_TieuDe_ThongKeSanPham.Value2 = "Thống kê hóa đơn";
//Tạo Ô Số Thứ Tự (STT)
Range row23_STT = ws.get_Range("A2");//Cột A dòng 2 và dòng 3
row23_STT.Font.Size = fontSizeTenTruong;
row23_STT.Font.Name = fontName;
row23_STT.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23_STT.Value2 = "STT";
//Tạo Ô Mã Sản phẩm :
Range row23_MaSP = ws.get_Range("B2");//Cột B dòng 2 và dòng 3
row23_MaSP.Font.Size = fontSizeTenTruong;
row23_MaSP.Font.Name = fontName;
row23_MaSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23_MaSP.Value2 = "Mã Hóa Đơn";
row23_MaSP.ColumnWidth = 20;
//Tạo Ô Tên Sản phẩm :
Range row23_TenSP = ws.get_Range("C2");//Cột C dòng 2 và dòng 3
row23_TenSP.Font.Size = fontSizeTenTruong;
row23_TenSP.Font.Name = fontName;
row23_TenSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23_TenSP.ColumnWidth = 20;
row23_TenSP.Value2 = "Ngày Lập";
//Tạo Ô Giá Nhập:
Range row3_GiaNhap = ws.get_Range("D2");//Ô D3
row3_GiaNhap.Font.Size = fontSizeTenTruong;
row3_GiaNhap.Font.Name = fontName;
row3_GiaNhap.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
row3_GiaNhap.Value2 = "Nhân Viên";
row3_GiaNhap.ColumnWidth = 20;
//Tạo Ô Giá Xuất:
Range row3_GiaXuat = ws.get_Range("E2");//Ô E3
row3_GiaXuat.Font.Size = fontSizeTenTruong;
row3_GiaXuat.Font.Name = fontName;
row3_GiaXuat.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
row3_GiaXuat.Value2 = "Tổng Tiền";
row3_GiaXuat.ColumnWidth = 20;
//Tô nền vàng các cột tiêu đề:
Range row23_CotTieuDe = ws.get_Range("A2", "E2");
//nền vàng
row23_CotTieuDe.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Yellow);
//in đậm
row23_CotTieuDe.Font.Bold = true;
//chữ đen
row23_CotTieuDe.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Black);
int stt = 0;
row = 2;
foreach (HoaDon hd in trangsuc.HoaDons)
{
stt++;
row++;
dynamic[] day = { stt, hd.MaHD.Trim(), Convert.ToDateTime(hd.TGLap), hd.UserName.Trim(), hd.TongTien };
Range rowData = ws.get_Range("A" + row, "E" + row);
rowData.Font.Size = fontSizeNoiDung;
rowData.Font.Name = fontName;
rowData.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowData.Value2 = day;
}
//Kẻ khung toàn bộ
BorderAround(ws.get_Range("A2", "E" + row));
//Lưu file excel xuống Ổ cứng
wb.SaveAs(saveExcelFile);
//đóng file để hoàn tất quá trình lưu trữ
wb.Close(true, misValue, misValue);
//thoát và thu hồi bộ nhớ cho COM
xlApp.Quit();
//Mở File excel sau khi Xuất thành công
System.Diagnostics.Process.Start(saveExcelFile);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
void ExportarExcel(string filename)
{
Excel._Application xlApp;
Excel._Workbook xlLibro;
Excel._Worksheet xlHoja;
Excel.Sheets xlHojas;
xlApp = new Excel.Application();
filename = Path.Combine(Directory.GetCurrentDirectory(), "Excel\\ShippingReportVinceStyle.xlsx");
xlLibro = xlApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlHojas = xlLibro.Sheets;
xlHoja = (Excel._Worksheet)xlHojas[1];
Excel.Range xlRange;
Cursor.Current = Cursors.WaitCursor;
xlHoja.Shapes.AddPicture(Path.Combine(Directory.GetCurrentDirectory(), "Logo.jpg"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 1, 100, 60);
try
{
int Row = 7;
int RowDetail = 8;
int RowCertificate = 9;
int FirstRowRange = 7;
int SecondRowRange = 11;
decimal decTotalPO = 0;
decimal decTotalShip = 0;
xlHoja.Cells[2, 1] = "SHIPPING REPORT VINCE STYLE # " + txtNameStyle.Text;
mLista = new ReporteProgramProductionBL().ListadoShippingReportVinceStyleGeneral(Parametros.intEmpresaId, 7, intIdStyle, Convert.ToInt32(cboSeason.EditValue));
if (mLista.Count > 0)
{
xlHoja.Cells[4, 2] = mLista[0].NameVendor;
foreach (var item in mLista)
{
xlHoja.Cells[Row, 1] = item.NameStyle;
xlHoja.Cells[Row, 2] = item.Description;
xlHoja.Cells[Row, 4] = item.Detail;
xlHoja.Cells[Row, 5] = item.NumberPO + "\\" + item.Dyelot;
xlHoja.Cells[Row, 6] = item.NameDestination;
xlHoja.Cells[RowDetail, 9] = item.XXS;
xlHoja.Cells[RowDetail, 10] = item.XS;
xlHoja.Cells[RowDetail, 11] = item.S;
xlHoja.Cells[RowDetail, 12] = item.M;
xlHoja.Cells[RowDetail, 13] = item.L;
xlHoja.Cells[RowDetail, 14] = item.XL;
xlHoja.Cells[RowDetail, 15] = item.XXL;
Row = Row + 7;
RowDetail = RowDetail + 7;
decTotalPO = decTotalPO + item.XS + item.S + item.M + item.L + item.XL + item.XXL;
}
//COLOREAMOS LAS FILAS SEGUN CRITERIO
var lstPOS =
from lista in mLista
group lista by lista.NumberPO into newGroup
orderby newGroup.Key
select newGroup;
string strNumberPO;
Color[] ColorSet = { Color.DodgerBlue, Color.MediumPurple, Color.LightGreen, Color.NavajoWhite, Color.LightCoral, Color.Aquamarine, Color.Brown, Color.CornflowerBlue, Color.Cyan, Color.DarkMagenta, Color.DarkOrange, Color.DeepPink, Color.OrangeRed, Color.MediumSlateBlue, Color.Orange, Color.DarkRed };
int intColor = 0;
foreach (var itempo in lstPOS)
{
strNumberPO = itempo.Key;
foreach (var itemrange in mLista)
{
if (itemrange.NumberPO == strNumberPO)
{
xlRange = xlHoja.get_Range("A" + FirstRowRange + ":" + "O" + SecondRowRange);
xlRange.Interior.Color = ColorSet[intColor];
FirstRowRange = FirstRowRange + 7;
SecondRowRange = SecondRowRange + 7;
}
}
intColor = intColor + 1;
}
}
//INSPECTION CERTIFICATE
mListaInspeccionCertificate = new ReporteInspectionCertificateBL().ListadoShippingReportVinceStyle(Parametros.intEmpresaId, 7, txtNameStyle.Text.Trim());
if (mListaInspeccionCertificate.Count > 0)
{
foreach (var item in mListaInspeccionCertificate)
{
xlHoja.Cells[RowCertificate, 9] = item.XXS;
xlHoja.Cells[RowCertificate, 10] = item.XS;
xlHoja.Cells[RowCertificate, 11] = item.S;
xlHoja.Cells[RowCertificate, 12] = item.M;
xlHoja.Cells[RowCertificate, 13] = item.L;
xlHoja.Cells[RowCertificate, 14] = item.XL;
xlHoja.Cells[RowCertificate, 15] = item.XXL;
RowCertificate = RowCertificate + 7;
decTotalShip = decTotalShip + item.XS + item.S + item.M + item.L + item.XL + item.XXL;
}
}
xlHoja.Cells[181, 8] = decTotalPO;
xlHoja.Cells[182, 8] = decTotalShip;
xlLibro.SaveAs("C:\\Excel\\ShippingReportVinceStyle.xlsx", Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlLibro.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
Cursor.Current = Cursors.Default;
BSUtils.OpenExcel("C:\\Excel\\ShippingReportVinceStyle.xlsx");
}
catch (Exception ex)
{
xlLibro.Close(false, Missing.Value, Missing.Value);
xlApp.Quit();
Cursor.Current = Cursors.Default;
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void btn1_Click_1(object sender, RoutedEventArgs e)
{
clearComments();
string showFileName = Directory.GetCurrentDirectory() + @"\hotel.xls";
string src_file_name = Directory.GetCurrentDirectory() + @"\Files\list.csv";
string dest_file_name = Directory.GetCurrentDirectory() + @"\Files\list.xlsx";
MsExcel.Application oExcApp; //Excel Application;
MsExcel.Workbook oExcBook; //
try
{
if (File.Exists(dest_file_name))
{
File.Delete(dest_file_name);
}
oExcApp = new MsExcel.Application();
object missing = System.Reflection.Missing.Value;
oExcBook = oExcApp.Workbooks.Add(true);
MsExcel.Worksheet worksheet1 = (MsExcel.Worksheet)oExcBook.Worksheets["sheet1"];
worksheet1.Activate();
oExcApp.Visible = false;
oExcApp.DisplayAlerts = false;
MsExcel.Range range1 = worksheet1.get_Range("B1", "H2");
range1.Columns.ColumnWidth = 8;
range1.Columns.RowHeight = 20;
range1.Merge(false);
//设置垂直居中和水平居中
range1.VerticalAlignment = MsExcel.XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = MsExcel.XlHAlign.xlHAlignCenter;
//range1.Font.Color = Color.FromRgb(0, 0, 255);
range1.Font.Size = 20;
range1.Font.Bold = true;
worksheet1.Cells[1, 2] = "学生成绩单";
worksheet1.Cells[3, 1] = "学号";
worksheet1.Cells[3, 2] = "姓名";
worksheet1.Columns[1].ColumnWidth = 12;
StreamReader sw = new StreamReader(src_file_name);
string a_str;
string[] str_list;
int i = 4;
a_str = sw.ReadLine();
while (a_str != null)
{
str_list = a_str.Split(",".ToCharArray());
worksheet1.Cells[i, 1] = str_list[0];
worksheet1.Cells[i, 2] = str_list[1];
i++;
a_str = sw.ReadLine();
}
sw.Close();
DataTable showdata = GetExcelData(showFileName);
foreach (DataRow row in showdata.Rows)
{
foreach (DataColumn column in showdata.Columns)
{
showComment(row[column].ToString());
}
}
for (int i1 = 0; i1 < 5; i1++)
{
for (int j = 0; j < 8; j++)
{
worksheet1.Cells[i1 + 18, j + 3].Value2 = "=CEILING.MATH(RAND()*100)";
worksheet1.Cells[i1 + 4, j + 3].Value2 = worksheet1.Cells[i1 + 18, j + 3].Value;
}
}
//添加图表
MsExcel.Shape theShape = worksheet1.Shapes.AddChart(MsExcel.XlChartType.xl3DColumn, 120, 130, 380, 250);
worksheet1.Cells[3, 3].Value2 = "美术";
worksheet1.Cells[3, 4].Value2 = "物理";
worksheet1.Cells[3, 5].Value2 = "政治";
worksheet1.Cells[3, 6].Value2 = "化学";
worksheet1.Cells[3, 7].Value2 = "体育";
worksheet1.Cells[3, 8].Value2 = "英语";
worksheet1.Cells[3, 9].Value2 = "数学";
worksheet1.Cells[3, 10].Value2 = "历史";
//设定图表的数据区域
MsExcel.Range range = worksheet1.get_Range("b3:j8");
theShape.Chart.SetSourceData(range, Type.Missing);
//设置图标题文本
theShape.Chart.HasTitle = true;
theShape.Chart.ChartTitle.Text = "学生成绩";
theShape.Chart.ChartTitle.Caption = "学生成绩";
//设置单元格边框线型
range1 = worksheet1.get_Range("a3", "j8");
range1.Borders.LineStyle = MsExcel.XlLineStyle.xlContinuous;
oExcBook.RefreshAll();
worksheet1 = null;
object file_name = dest_file_name;
oExcBook.Close(true, file_name, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcBook);
oExcBook = null;
oExcApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcApp);
oExcApp = null;
System.GC.Collect();
}
catch (Exception e2)
{
MessageBox.Show(e2.Message);
}
finally
{
Console.WriteLine(" 正在结束 excel 进程");
showComment("正在结束excel进程");
//关闭 excel 进程
Process[] AllProces = Process.GetProcesses();
for (int j = 0; j < AllProces.Length; j++)
{
string theProcName = AllProces[j].ProcessName;
if (String.Compare(theProcName, "EXCEL") == 0)
{
if (AllProces[j].Responding && !AllProces[j].HasExited)
{
AllProces[j].Kill();
}
}
}
//Close excel Process.
OpenExcel(dest_file_name);
}
}
/// <summary>
/// 使用COM读取Excel
/// </summary>
/// <param name="excelFilePath">路径</param>
/// <returns>DataTabel</returns>
public DataTable GetExcelData(string excelFilePath)
{
MsExcel.Application app = new MsExcel.Application();
MsExcel.Sheets sheets;
MsExcel.Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
DataTable dt = new DataTable();
wath.Start();
try
{
if (app == null)
{
return(null);
}
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
//将数据读入到DataTable中——Start
sheets = workbook.Worksheets;
MsExcel.Worksheet worksheet = (MsExcel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null)
{
return(null);
}
string cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
MsExcel.Range range;
//负责列头Start
DataColumn dc;
int ColumnID = 1;
range = (MsExcel.Range)worksheet.Cells[1, 1];
while (range.Text.ToString().Trim() != "")
{
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
dt.Columns.Add(dc);
range = (MsExcel.Range)worksheet.Cells[1, ++ColumnID];
}
//End
for (int iRow = 2; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (MsExcel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
wath.Stop();
TimeSpan ts = wath.Elapsed;
//将数据读入到DataTable中——End
return(dt);
}
catch
{
return(null);
}
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public ActionResult ShowDoc(HttpPostedFileBase file, string Title,string Index, int AcceptedUserId)
{
Document oldDocument;
Document newDocument;
using (var db = new DocumentDbContext())
{
oldDocument = GetOldOne(db, Index);
newDocument = GetNewOne(db, Index);
//stworzenie sciezki do zapisania pliku
string pathUser = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string pathDownload = Path.Combine(pathUser, "Downloads");
string title = Title + DateTime.Now;
title = title.Replace("-", "");
title = title.Replace(":", "");
string title2 = "temp" + ".pdf";
string EndPath = Path.Combine(pathDownload, title);
string EndPathPDF = Path.Combine(pathDownload, title2);
int currentVersion = oldDocument.Version+1;
//stworzenie tablicy wielkosci
newDocument.DocumentFile = new byte[file.InputStream.Length];
file.InputStream.Read(newDocument.DocumentFile, 0, newDocument.DocumentFile.Length);
System.IO.File.WriteAllBytes(EndPath, newDocument.DocumentFile);
MyApp = new Microsoft.Office.Interop.Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(EndPath);
MyWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)MyBook.Sheets[1];
MyWorkSheet.Cells[2, 3] = oldDocument.Index;
MyWorkSheet.Cells[3, 3] = db.Lines.Where(x => x.LineId ==oldDocument.LineId).First().LineName;
MyWorkSheet.Cells[2, 5] = Title;
MyWorkSheet.Cells[2, 13] = currentVersion;
MyWorkSheet.Cells[4, 3] = db.Operations.Where(x => x.OperationId ==oldDocument.OperationId).First().OperationNumber.ToString();
MyWorkSheet.Cells[5, 5] = db.DocumentTypes.Where(x => x.DocumentTypeId == oldDocument.DocumentTypeId).First().Description;
UserAccount tempAuthor;
User authorUser;
var user = (string)Session["UserName"];
using (var db1 = new AccountDbContext())
{
tempAuthor = db1.userAccount.Where(x => x.UserName == user).First();
authorUser = db.Users.Where(x => x.LastName == tempAuthor.LastName).First();
MyWorkSheet.Cells[4, 13] = authorUser.FirstName + " " + authorUser.LastName;
}
MyApp.Save();
MyBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, EndPathPDF);
MyBook.Close();
byte[] f1 = new byte[15728640];
f1 = System.IO.File.ReadAllBytes(EndPath);
byte[] fPDF = new byte[15728640];
fPDF = System.IO.File.ReadAllBytes(EndPathPDF);
newDocument.DocumentFileExcel = f1;
newDocument.DocumentFile = fPDF;
newDocument.Status = db.Status.Where(x => x.StatusId == 1).First();
newDocument.Version = currentVersion;
newDocument.Title = Title;
newDocument.LineId =oldDocument.LineId;
newDocument.DateOfAdding = DateTime.Now;
newDocument.OperationId = oldDocument.OperationId;
newDocument.DocumentTypeId = oldDocument.DocumentTypeId;
newDocument.AuthorUserId = authorUser.UserId;
newDocument.AcceptedUserId = AcceptedUserId;
db.Documents.Add(newDocument);
db.SaveChanges();
if (System.IO.File.Exists(EndPath))
{
System.IO.File.Delete(EndPath);
}
if (System.IO.File.Exists(EndPathPDF))
{
System.IO.File.Delete(EndPathPDF);
}
}
return RedirectToAction("ShowMessage");
}
public static DataTable getExcelDataToDataTable()
{
DataTable dt = new DataTable();
OpenFileDialog openFile = new OpenFileDialog();
openFile.DefaultExt = ".xlsx";
openFile.Filter = "(.xlsx)|*.xlsx|All Files(*.*)|*.*";
var browsefile = openFile.ShowDialog();
if (browsefile == true)
{
string txtFilePath = (openFile.FileName).ToString();
Excel.Application excelApp = new Excel.Application();
//Static File From Base Path...........
//Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Dynamic File Using Uploader...........
Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Excel.Range excelRange = excelSheet.UsedRange;
string strCellData = "";
double douCellData;
int rowCnt = 0;
int colCnt = 0;
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Excel.Range).Value2;
dt.Columns.Add(strColumn, typeof(string));
}
for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string strData = "";
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
try
{
strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Excel.Range).Value2;
strData += strCellData + "|";
}
catch (Exception ex)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Excel.Range).Value2;
strData += douCellData.ToString() + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
dt.Rows.Add(strData.Split('|'));
}
excelBook.Close(true, null, null);
excelApp.Quit();
return(dt);
}
else
{
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="fileName">file to get information form</param>
/// <param name="sheetName">valid sheet name to get last row and column</param>
/// <returns>ExcelLast</returns>
public ExcelLast UsedRowsColumns(string fileName, string sheetName)
{
int RowsUsed = -1;
int ColsUsed = -1;
if (File.Exists(fileName))
{
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(fileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
if (xlWorkSheet.Name == sheetName)
{
Excel.Range xlCells = null;
xlCells = xlWorkSheet.Cells;
Excel.Range workRange = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
RowsUsed = workRange.Row;
ColsUsed = workRange.Column;
Marshal.FinalReleaseComObject(workRange);
workRange = null;
Marshal.FinalReleaseComObject(xlCells);
xlCells = null;
break;
}
Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
Release(xlWorkSheets);
Release(xlWorkSheet);
Release(xlWorkBook);
Release(xlWorkBooks);
Release(xlApp);
return(new ExcelLast()
{
Row = RowsUsed, Column = ColsUsed
});
}
else
{
throw new Exception("'" + fileName + "' not found.");
}
}
public void EntityToExcelSheet(string excelFilePath, string sheetName, IQueryable result, ObjectContext ctx)
{ //Query to grab excel data
// string query = "select * from Employees";
// string excelFilePath = "";
//string sheetName = "";
//IQueryable result;
//ObjectContext ctx;
//Oledb set up
Excel.Application appObj;
Excel.Workbook workBookObj;
Excel.Worksheet sheetObj;
Excel.Range rangeCheck;
try
{
//Excel / application object reference
appObj = new Excel.Application();
// Setting properties
appObj.Visible = true;
appObj.DisplayAlerts = false;
//New workbook
workBookObj = appObj.Workbooks.Add(Missing.Value);
//Get active
sheetObj = (Excel.Worksheet)workBookObj.ActiveSheet;
sheetObj.Name = sheetName;
// Process the DataTable
// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
DataTable dt = EntityToDataTable(result, ctx);
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
sheetObj.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
sheetObj.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
// Resize the columns
rangeCheck = sheetObj.Range[sheetObj.Cells[1, 1], sheetObj.Cells[rowCount, dt.Columns.Count]];
rangeCheck.Columns.AutoFit();
// Save the sheet and close
sheetObj = null;
rangeCheck = null;
workBookObj.SaveAs(excelFilePath, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
workBookObj.Close(Missing.Value, Missing.Value, Missing.Value);
workBookObj = null;
appObj.Quit();
}
catch (Exception ex)
{
throw ex;
}
}
private void Form1_Load(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\user\Downloads\monte-carlo\monte-carlo\monte-carlo\monte-carlo\bin\Debug\data1.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int k = 0;
for (int i = 2; ; i++)
{
for (int j = 1; j <= 2; j++)
{
//write the value to the console
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
if (j % 2 == 1)
{
clist pnn = new clist();
pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2);
tot += pnn.val;
pnn.cumulative = tot;
demand.Add(pnn);
dataGridView1.Rows.Add();
dataGridView1.Rows[k].Cells[0].Value = pnn.val;
}
else
{
clist pnn = new clist();
pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2);
tot2 += pnn.val;
pnn.cumulative = tot2;
freqency.Add(pnn);
dataGridView1.Rows.Add();
dataGridView1.Rows[k].Cells[1].Value = pnn.val;
dataGridView1.Rows[k].Cells[3].Value = pnn.cumulative;
}
}
else
{
stop = true;
}
//add useful things here!
}
k++;
if (stop)
{
break;
}
}
tot = 0;
for (int i = 0; i < freqency.Count; i++)
{
clist pnn = new clist();
pnn.val = freqency[i].val / freqency[freqency.Count - 1].cumulative;
tot += pnn.val;
pnn.relativecumulative = tot;
relativefreq.Add(pnn);
dataGridView1.Rows[i].Cells[2].Value = pnn.val;
dataGridView1.Rows[i].Cells[4].Value = pnn.relativecumulative;
}
for (int i = 0; i < freqency.Count; i++)
{
if (i == 0)
{
freqency[i].range = 0;
freqency[i].rangeend = freqency[i].cumulative;
dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
}
else
{
freqency[i].range = (freqency[i - 1].cumulative + 1);
freqency[i].rangeend = freqency[i].cumulative;
dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
}
}
xlWorkbook.Close(true);
xlApp.Quit();
}
//Create the Excel file from dataset
public void Okuri(DataTable ds, string SOKOCD)
{
string saveFileName = "送り状兼受領書_" + SOKOCD + "_" + DateTime.Now.ToString("yyyyMMddHHmmss");
string templetFile = @"template\送り状兼受領書.xlsx";
SaveFileDialog saveDialog = new SaveFileDialog {
Filter = "Excel文件 |*.xlsx",
FileName = saveFileName,
RestoreDirectory = true
};
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
File.Copy(templetFile, saveFileName, true);
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(saveFileName);
Excel.Worksheet worksheet = workbook.Worksheets[1];
excel.Visible = false;
try
{
log.Info("EXEC BEGIN");
var distinctIds = ds.AsEnumerable()
.Select(s => new
{
area = s.Field <string>("AREANM"),
homen = s.Field <string>("HOMEN"),
//soko = s.Field<string>("SOKONM"),
//nukn = s.Field<string>("NUKNNM"),
})
.Distinct().ToList();
if (distinctIds.Count > 1)
{
int no = 0;
foreach (var a in distinctIds)
{
string expression;
DataRow[] foundRows;
expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'";
foundRows = ds.Select(expression);
worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10];
workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0];
worksheet.Cells[1, "H"] = foundRows[0][11];
worksheet.Cells[1, "AD"] = foundRows[0][10];
worksheet.Cells[5, "B"] = foundRows[0][2];
worksheet.Cells[5, "H"] = foundRows[0][5];
worksheet.Cells[5, "N"] = foundRows[0][3];
worksheet.Cells[5, "Y"] = foundRows[0][1];
worksheet.Cells[5, "AS"] = foundRows[0][6];
worksheet.Cells[5, "AW"] = foundRows[0][7];
worksheet.Cells[5, "BC"] = foundRows[0][8];
worksheet.Cells[5, "BM"] = foundRows[0][9];
worksheet.Cells[5, "CD"] = foundRows[0][12];
worksheet.Cells[5, "CN"] = "サイン";
for (int i = 0; i < foundRows.Length - 1; i++)
{
Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 6)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
worksheet.Cells[6 + i, "B"] = foundRows[i + 1][2];
worksheet.Cells[6 + i, "H"] = foundRows[i + 1][5];
worksheet.Cells[6 + i, "N"] = foundRows[i + 1][3];
worksheet.Cells[6 + i, "Y"] = foundRows[i + 1][1];
worksheet.Cells[6 + i, "AS"] = foundRows[i + 1][6];
worksheet.Cells[6 + i, "AW"] = foundRows[i + 1][7];
worksheet.Cells[6 + i, "BC"] = foundRows[i + 1][8];
worksheet.Cells[6 + i, "BM"] = foundRows[i + 1][9];
worksheet.Cells[6 + i, "CD"] = foundRows[i + 1][12];
worksheet.Cells[6 + i, "CN"] = "サイン";
}
no++;
if (no < distinctIds.Count)
{
worksheet.Copy(workbook.Worksheets[1], Type.Missing);
worksheet.get_Range("B5:B1000").EntireRow.ClearContents();
worksheet.get_Range("CN6:CN1000").EntireRow.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
}
else
{
foreach (var a in distinctIds)
{
string expression;
DataRow[] foundRows;
expression = "AREANM = '" + a.area.ToString() + "' AND HOMEN = '" + a.homen.ToString() + "'";
foundRows = ds.Select(expression);
worksheet.Name = foundRows[0][11] + "_" + foundRows[0][10];
workbook.Worksheets[1].Name = workbook.Worksheets[1].Name.Split(' ')[0];
worksheet.Cells[1, "H"] = foundRows[0][11];
worksheet.Cells[1, "AD"] = foundRows[0][10];
worksheet.Cells[5, "B"] = foundRows[0][2];
worksheet.Cells[5, "H"] = foundRows[0][5];
worksheet.Cells[5, "N"] = foundRows[0][3];
worksheet.Cells[5, "Y"] = foundRows[0][1];
worksheet.Cells[5, "AS"] = foundRows[0][6];
worksheet.Cells[5, "AW"] = foundRows[0][7];
worksheet.Cells[5, "BC"] = foundRows[0][8];
worksheet.Cells[5, "BM"] = foundRows[0][9];
worksheet.Cells[5, "CD"] = foundRows[0][12];
worksheet.Cells[5, "CN"] = "サイン";
for (int i = 1; i < foundRows.Length; i++)
{
Excel.Range RngToCopy = worksheet.get_Range("B5").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("B" + (i + 5)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
worksheet.Cells[5 + i, "B"] = foundRows[i][2];
worksheet.Cells[5 + i, "H"] = foundRows[i][5];
worksheet.Cells[5 + i, "N"] = foundRows[i][3];
worksheet.Cells[5 + i, "Y"] = foundRows[i][1];
worksheet.Cells[5 + i, "AS"] = foundRows[i][6];
worksheet.Cells[5 + i, "AW"] = foundRows[i][7];
worksheet.Cells[5 + i, "BC"] = foundRows[i][8];
worksheet.Cells[5 + i, "BM"] = foundRows[i][9];
worksheet.Cells[5 + i, "CD"] = foundRows[i][12];
worksheet.Cells[5 + i, "CN"] = "サイン";
}
}
}
}
catch (Exception e)
{
log.Error("ERROR" + e.Message);
}
finally
{
excel.DisplayAlerts = false;
Application.DoEvents();
workbook.Save();
excel.Quit();
Marshal.FinalReleaseComObject(excel);
log.Info("EXEC END");
}
}
else
{
MessageBox.Show("処理を中止しました。");
}
}
public static string FillExcel <T>(string Path, string[] columnName, string name, List <T> list)
{
System.Data.DataTable dt = ListToDataTable.ListToTable <T>(list, columnName);
Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
Workbooks wbk2 = ex.Workbooks;
_Workbook _wbk2 = wbk2.Add(true);
//复制完关闭excel模板
Worksheet ws = _wbk2.Sheets[1];
//整体写入方法
object[,] objData = new object[dt.Rows.Count + 1, dt.Columns.Count];
//首先将数据写入到一个二维数组中
for (int m = 0; m < dt.Columns.Count; m++)
{
//if (!dt.Columns[m].ColumnName.StartsWith("F"))
objData[0, m] = dt.Columns[m].ColumnName;
//else
// objData[0, m] = null;
}
if (dt.Rows.Count > 0)
{
for (int m = 0; m < dt.Rows.Count; m++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
objData[m + 1, j] = dt.Rows[m][j];
}
}
}
string startCol = "A";
int iCnt = (dt.Columns.Count / 26);
string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
string endCol = endColSignal + ((char)('A' + dt.Columns.Count - iCnt * 26 - 1)).ToString();
Microsoft.Office.Interop.Excel.Range range = ws.get_Range(startCol + "1", endCol + (dt.Rows.Count - iCnt * 26 + 1).ToString());
//Microsoft.Office.Interop.Excel.Range range = ws.get_Range(startCol + "1", endCol + dt.Rows.Count.ToString());
range.Value = objData;
//逐个单元格写入方法
// for (int r = 0; r < dt.Rows.Count; r++)
// {
// for (int l = 0; l < dt.Columns.Count; l++)
// {
// ws.Cells[r + 2, l + 1] = dt.Rows[r][l].ToString();
// }
// }
//保存
ex.DisplayAlerts = false;
ex.AlertBeforeOverwriting = false;
try
{
_wbk2.SaveAs(Path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception e)
{
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk2);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbk2);
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
}
return(Path);
}
private void Design_IRC_Abutment_Bridges_Box_Type()
{
string file_path = Get_Project_Folder();
//file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx");
file_path = Path.Combine(file_path, "IRC Abutment Design Box Type.xlsx");
string copy_path = file_path;
//file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC Abutment Design.xlsx");
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Box_Type.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.Application();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"];
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8));
//formatRange.Interior.Color = System.Drawing.
//ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <string> list = new List <string>();
#region Section Input
myExcelWorksheet.get_Range("A9").Formula = txt_sec_B1.Text;
myExcelWorksheet.get_Range("B9").Formula = txt_sec_B2.Text;
myExcelWorksheet.get_Range("C9").Formula = txt_sec_B3.Text;
myExcelWorksheet.get_Range("E9").Formula = txt_sec_B4.Text;
myExcelWorksheet.get_Range("D15").Formula = txt_sec_B5.Text;
myExcelWorksheet.get_Range("G17").Formula = txt_sec_B6.Text;
myExcelWorksheet.get_Range("A17").Formula = txt_sec_H1.Text;
myExcelWorksheet.get_Range("E12").Formula = txt_sec_H2.Text;
myExcelWorksheet.get_Range("G12").Formula = txt_sec_H3.Text;
myExcelWorksheet.get_Range("I12").Formula = txt_sec_H4.Text;
myExcelWorksheet.get_Range("D13").Formula = txt_sec_H5.Text;
myExcelWorksheet.get_Range("E20").Formula = txt_sec_H6.Text;
#endregion Section Input
DataGridView dgv = dgv_sidl;
int rindx = 0;
#region SIDL Input
string v1 = "";
double n, w, d, uu;
myExcelWorksheet.get_Range("L118").Formula = txt_sidl_spc1.Text;
myExcelWorksheet.get_Range("M118").Formula = txt_sidl_spc2.Text;
for (int i = 0; i < dgv.RowCount; i++)
{
n = MyList.StringToDouble(dgv[2, i].Value.ToString(), 0.0);
w = MyList.StringToDouble(dgv[3, i].Value.ToString(), 0.0);
d = MyList.StringToDouble(dgv[4, i].Value.ToString(), 0.0);
uu = MyList.StringToDouble(dgv[5, i].Value.ToString(), 0.0);
myExcelWorksheet.get_Range("D" + (i + 117)).Formula = dgv[2, i].Value.ToString();
myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[3, i].Value.ToString();
myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[4, i].Value.ToString();
myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[5, i].Value.ToString();
try
{
if ((n * w * d * uu) == 0.0)
{
myExcelWorksheet.get_Range("H" + (i + 117)).Formula = dgv[6, i].Value.ToString();
}
}
catch (Exception ex0)
{
//throw;
}
}
#endregion SIDL Input
double L = MyList.StringToDouble(txt_Ana_L.Text, 0.0);
double og = MyList.StringToDouble(txt_Ana_og.Text, 0.0);
double eff_L = (L - 2 * og);
myExcelWorksheet.get_Range("B153").Formula = txt_Ana_og.Text;
myExcelWorksheet.get_Range("C152").Formula = (eff_L / 4.0).ToString("f3");
myExcelWorksheet.get_Range("D160").Formula = eff_L.ToString("f3");
#region LL
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["LL"];
myExcelWorksheet.get_Range("A9").Formula = og.ToString();
myExcelWorksheet.get_Range("I10").Formula = eff_L.ToString();
myExcelWorksheet.get_Range("S9").Formula = og.ToString();
#endregion LL
#region Earth pr
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"];
dgv = dgv_earth_pressure;
for (int i = 0; i < dgv.RowCount; i++)
{
myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString();
}
#endregion Earth pr
#region Base Pressure
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"];
rindx = 0;
dgv = dgv_base_pressure;
List <string> ldbl = new List <string>();
for (int i = 0; i < dgv.RowCount; i++)
{
try
{
if (dgv[1, i].Value.ToString() != "")
{
ldbl.Add(dgv[1, i].Value.ToString());
}
}
catch (Exception exx) {}
}
rindx = 0;
for (int i = 7; i < 55; i++)
{
if (i == 18 ||
i == 19 ||
i == 25 ||
(i >= 28 && i <= 30) ||
i == 35 ||
i == 44 ||
i == 47)
{
continue;
}
else
{
myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString();
}
}
#endregion Earth pr
rindx = 0;
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
//iapp.Excel_Open_Message();
}
public JsonResult GAnalytics(string path, string orig_filename, int month, int year, DateTime dt)
{
//string path = "C:\\Users\\Abel\\Downloads\\AugustAnalytics\\BMRACING.xlsx";
var _app = new Excel.Application();
var _workbooks = _app.Workbooks.Open(path);
var _worksheet = _workbooks.ActiveSheet;
string cell_data = "";
Microsoft.Office.Interop.Excel.Range range = _worksheet.UsedRange;
//Read the first cell
string text_value = "";
int end_row = range.Rows.Count;
var end_column = range.Columns.Count;
var group_field = "";
var subgroup_field_text = "";
var subgroup_field_column2 = "";
var subgroup_field_column3 = "";
var subgroup_field_column4 = "";
var insert = false;
var value = "";
var name = "";
//[row,column]
for (var index_row = 1; index_row <= end_row; index_row++)
{
insert = true;
value = "";
name = "";
for (var index_column = 1; index_column <= end_column; index_column++)
{
text_value = _worksheet.Cells[index_row, index_column].Text.ToString();
if (text_value != "" || index_column == 2 || index_column == 3 || index_column == 4)
{
if (index_column == 1)
{
if (text_value == "Audience" ||
text_value == "New vs Returning Visitor" ||
text_value == "Devices" ||
text_value == "Acquisition" ||
text_value == "Behavior" ||
text_value == "Site Speed"
)
{
group_field = text_value;
insert = false;
}
else if (text_value.Contains("Country"))
{
group_field = "Country";
insert = false;
}
}
if (!insert)
{
if (index_column == 2)
{
subgroup_field_column2 = text_value;
}
else if (index_column == 3)
{
subgroup_field_column3 = text_value;
//subgroup_field_text = subgroup_field_column3;
}
else if (index_column == 4)
{
subgroup_field_column4 = text_value;
// subgroup_field_text = subgroup_field_column4;
}
}
else
{
if (index_column == 1)
{
name = text_value;
}
if (index_column == 2)
{
subgroup_field_text = subgroup_field_column2;
value = text_value;
}
else if (index_column == 3)
{
subgroup_field_text = subgroup_field_column3;
value = text_value;
}
else if (index_column == 4)
{
subgroup_field_text = subgroup_field_column4;
value = text_value;
}
}
string fileName = Path.GetFileNameWithoutExtension(orig_filename);
//cell_data += group_field + index_row + ":" + index_column + " " + text_value;
if (insert && value != "")
{
Library.Execute(@"insert into tblBrandGoogleAanalyticsData
(brand,group_field,subgroup_id, subgroup_field, name, value, month, year, dt)
values('" + fileName + "','" + group_field + "','" + (index_column - 1) + "','" + subgroup_field_text + "','" + name + "','" + value + "','" + month + "','" + year + "','" + dt.Date + @"'
)
");
// cell_data += "group_field: " + group_field + " subgroup_field:(" + (index_column - 1) + ")" + subgroup_field_text + " " + "name: " + name + " " + value;
}
}
else
{
// subgroup_field_column2 = ""; subgroup_field_column3 = ""; subgroup_field_column4 = "";
}
}
}
_workbooks.Close();
_app.Quit();
if (_worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_worksheet);
}
if (_workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_workbooks);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(_app);
return(Json(new
{
path = path
}));
}
protected override void Execute(CodeActivityContext context)
{
//Load Input Fields unto local variables
string filePath = FilePath.Get(context);
//Validate that file path is not empty
if (!String.IsNullOrEmpty(filePath))
{
if (File.Exists(filePath))
{
try
{
//Initialize Excel Interop objects;
object m = Type.Missing;
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(filePath, m, true, m, m, m, m, m, m, m, m, m, m, m, m);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
// Gather Row and Col counts
Int32 lastRow = xlRange.Rows.Count;
Int32 lCol = xlRange.Columns.Count;
Int32 sCol = lCol + 1;
// Create local variables for processing
int i;
string vLstCol;
//iterate by the existing columns to determine if a current Status column exists
for (i = 1; i <= lCol; i++)
{
vLstCol = xlWorksheet.Cells[1, i].Value;
if (vLstCol == "Status")
{
sCol = i;
lCol = i - 1;
}
}
//Count all rows that Status Column has the value of "Complete"
int countComplete = (int)xlApp.WorksheetFunction.CountIf(xlRange.Columns[sCol, m], "Complete") + 1;
//Count all rows that are not empty on the first column (Item-Index column)
int countNotNull = (int)xlApp.WorksheetFunction.CountA(xlRange.Columns[1, m]);
//Validate that complete rows match the lastRow count
if (countComplete == countNotNull)
{
//Return True for Complete
isComplete.Set(context, true);
}
else
{
//Return False for not complete
isComplete.Set(context, false);
}
//DebugOut Output
DebugOut.Set(context, "CC: " + countComplete.ToString() + " | CA: " + countNotNull.ToString() + " | LR: " + lastRow.ToString());
//Close Workbook no Save
xlWorkbook.Close(false, m, m);
//CLOSE AND GARBAGE COLLECT
Marshal.ReleaseComObject(xlWorksheet);
xlWorksheet = null;
Marshal.ReleaseComObject(xlWorkbooks);
xlWorkbooks = null;
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect(); //Garbage Collect
GC.WaitForPendingFinalizers(); //Wait until Garbage Collect completes
}
catch
{
throw;
}
}
else
{
throw new FileNotFoundException();
}
}
else
{
throw new ArgumentNullException();
}
}
private void button1_Click(object sender, EventArgs e)
{
// Khởi động chương trình Excel
COMExcel.Application exApp = new COMExcel.Application();
COMExcel.Workbook exBook;
COMExcel.Worksheet exSheet;
COMExcel.Range exRange;
string sql;
int hang = 0, cot = 0;
DataTable tblThongtinHD, tblThongtinHang;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
// Định dạng chung
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Name = "Times new roman";
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời
exRange.Range["A1:A1"].ColumnWidth = 7;
exRange.Range["B1:B1"].ColumnWidth = 15;
exRange.Range["A1:B1"].MergeCells = true;
exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A1:B1"].Value = "E Store";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Việt Nam";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: 0972594994";
exRange.Range["C2:E2"].Font.Size = 16;
exRange.Range["C2:E2"].Font.Name = "Times new roman";
exRange.Range["C2:E2"].Font.Bold = true;
exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ
exRange.Range["C2:E2"].MergeCells = true;
exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:E2"].Value = "BÁO CÁO";
//Lấy thông tin các mặt hàng
sql = @"SELECT tongtien, makh FROM tb_HDB WHERE ngayban BETWEEN '" + d1.Text + "' AND '" + d2.Text + "'";
tblThongtinHang = cn.taobang(sql);
//Tạo dòng tiêu đề bảng
exRange.Range["A11:F11"].Font.Bold = true;
exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C11:F11"].ColumnWidth = 12;
exRange.Range["A11:A11"].Value = "STT";
exRange.Range["B11:B11"].Value = "Mã KH";
exRange.Range["C11:C11"].Value = "Số Lượng";
for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++)
{
//Điền số thứ tự vào cột 1 từ dòng 12
exSheet.Cells[1][hang + 12] = hang + 1;
for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++)
{
//Điền thông tin hàng từ cột thứ 2, dòng 12
exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString();
}
}
exRange = exSheet.Cells[1][hang + 15]; //Ô A1
exRange.Range["A1:F1"].MergeCells = true;
exRange.Range["A1:F1"].Font.Bold = true;
exRange.Range["A1:F1"].Font.Italic = true;
exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight;
exRange = exSheet.Cells[4][hang + 17]; //Ô A1
exRange.Range["A1:C1"].MergeCells = true;
exRange.Range["A1:C1"].Font.Italic = true;
exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
DateTime d = DateTime.Now;
exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year;
exRange.Range["A2:C2"].MergeCells = true;
exRange.Range["A2:C2"].Font.Italic = true;
exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A6:C6"].MergeCells = true;
exRange.Range["A6:C6"].Font.Italic = true;
exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exSheet.Name = "Báo Cáo";
exApp.Visible = true;
}
private void EigyoReport(DataGridView tempDGV)
{
const int S_GYO = 4; //エクセルファイル見出し行（明細は3行目から印字）
const int S_ROWSMAX = 8; //エクセルファイル列最大値
string sMidashi;
double gTL = 0;
try
{
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.営業売上表, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing));
Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2];
try
{
for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++)
{
sMidashi = this.comboBox1.Text + " ";
if (tDate.Checked == true)
{
sMidashi += this.tDate.Value.ToShortDateString() + " 〜 ";
}
if (tDate2.Checked == true)
{
if (tDate.Checked == false)
{
sMidashi += " 〜 ";
}
sMidashi += this.tDate2.Value.ToShortDateString();
}
oxlsSheet.Cells[S_GYO - 2, 1] = sMidashi;
oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[colNDt, iX].Value.ToString();
oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[colClient, iX].Value.ToString();
oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[colNyukin, iX].Value.ToString();
oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[colEganka, iX].Value.ToString();
oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[colArari1, iX].Value.ToString();
double g = Utility.strToDouble(tempDGV[colGaichuhi, iX].Value.ToString()) +
Utility.strToDouble(tempDGV[colGaichuhi2, iX].Value.ToString()) +
Utility.strToDouble(tempDGV[colGaichuhi3, iX].Value.ToString());
oxlsSheet.Cells[iX + S_GYO, 6] = g.ToString();
oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[colArari2, iX].Value.ToString();
oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[colArariSai, iX].Value.ToString();
//セル下部へ実線ヨコ罫線を引く
rng[0] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, 1];
rng[1] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, S_ROWSMAX];
oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
gTL += g;
}
////セル上部へ実線ヨコ罫線を引く
//rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1];
//rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX];
//oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
////セル下部へ実線ヨコ罫線を引く
//rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1];
//rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX];
//oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//表全体に実線縦罫線を引く
rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1];
rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX];
oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//表全体の左端縦罫線
rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1];
rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1];
oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//表全体の右端縦罫線
rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX];
rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX];
oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//合計
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count + 1, 3] = this.label5.Text;
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 4] = this.lblEgenka.Text;
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 5] = this.lblArari1.Text;
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 6] = gTL.ToString("#,##0");
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 7] = this.lblArari2.Text;
oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 8] = this.lblArarisai.Text;
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
// 確認のためExcelのウィンドウを表示する
oXls.Visible = true;
//印刷
oxlsSheet.PrintPreview(true);
// ウィンドウを非表示にする
oXls.Visible = false;
//保存処理
oXls.DisplayAlerts = false;
DialogResult ret;
string msgHD = "";
if (tDate.Checked == true)
{
msgHD += tDate.Value.ToLongDateString() + "から";
}
if (tDate2.Checked == true)
{
msgHD += tDate2.Value.ToLongDateString() + "まで";
}
//ダイアログボックスの初期設定
saveFileDialog1.Title = MESSAGE_CAPTION;
saveFileDialog1.OverwritePrompt = true;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + comboBox1.Text + "_" + msgHD;
saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*";
//ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示
string fileName;
ret = saveFileDialog1.ShowDialog();
if (ret == System.Windows.Forms.DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
//Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
//Excelを終了
oXls.Quit();
}
catch (Exception e)
{
MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
//Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
//Excelを終了
oXls.Quit();
}
finally
{
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "営業別売上表", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
public void SaveExcel(string saveFileName, DataGridView dataGridView1)
{
if (dataGridView1.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks workBooks;
Workbook workBook;
Worksheet workSheet;
try
{
app = new Microsoft.Office.Interop.Excel.Application();
workBooks = app.Workbooks;
workBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);
workSheet = (Worksheet)workBook.Worksheets[1];
}
catch (System.Exception ex)
{
Trace.WriteLine(ex.ToString());
MessageBox.Show(" 您可能没有安装 Office ，请安装再使用该功能 ");
return;
}
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
workSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
workSheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
workSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
if (saveFileName != "")
{
try
{
workBook.Saved = true;
workBook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
app.Quit();
app = null;
}
else
{
MessageBox.Show("数据表中没有要保存的数据");
}
}
public ExcelReader()
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkbooks = xlApp.Workbooks;
}
private void button1_Click(object sender, EventArgs e)
{
// Khởi động chương trình Excel
COMExcel.Application exApp = new COMExcel.Application();
COMExcel.Workbook exBook;
COMExcel.Worksheet exSheet;
COMExcel.Range exRange;
string sql;
int hang = 0, cot = 0;
DataTable tblThongtinHD, tblThongtinHang;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
// Định dạng chung
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Name = "Times new roman";
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời
exRange.Range["A1:A1"].ColumnWidth = 7;
exRange.Range["B1:B1"].ColumnWidth = 15;
exRange.Range["A1:B1"].MergeCells = true;
exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A1:B1"].Value = "E Store";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Việt Nam";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: 0972594994";
exRange.Range["C2:E2"].Font.Size = 16;
exRange.Range["C2:E2"].Font.Name = "Times new roman";
exRange.Range["C2:E2"].Font.Bold = true;
exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ
exRange.Range["C2:E2"].MergeCells = true;
exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:E2"].Value = "HÓA ĐƠN NHẬP";
// Biểu diễn thông tin chung của hóa đơn bán
sql = @"SELECT tb_HDB.sohdb, tb_Khachhang.tenkh, tb_Khachhang.diachi,
tb_Nhanvien.tennv,tb_HDB.ngayban
FROM tb_CTHDB INNER JOIN
tb_HDB ON tb_CTHDB.sohdb = tb_HDB.sohdb INNER JOIN
tb_Khachhang ON tb_HDB.makh = tb_Khachhang.makh INNER JOIN
tb_Nhanvien ON tb_HDB.manv = tb_Nhanvien.manv where tb_HDB.sohdb='" + cbhd.Text + "'";
tblThongtinHD = cn.taobang(sql);
exRange.Range["B6:C9"].Font.Size = 12;
exRange.Range["B6:C9"].Font.Name = "Times new roman";
exRange.Range["B6:B6"].Value = "Mã hóa đơn:";
exRange.Range["C6:E6"].MergeCells = true;
exRange.Range["C6:E6"].Value = tblThongtinHD.Rows[0][0].ToString();
exRange.Range["B7:B7"].Value = "Nhà Cung Cấp:";
exRange.Range["C7:E7"].MergeCells = true;
exRange.Range["C7:E7"].Value = tblThongtinHD.Rows[0][1].ToString();
exRange.Range["B8:B8"].Value = "Địa chỉ:";
exRange.Range["C8:E8"].MergeCells = true;
exRange.Range["C8:E8"].Value = tblThongtinHD.Rows[0][2].ToString();
exRange.Range["B9:B9"].Value = "Điện thoại:";
exRange.Range["C9:E9"].MergeCells = true;
exRange.Range["C9:E9"].Value = tblThongtinHD.Rows[0][3].ToString();
//Lấy thông tin các mặt hàng
sql = @"SELECT tb_Hanghoa.tenhang, tb_CTHDB.soluong,tb_Hanghoa.dongiaban, tb_CTHDB.giamgia, tb_CTHDB.thanhtien
FROM tb_CTHDB INNER JOIN
tb_Hanghoa ON tb_CTHDB.mahang = tb_Hanghoa.mahang where tb_CTHDB.sohdb='" + cbhd.Text + "'";
tblThongtinHang = cn.taobang(sql);
//Tạo dòng tiêu đề bảng
exRange.Range["A11:F11"].Font.Bold = true;
exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C11:F11"].ColumnWidth = 12;
exRange.Range["A11:A11"].Value = "STT";
exRange.Range["B11:B11"].Value = "Tên hàng";
exRange.Range["C11:C11"].Value = "Số lượng";
exRange.Range["D11:D11"].Value = "Đơn giá";
exRange.Range["E11:E11"].Value = "Giảm giá";
exRange.Range["F11:F11"].Value = "Thành tiền";
for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++)
{
//Điền số thứ tự vào cột 1 từ dòng 12
exSheet.Cells[1][hang + 12] = hang + 1;
for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++)
{
//Điền thông tin hàng từ cột thứ 2, dòng 12
exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString();
}
}
exRange = exSheet.Cells[1][hang + 15]; //Ô A1
exRange.Range["A1:F1"].MergeCells = true;
exRange.Range["A1:F1"].Font.Bold = true;
exRange.Range["A1:F1"].Font.Italic = true;
exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight;
exRange = exSheet.Cells[4][hang + 17]; //Ô A1
exRange.Range["A1:C1"].MergeCells = true;
exRange.Range["A1:C1"].Font.Italic = true;
exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
DateTime d = Convert.ToDateTime(tblThongtinHD.Rows[0][4]);
exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year;
exRange.Range["A2:C2"].MergeCells = true;
exRange.Range["A2:C2"].Font.Italic = true;
exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:C2"].Value = "Nhân viên Bán hàng";
exRange.Range["A6:C6"].MergeCells = true;
exRange.Range["A6:C6"].Font.Italic = true;
exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A6:C6"].Value = tblThongtinHD.Rows[0][3];
exSheet.Name = "Hóa đơn Bán";
exApp.Visible = true;
}
/// <summary>
/// 启动合并
/// </summary>
/// <param name="dirPath"></param>
public void Start(string dirPath)
{
if (this.isStarted)
{
return;
}
this.isStarted = true;
this.total = ExcelHelper.GetAvailableExcelCount(dirPath);
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
new AsyncTask <_Workbook>(() =>
{
Workbooks workbooks = excel.Workbooks;
_Workbook result = workbooks.Add();//创建一个空的excel文件
DirectoryInfo dir = new DirectoryInfo(dirPath);
FileSystemInfo[] fsList = dir.GetFileSystemInfos();
foreach (FileSystemInfo fs in fsList)
{
if (fs is FileInfo)
{
if (fs.Attributes.HasFlag(FileAttributes.Temporary) || fs.Attributes.HasFlag(FileAttributes.Hidden) || fs.Attributes.HasFlag(FileAttributes.NotContentIndexed))
{
continue;
}
if (fs.Extension.Equals(".xls", StringComparison.OrdinalIgnoreCase) ||
fs.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
_Workbook wb = workbooks.Open(fs.FullName);
foreach (_Worksheet sheet in wb.Sheets)
{
Range range = sheet.UsedRange;
range.Copy(((_Worksheet)result.Worksheets[1]).Range[string.Format("A{0}", currentRowCount + 1), Missing.Value]);
currentRowCount += range.Rows.Count;
}
wb.Close();
this.finished++;//每处理完一个文件 完成数+1
}
}
}
return(result);
}).Run((result, ex) =>
{
string path = string.Empty;
string name = "合并后的excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
if (ex != null)
{
OnErrored(ex.Message);
}
else
{
path = ExcelHelper.SaveTo(name);
}
try
{
if (result != null)
{
if (!string.IsNullOrEmpty(path))
{
result.SaveAs(path);
}
else
{
if (!Directory.Exists(cacheDirPath))
{
Directory.CreateDirectory(cacheDirPath);
}
result.SaveAs(System.IO.Path.Combine(cacheDirPath, name));
}
result.Close();
}
excel.Quit();
if (!string.IsNullOrEmpty(path))
{
ExcelHelper.OpenFileFolder(path);
}
}
catch (Exception ex2)
{
OnErrored(ex2.Message);
}
finally
{
this.currentRowCount = 0; //清理上一次记录行号
this.finished = 0;
this.isStarted = false;
OnCompleted();
}
});
}
public static void Senddata(string name, string unit, int count, int price, double discount, Excel.Application excelapp, Excel.Worksheet excelworksheet, int rubdiscount)
{
int k = 10;
int l;
//Поиск пустой строки
for (k = 10; k < 200; k++)
{
Excel.Range cell = (Excel.Range)excelworksheet.Cells[k, 3];
if (cell.Value == null)
{
break;
}
}
//Очистка на всякий пожарный, перед записью
for (l = k; l < k + 6; l++)
{
for (int i = 2; i < 9; i++)
{
Excel.Range cell = (Excel.Range)excel.excelworksheet1.Cells[l, i];
cell.Value = null;
}
}
Excel.Range cell2 = (Excel.Range)excelworksheet.Cells[k + 1, 2];
cell2.Value = "";
cell2 = (Excel.Range)excelworksheet.Cells[k + 1, 3];
cell2.Value = "";
cell2 = (Excel.Range)excelworksheet.Cells[k + 2, 2];
cell2.Value = "";
cell2 = (Excel.Range)excelworksheet.Cells[k + 2, 3];
cell2.Value = "";
//
//Заполнение строки и динамическая отрисовка таблицы
cell2 = (Excel.Range)excelworksheet.Cells[k, 3];
cell2.Value = name;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlLeft;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
//Обводка
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = (Excel.Range)excelworksheet.Cells[k, 4];
cell2.Value = unit;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
//Обводка
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = (Excel.Range)excelworksheet.Cells[k, 5];
cell2.Value = count;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
//Обводка
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = (Excel.Range)excelworksheet.Cells[k, 6];
cell2.Value = price;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
//Обводка
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = (Excel.Range)excelworksheet.Cells[k, 8];
cell2.FormulaLocal = "=G" + Convert.ToString(k) + "*" + Convert.ToString(discount / 100);
cell2 = excelworksheet.get_Range("F" + k);
cell2.Font.Bold = false;
cell2 = (Excel.Range)excelworksheet.Cells[k, 7];
//cell2.Value = count * price;
cell2.FormulaLocal = "=E" + Convert.ToString(k) + "*F" + Convert.ToString(k);
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
//Обводка
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excelworksheet.get_Range("G" + k);
cell2.Font.Bold = false;
// столбец №
cell2 = excelworksheet.get_Range("B11:B" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excelworksheet.get_Range("B10", "G" + (k));
cell2.Font.Bold = false;
//
k = k + 1;
cell2 = excelworksheet.get_Range("F" + k);
cell2.Value = "Итого:";
cell2.Font.Bold = true;
cell2 = excelworksheet.get_Range("G" + k);
cell2.FormulaLocal = "=СУММ(G11:G" + (k - 1) + ")";
cell2.Font.Bold = true;
cell2 = excelworksheet.get_Range("F" + (k + 1));
cell2.Value = "Сумма скидки:";
cell2.Font.Bold = true;
cell2 = excelworksheet.get_Range("G" + (k + 1));
cell2.FormulaLocal = "=СУММ(H11:H" + (k - 1) + ")";
cell2.Font.Bold = true;
if (rubdiscount != 0)
{
cell2 = excel.excelworksheet1.get_Range("G" + (k + 1));
cell2.Value = rubdiscount;
}
cell2 = excelworksheet.get_Range("F" + (k + 2));
cell2.Value = "Итого со скидкой:";
cell2.Font.Bold = true;
cell2 = excelworksheet.get_Range("G" + (k + 2));
cell2.FormulaLocal = "=G" + k + "-G" + (k + 1);
cell2.Font.Bold = true;
//Итого прописью:
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 2];
cell2.Value = "Итого: ";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 3];
Excel.Range cell3 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 7];
cell2.Value = NumByWords.RurPhrase(Convert.ToDecimal(cell3.Value));
/* Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
* Excel.Workbook excelappworkbook = excelappworkbooks["Bobmaster Calculator"];
* excelappworkbook.Saved = false;*/
//excelapp.Windows[1].Close(true, "E:\\Bobmaster\\Bobmaster Calculator");
}
/// <summary>
/// Считывание разбивки на режимы (используется для формирования R-file) из xlsx файла формата 9.41-сц2
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public override List <KadrIntervals> KadrIntervalsReadFromExcel(string filename)
{
//считываем данные из Excel файла в двумерный массив
Excel.Application xlApp = new Excel.Application(); //Excel
Excel.Workbook xlWB; //рабочая книга
Excel.Worksheet xlSht; //лист Excel
xlWB = xlApp.Workbooks.Open(filename); //название файла Excel
int NShts = xlWB.Worksheets.Count;
List <KadrIntervals> KadrIntervalsList = new List <KadrIntervals>();
try
{
foreach (Excel.Worksheet sheet in xlWB.Worksheets)
{
int iLastRow = sheet.Cells[sheet.Rows.Count, "A"].End[Excel.XlDirection.xlUp].Row;
var arrData = (object[, ])sheet.Range["A5:H" + iLastRow].Value; //берём данные с листа Excel
KadrIntervals kadrIntervals = new KadrIntervals();
List <KadrInterval> intervals = new List <KadrInterval>();
//заполняем intervals данными из массива
int i;
for (i = 1; i <= arrData.GetUpperBound(0); i++)
{
double t = (double)arrData[i, 1] * 3_600_000 * 24;
long tbeg = (long)t;
double te = t + 3_600_000 * 24;
if (i != arrData.GetUpperBound(0))
{
te = (double)arrData[i + 1, 1] * 3_600_000 * 24;
}
long tend = (long)te;
string[] kadrs = new string[arrData.GetUpperBound(1) - 1];
int j;
for (j = 2; j <= arrData.GetUpperBound(1); j++)
{
kadrs[j - 2] = (string)arrData[i, j];
}
KadrInterval I = new KadrInterval(kadrs,
tbeg,
tend);
intervals.Add(I);
}
kadrIntervals.Intervals = intervals;
kadrIntervals.Id = sheet.Cells[2, "H"].Value.ToString();
kadrIntervals.tags = new List <string>();
var arrDataTags = (object[, ])sheet.Range["A2:H2"].Value;
for (i = 1; i <= 8; i++)
{
if (arrDataTags[1, i] != null)
{
kadrIntervals.tags.Add(arrDataTags[1, i].ToString());
}
}
kadrIntervals.filename = "NONE!";
KadrIntervalsList.Add(kadrIntervals);
Console.WriteLine(sheet.Name);
}
}
catch (Exception e)
{
MessageBox.Show("SpecialFor9_41_SCENARY4.KadrIntervalsReadFromExcel - Ошибка считывания файла " + filename + " : " + e.Message + " Stacktrace:" + e.StackTrace);
}
finally
{
xlWB.Close(false); //закрываем книгу, изменения не сохраняем
xlApp.Quit(); //закрываем Excel
}
return(KadrIntervalsList);
}
public void AttempImprimir()
{
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.DefaultExt = ".xlsx";
dlg.Filter = "Documentos Excel (.xlsx)|*.xlsx";
if (dlg.ShowDialog() == true)
{
string filename = dlg.FileName;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
Workbook excelPrint = excel.Workbooks.Open(@"C:\Programs\ElaraInventario\Resources\SalidaPrestamo.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1];
//Folio
excel.Cells[8, 6] = _movimientoModel.UnidMovimiento.ToString();
//Fecha
excel.Cells[8, 23] = _movimientoModel.FechaMovimiento;
//Solicitante y su área
excel.Cells[13, 12] = _movimientoModel.Solicitante.SOLICITANTE_NAME;
excel.Cells[15, 12] = _movimientoModel.Solicitante.Departamento.DEPARTAMENTO_NAME;
try
{
//Recibe
excel.Cells[21, 12] = _movimientoModel.Tecnico.TECNICO_NAME;
//Procedencia
excel.Cells[17, 12] = "Almacén: " + _movimientoModel.AlmacenProcedencia.ALMACEN_NAME;
//Destino
string p = "";
if (_movimientoModel.ProveedorDestino != null)
{
p = "Proveedor : " + _movimientoModel.ProveedorDestino.PROVEEDOR_NAME;
}
else if (_movimientoModel.AlmacenDestino != null)
{
p = "Almacén: " + _movimientoModel.AlmacenDestino.ALMACEN_NAME;
}
else
{
p = "Cliente: " + _movimientoModel.ClienteDestino.CLIENTE1;
}
excel.Cells[19, 12] = p;
}
catch (Exception Ex)
{
}
//TT
excel.Cells[33, 12] = _movimientoModel.Tt;
//Empresa
excel.Cells[11, 12] = _movimientoModel.Empresa.EMPRESA_NAME;
//Transporte
excel.Cells[27, 12] = _movimientoModel.Transporte.TRANSPORTE_NAME;
//Contacto
excel.Cells[31, 12] = _movimientoModel.Contacto;
//Guia
excel.Cells[29, 12] = _movimientoModel.Guia;
//Nombre de Sitio
excel.Cells[23, 12] = _movimientoModel.NombreSitio;
//Sitio/Enlace
excel.Cells[25, 12] = _movimientoModel.SitioEnlace;
int X = 40;
Microsoft.Office.Interop.Excel.Borders borders;
for (int i = 0; i < ItemModel.ItemModel.Count; i++)
{
//for (int i = 0; i < 5; i++) {
//No.
excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Merge();
excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 2] = (i + 1).ToString() + ".-";
borders = excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//DESCRIPCIÓN
excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Merge();
excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 4] = ItemModel.ItemModel[i].Articulo.ARTICULO1;
borders = excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//N° DE SERIE
excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Merge();
excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 23] = ItemModel.ItemModel[i].NUMERO_SERIE;
borders = excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//SKU
excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Merge();
excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 27] = ItemModel.ItemModel[i].SKU;
borders = excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//CANTIDAD
excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Merge();
excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 31] = ItemModel.ItemModel[i].CantidadMovimiento;
borders = excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
X++;
}
X += 2;
excel.Cells[X, 3] = "OBSERVACIONES:";
excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Merge();
borders = excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
X += 4;
excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].Merge();
excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 2] = "ENTREGADO POR:";
excel.Cells[X, 2].Font.Bold = true;
excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].Merge();
excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 18] = "RECIBIDO POR:";
excel.Cells[X, 18].Font.Bold = true;
X += 1;
excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Merge();
borders = excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Merge();
borders = excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelSheetPrint.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Visible = true;
}
}
// Метод автоматического перебора файлов-доноров из указанной папки и записи значений в шаблон
/*
* public void ReadFile(int counter, String[] dirs, String template, ProgressBar progressBar1, Label label3, Button Cancel)
* {
*
* //CloseProcess("start");
*
* var app = new Excel.Application();
* app.Visible = false;
*
* var outbook = app.Workbooks.Open(template);
* //Console.WriteLine(Directory.GetCurrentDirectory() + "/" + template);
*
* progressBar1.Visible = true;
*
* int t = 1;
* int n = 1;
* //int stoper = 0;
*
* foreach (string dir in dirs)
* {
* var inbook = app.Workbooks.Open(dir);
* Application.DoEvents();
* //button4.Focus();
* Cancel.Enabled = true;
* // Лист 1
*
* // 3 - Номер платежного документа
* CopyRange(
* inbook.Sheets[1].Range["I1", "I1"],
* outbook.Sheets[1].Range["C" + (t + 3).ToString(), "C" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 4 - Расчетный период
* CopyRange(
* inbook.Sheets[1].Range["D21", "D21"],
* outbook.Sheets[1].Range["D" + (t + 3).ToString(), "D" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 5 - Общая площадь
* CopyRange(
* inbook.Sheets[1].Range["F26", "F26"],
* outbook.Sheets[1].Range["E" + (t + 3).ToString(), "E" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 7 - Отапливаемая площадь
* CopyRange(
* inbook.Sheets[1].Range["I26", "I26"],
* outbook.Sheets[1].Range["G" + (t + 3).ToString(), "G" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 8 - Количество проживающих (зарегестрированных)
* CopyRange(
* inbook.Sheets[1].Range["M26", "M26"],
* outbook.Sheets[1].Range["H" + (t + 3).ToString(), "H" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 9 - Сумма к оплате за расчетный период
* CopyRange(
* inbook.Sheets[1].Range["N14", "N14"],
* outbook.Sheets[1].Range["X" + (t + 3).ToString(), "X" + (t + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 16 - БИК банка
*
* CopyRangeReg(
* inbook.Sheets[1].Range["J3", "J3"],
* outbook.Sheets[1].Range["P" + (t + 3).ToString(), "P" + (t + 3).ToString()],
* @"Кор\/сч: \d+ БИК: (\d+)");
*
* Application.DoEvents();
* Cancel.Focus();
* //button4.Focus();
*
* // 17 - Расчетный счет
*
* CopyRangeReg(
* inbook.Sheets[1].Range["I2", "I2"],
* outbook.Sheets[1].Range["Q" + (t + 3).ToString(), "Q" + (t + 3).ToString()],
* @"ИНН\/КПП: \d+ \/ \d+ Р\/сч: (\d+)");
*
* Application.DoEvents();
*
* // Лист 2
*
* if (t == 1) n = 1;
* else n += 16;
*
* // 01 - Номер платежного документа
* CopyRange(
* inbook.Sheets[1].Range["I1", "I1"],
* outbook.Sheets[2].Range["A" + (n + 4).ToString(), "A" + (n + 4).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // 02 - Заполняем поля затрат
*
* // Техобслуживание
* outbook.Sheets[2].Range["B" + (n + 4).ToString(), "B" + (n + 4).ToString()] = "Техобслуживание";
*
* CopyRange(
* inbook.Sheets[1].Range["F42", "I42"],
* outbook.Sheets[2].Range["G" + (n + 4).ToString(), "H" + (n + 4).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N42", "N42"],
* outbook.Sheets[2].Range["AC" + (n + 4).ToString(), "AC" + (n + 4).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Вывоз ТОП
* outbook.Sheets[2].Range["B" + (n + 4 + 1).ToString(), "B" + (n + 4 + 1).ToString()] = "Вывоз ТОП";
*
* CopyRange(
* inbook.Sheets[1].Range["F43", "I43"],
* outbook.Sheets[2].Range["G" + (n + 4 + 1).ToString(), "H" + (n + 4 + 1).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N43", "N43"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 1).ToString(), "AC" + (n + 4 + 1).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // ХВС
* outbook.Sheets[2].Range["B" + (n + 4 + 2).ToString(), "B" + (n + 4 + 2).ToString()] = "ХВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F44", "I44"],
* outbook.Sheets[2].Range["G" + (n + 4 + 2).ToString(), "H" + (n + 4 + 2).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N44", "N44"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 2).ToString(), "AC" + (n + 4 + 2).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // ГВС
* outbook.Sheets[2].Range["B" + (n + 4 + 3).ToString(), "B" + (n + 4 + 3).ToString()] = "ГВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F45", "I45"],
* outbook.Sheets[2].Range["G" + (n + 4 + 3).ToString(), "H" + (n + 4 + 3).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N45", "N45"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 3).ToString(), "AC" + (n + 4 + 3).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Водоотведение
* outbook.Sheets[2].Range["B" + (n + 4 + 4).ToString(), "B" + (n + 4 + 4).ToString()] = "Водоотведение";
*
* CopyRange(
* inbook.Sheets[1].Range["F46", "I46"],
* outbook.Sheets[2].Range["G" + (n + 4 + 4).ToString(), "H" + (n + 4 + 4).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N46", "N46"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 4).ToString(), "AC" + (n + 4 + 4).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Электроснабжение
* outbook.Sheets[2].Range["B" + (n + 4 + 5).ToString(), "B" + (n + 4 + 5).ToString()] = "Электроснабжение";
*
* CopyRange(
* inbook.Sheets[1].Range["F47", "I47"],
* outbook.Sheets[2].Range["G" + (n + 4 + 5).ToString(), "H" + (n + 4 + 5).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N47", "N47"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 5).ToString(), "AC" + (n + 4 + 5).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Отопление
* outbook.Sheets[2].Range["B" + (n + 4 + 6).ToString(), "B" + (n + 4 + 6).ToString()] = "Отопление";
*
* CopyRange(
* inbook.Sheets[1].Range["F49", "I49"],
* outbook.Sheets[2].Range["G" + (n + 4 + 6).ToString(), "H" + (n + 4 + 6).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N49", "N49"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 6).ToString(), "AC" + (n + 4 + 6).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // ХВС
* outbook.Sheets[2].Range["B" + (n + 4 + 7).ToString(), "B" + (n + 4 + 7).ToString()] = "ХВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F50", "I50"],
* outbook.Sheets[2].Range["G" + (n + 4 + 7).ToString(), "H" + (n + 4 + 7).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N50", "N50"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 7).ToString(), "AC" + (n + 4 + 7).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Стоки ХВС
* outbook.Sheets[2].Range["B" + (n + 4 + 8).ToString(), "B" + (n + 4 + 8).ToString()] = "Стоки ХВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F51", "I51"],
* outbook.Sheets[2].Range["G" + (n + 4 + 8).ToString(), "H" + (n + 4 + 8).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N51", "N51"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 8).ToString(), "AC" + (n + 4 + 8).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // ГВС
* outbook.Sheets[2].Range["B" + (n + 4 + 9).ToString(), "B" + (n + 4 + 9).ToString()] = "ГВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F52", "I52"],
* outbook.Sheets[2].Range["G" + (n + 4 + 9).ToString(), "H" + (n + 4 + 9).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N52", "N52"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 9).ToString(), "AC" + (n + 4 + 9).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Стоки ГВС
* outbook.Sheets[2].Range["B" + (n + 4 + 10).ToString(), "B" + (n + 4 + 10).ToString()] = "Стоки ГВС";
*
* CopyRange(
* inbook.Sheets[1].Range["F53", "I53"],
* outbook.Sheets[2].Range["G" + (n + 4 + 10).ToString(), "H" + (n + 4 + 10).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N53", "N53"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 10).ToString(), "AC" + (n + 4 + 10).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Электроснабжение
* outbook.Sheets[2].Range["B" + (n + 4 + 11).ToString(), "B" + (n + 4 + 11).ToString()] = "Электроснабжение";
*
* CopyRange(
* inbook.Sheets[1].Range["F54", "I54"],
* outbook.Sheets[2].Range["G" + (n + 4 + 11).ToString(), "H" + (n + 4 + 11).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N54", "N54"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 11).ToString(), "AC" + (n + 4 + 11).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Электроснабжение
* outbook.Sheets[2].Range["B" + (n + 4 + 12).ToString(), "B" + (n + 4 + 12).ToString()] = "Электроснабжение";
*
* CopyRange(
* inbook.Sheets[1].Range["F55", "I55"],
* outbook.Sheets[2].Range["G" + (n + 4 + 12).ToString(), "H" + (n + 4 + 12).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N55", "N55"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 12).ToString(), "AC" + (n + 4 + 12).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Текущ. ремонт
* outbook.Sheets[2].Range["B" + (n + 4 + 13).ToString(), "B" + (n + 4 + 13).ToString()] = "Текущ. ремонт";
*
* CopyRange(
* inbook.Sheets[1].Range["F56", "I56"],
* outbook.Sheets[2].Range["G" + (n + 4 + 13).ToString(), "H" + (n + 4 + 13).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N56", "N56"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 13).ToString(), "AC" + (n + 4 + 13).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Антенна
* outbook.Sheets[2].Range["B" + (n + 4 + 14).ToString(), "B" + (n + 4 + 14).ToString()] = "Антенна";
*
* CopyRange(
* inbook.Sheets[1].Range["F57", "I57"],
* outbook.Sheets[2].Range["G" + (n + 4 + 14).ToString(), "H" + (n + 4 + 14).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N57", "N57"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 14).ToString(), "AC" + (n + 4 + 14).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* // Услуги банка
* outbook.Sheets[2].Range["B" + (n + 4 + 15).ToString(), "B" + (n + 4 + 15).ToString()] = "Услуги банка";
*
* CopyRange(
* inbook.Sheets[1].Range["F58", "I58"],
* outbook.Sheets[2].Range["G" + (n + 4 + 15).ToString(), "H" + (n + 4 + 15).ToString()]);
*
* CopyRange(
* inbook.Sheets[1].Range["N58", "N58"],
* outbook.Sheets[2].Range["AC" + (n + 4 + 15).ToString(), "AC" + (n + 4 + 15).ToString()]);
*
* Application.DoEvents();
* Cancel.Focus();
*
* Console.WriteLine("Обработано файлов {0} из {1}.", t, dirs.Length);
*
* inbook.Close(false);
* progressBar1.Value += 1;
* //MessageBox.Show("Обработан файл " + t);
* label3.Text = "Обработано " + t + " из " + dirs.Length;
* t += 1;
*
* if (stop_cycle == "stop")
* {
* label3.Text = "Обработка остановлена.";
* stop_cycle = "no";
* progressBar1.Value = 0;
* break;
* }
* }
*
* outbook.Close(true);
* app.Visible = false;
*
* app.Quit();
*
* Console.WriteLine();
* Console.WriteLine("Шаблон \"{0}\" обновлён.", template);
* Console.WriteLine("Нажмите клавишу Enter для выхода...");
* Console.Read();
* }
*/
// Метод автоматического перебора файлов-доноров из указанной папки и записи значений в шаблон
public void ReadFile1(int counter, String[] dirs, String template, ProgressBar progressBar1, Label label3, Button Cancel)
{
Regex regex = new Regex(@"[A-Z]{1,2}[1-9]{0,2}");
//CloseProcess("start");
var app = new Excel.Application();
app.Visible = false;
var outbook = app.Workbooks.Open(template);
progressBar1.Visible = true;
int t = 1;
int n = 1;
//int stoper = 0;
foreach (var dir in dirs)
{
var inbook = app.Workbooks.Open(dir);
Application.DoEvents();;
Cancel.Enabled = true;
button3.Enabled = false;
String account = dataGridView1.Rows[0].Cells[2].Value.ToString();
if (t == 1)
{
n = 1;
}
else
{
n += 16;
}
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
String name = dataGridView1.Rows[i].Cells[0].Value.ToString();
int list_num = Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value.ToString());
String kvitancia_link = dataGridView1.Rows[i].Cells[2].Value.ToString();
String template_link = dataGridView1.Rows[i].Cells[3].Value.ToString();
int count_shift = Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value.ToString());
if (i < 8) // Заполняем первый лист
{
/*
* CopyRange(
* inbook.Sheets[1].Range[kvitancia_link, kvitancia_link],
* outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()]);
*/
if (regex.IsMatch(kvitancia_link))
{
CopyRange(
inbook.Sheets[1].Range[kvitancia_link, kvitancia_link],
outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()]);
}
else
{
outbook.Sheets[(int)list_num].Range[template_link + (t + 3).ToString(), template_link + (t + 3).ToString()] = kvitancia_link;
}
Application.DoEvents();
Cancel.Focus();
}
else // Заполняем второй лист
{
if (regex.IsMatch(account))
{
CopyRange(
inbook.Sheets[1].Range[account, account],
outbook.Sheets[(int)list_num].Range["A" + (n + 4 + count_shift).ToString(), "A" + (n + 4 + count_shift).ToString()]);
}
else
{
outbook.Sheets[(int)list_num].Range["A" + (n + 4 + count_shift).ToString(), "A" + (n + 4 + count_shift).ToString()] = account;
}
/*
* CopyRange(
* inbook.Sheets[1].Range[kvitancia_link, kvitancia_link],
* outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()]);
*/
if (regex.IsMatch(kvitancia_link))
{
CopyRange(
inbook.Sheets[1].Range[kvitancia_link, kvitancia_link],
outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()]);
}
else
{
outbook.Sheets[(int)list_num].Range[template_link + (n + 4 + count_shift).ToString(), template_link + (n + 4 + count_shift).ToString()] = kvitancia_link;
}
Application.DoEvents();
Cancel.Focus();
}
}
Console.WriteLine("Обработано файлов {0} из {1}.", t, dirs.Length);
inbook.Close(false);
progressBar1.Value += 1;
//MessageBox.Show("Обработан файл " + t);
label3.Text = "Обработано " + t + " из " + dirs.Length;
t += 1;
if (stop_cycle == "stop")
{
label3.Text = "Обработка остановлена.";
stop_cycle = "no";
progressBar1.Value = 0;
button3.Enabled = true;
break;
}
}
outbook.Close(true);
app.Visible = false;
app.Quit();
Console.WriteLine();
Console.WriteLine("Шаблон \"{0}\" обновлён.", template);
Console.WriteLine("Нажмите клавишу Enter для выхода...");
Console.Read();
if (label3.Text != "Обработка остановлена.")
{
label3.Text = "Обработка завершена.";
}
}
/// <summary>
/// Считывание разбивки на режимы (используется для формирования R-file) из xlsx файла формата 9.41-сц2
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
public override List <SeparatorIntervals> SeparatorIntervalsReadFromExcel(string filename)
{
//считываем данные из Excel файла в двумерный массив
Excel.Application xlApp = new Excel.Application(); //Excel
Excel.Workbook xlWB; //рабочая книга
Excel.Worksheet xlSht; //лист Excel
xlWB = xlApp.Workbooks.Open(filename); //название файла Excel
int NShts = xlWB.Worksheets.Count;
List <SeparatorIntervals> SeparatorIntervalsList = new List <SeparatorIntervals>();
// xlSht = xlWB.Worksheets[1]; //название листа или 1-й лист в книге xlSht = xlWB.Worksheets[1];
try
{
foreach (Excel.Worksheet sheet in xlWB.Worksheets)
{
SeparatorIntervals separatorIntervals = new SeparatorIntervals();
List <Interval> intervals = new List <Interval>();
int i;
int iLastRow = sheet.Cells[sheet.Rows.Count, "A"].End[Excel.XlDirection.xlUp].Row;
var arrData = (object[, ])sheet.Range["A5:B" + iLastRow].Value; //берём данные с листа Excel
for (i = 1; i < arrData.GetUpperBound(0); i++)
{
double t = (double)arrData[i, 1] * 3_600_000 * 24;
long tbeg = (long)t;
double te = (double)arrData[i + 1, 1] * 3_600_000 * 24;
long tend = (long)te;
Interval I = new Interval(
((string)arrData[i, 2]).Trim(),
tbeg,
tend);
intervals.Add(I);
}
separatorIntervals.Intervals = intervals;
separatorIntervals.Id = sheet.Cells[2, "H"].Value.ToString();
separatorIntervals.tags = new List <string>();
var arrDataTags = (object[, ])sheet.Range["A2:H2"].Value;
for (i = 1; i <= 8; i++)
{
if (arrDataTags[1, i] != null)
{
separatorIntervals.tags.Add(arrDataTags[1, i].ToString());
}
}
separatorIntervals.filename = "NONE!";
SeparatorIntervalsList.Add(separatorIntervals);
}
}
catch (Exception e)
{
MessageBox.Show("SpecialFor9_41_SCENARY4.SeparatorIntervalsReadFromExcel. Ошибка считывания файла " + filename + ": " + e.Message + " Stacktrace:" + e.StackTrace);
}
finally
{
xlWB.Close(false); //закрываем книгу, изменения не сохраняем
xlApp.Quit(); //закрываем Excel
}
return(SeparatorIntervalsList);
}
private void button1_Click(object sender, System.EventArgs e)
{
button1.Enabled = false;
textBox_message.Text = "报表生成中.......";
dateTimePicker_report.Focus();
//获取输入的时间
DateTime dt = dateTimePicker_report.Value;
//用来限定访问数据的范围
string firstDay = dt.Year + "-" + dt.Month + "-" + 1;
string lastDay;
if (dt.Month.Equals(12))
{
lastDay = (dt.Year + 1) + "-" + 1 + "-" + 1;
}
else
{
lastDay = dt.Year + "-" + (dt.Month + 1) + "-" + 1;
}
try
{
// 访问Aras
string url = Properties.Settings.Default.url;
string username = Properties.Settings.Default.username;
string pwd = Properties.Settings.Default.pwd;
string database = Properties.Settings.Default.database;
Item loginItem = IomFactory.CreateHttpServerConnection(url, database, username, pwd).Login();
if (loginItem.isError())
{
MessageBox.Show("访问数据库失败,请重试");
button1.Enabled = true;
textBox_message.Text = String.Empty;
}
else
{
Innovator inn = loginItem.getInnovator();
//EB25E105EFDF478980005105489F6E74表示项目List的cofigId;
Item projectSql = inn.applySQL("SELECT value FROM[IPD].[innovator].VALUE where SOURCE_ID = 'EB25E105EFDF478980005105489F6E74' ");
//D1593C4073EE49218E67A6B9F28CA943表示用户list的configId
Item nameSql = inn.applySQL("SELECT value,label FROM [IPD].[innovator].VALUE a where SOURCE_ID = 'D1593C4073EE49218E67A6B9F28CA943' ");
Dictionary <string, string> nameMap = new Dictionary <string, string>();
for (int i = 0; i < nameSql.getItemCount(); i++)
{
Item item = nameSql.getItemByIndex(i);
string nameE = item.getProperty("value");
string nameC = item.getProperty("label");
if (nameE != null)
{
nameMap[nameE] = nameC;
}
}
Item allDateSql = inn.applySQL("SELECT a.[SOURCE_ID] " +
", a.[PROJECT_NAME] " +
", a.[PROJECT_TIME] " +
", a.[PROJECT_COMMENT] " +
", b.MY_NAME " +
" , b.WORKING_DATE " +
"FROM[IPD].[innovator].[LAUREL_PROJECT_AND_TIME] a " +
"Left Join IPD.innovator.MANHOUR_REGISTER b on b.CONFIG_ID = a.SOURCE_ID " +
"and b.WORKING_DATE > ' " + firstDay + " ' and b.WORKING_DATE <'" + lastDay + "' ");
if (allDateSql.getItemCount() == -1 || allDateSql.getItemCount() == 0)
{
MessageBox.Show("未查询到数据,请重试");
button1.Enabled = true;
textBox_message.Text = String.Empty;
return;
}
//把所有的数据制作成Map
Dictionary <string, List <Item> > dictionary = new Dictionary <string, List <Item> >();
for (int i = 0; i < allDateSql.getItemCount(); i++)
{
Item item = allDateSql.getItemByIndex(i);
string projectName = item.getProperty("project_name");
string myName = item.getProperty("my_name");
if (!string.IsNullOrWhiteSpace(projectName) && !string.IsNullOrWhiteSpace(myName))
{
if (dictionary.ContainsKey(projectName))
{
List <Item> list = dictionary[projectName];
list.Add(item);
}
else
{
List <Item> list = new List <Item>();
list.Add(item);
dictionary[projectName] = list;
}
}
}
//新建Excel和工作簿
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
//启动Excel并获取应用程序对象
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
//遍历所有的名字
for (int i = 0; i < projectSql.getItemCount(); i++)
{
Item item = projectSql.getItemByIndex(i);
string name = item.getProperty("value"); //map制作完成,
//防止list中的项目在Map中不存在
if (dictionary.ContainsKey(name))
{
List <Item> sqlResult = dictionary[name];
if (sqlResult.Count() <= 0)
{
MessageBox.Show(name + "无查询结果");
button1.Enabled = true;
textBox_message.Text = String.Empty;
}
else
{
makeSheet(sqlResult, dt, oXL, oWB, name, nameMap);
}
}
}
oXL.Visible = true;
oXL.UserControl = true;
this.Close();
}
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.InnerException);
errorMessage = String.Concat(errorMessage, theException.Source);
errorMessage = String.Concat(errorMessage, theException.ToString());
errorMessage = String.Concat(errorMessage, theException.TargetSite);
MessageBox.Show(errorMessage, "Error");
}
}
private void Design_IRC_Abutment_Bridges_Girder_Type()
{
string file_path = Get_Project_Folder();
//file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx");
file_path = Path.Combine(file_path, "IRC Abutment Design Girder Type.xlsx");
string copy_path = file_path;
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Girder_Type.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.Application();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"];
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8));
//formatRange.Interior.Color = System.Drawing.
//ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <string> list = new List <string>();
#region Section Input
myExcelWorksheet.get_Range("B4").Formula = txt_Ana_L.Text;
myExcelWorksheet.get_Range("B5").Formula = txt_Ana_og.Text;
myExcelWorksheet.get_Range("B6").Formula = txt_total_weight.Text;
myExcelWorksheet.get_Range("B7").Formula = txt_total_sidl.Text;
#endregion Section Input
DataGridView dgv = dgv_sidl;
int rindx = 0;
#region SIDL Input
//for (int i = 0; i < dgv.RowCount; i++)
//{
// myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[2, i].Value.ToString();
// myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[3, i].Value.ToString();
// myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[4, i].Value.ToString();
//}
#endregion SIDL Input
#region Earth pr
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"];
dgv = dgv_earth_pressure;
for (int i = 0; i < dgv.RowCount; i++)
{
myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString();
}
#endregion Earth pr
#region Base Pressure
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"];
rindx = 0;
dgv = dgv_base_pressure;
List <string> ldbl = new List <string>();
for (int i = 0; i < dgv.RowCount; i++)
{
try
{
if (dgv[1, i].Value.ToString() != "")
{
ldbl.Add(dgv[1, i].Value.ToString());
}
}
catch (Exception exx) { }
}
rindx = 0;
for (int i = 7; i < 55; i++)
{
if (i == 18 ||
i == 19 ||
i == 25 ||
(i >= 28 && i <= 30) ||
i == 35 ||
i == 44 ||
i == 47)
{
continue;
}
else
{
myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString();
}
}
#endregion Earth pr
rindx = 0;
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
iapp.Excel_Open_Message();
}
private void Form1_Load(object sender, EventArgs e)
{
app = new Excel.Application();
app.Visible = false;
workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls");
worksheet = (Excel.Worksheet) workbook.ActiveSheet;
int i = 0;
names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
string current_date = DateTime.Now.ToString("dd.MM");
for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
{
string departament1_value = Convert.ToString(departaments1.Cells[i]);
string departament2_value = Convert.ToString(departaments1.Cells[i]);
string birthday_value = Convert.ToString(departaments1.Cells[i]);
string name_value = Convert.ToString(departaments1.Cells[i]);
if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД")
{
if (birthday_value == current_date)
label1.Text = "Сьогодні свій день народження відзначає " + name_value;
}
}
app.Quit();
}
