public void SpremanjeFaktura()
{
string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx";
//unos podataka
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = theWorkbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
excelApp.Workbooks.Open(OtvorenaFaktura);
var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value;
int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1;
excelApp.Cells[1, 10] = NoviBrojOtpremnice;
excelApp.ActiveWorkbook.Save();
theWorkbook.Close(0);
excelApp.Quit();
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
Process.Start(OtvorenaFaktura);
}
private void cbBuaan_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbBuaan.Text == "Trưa")
{
caan = " Trua";
}
else if (cbBuaan.Text == "Chiều")
{
caan = " Chieu";
}
else if (cbBuaan.Text == "Tối")
{
caan = " Toi";
}
else
{
caan = " Buaphu";
}
foreach (BuaAn ba in buaan)
{
if (ba.ten == cbBuaan.Text)
{
caanid = ba.id;
}
}
APICheckBaoCom = "http://192.84.100.207/MealOrdersAPI/api/DulieuBaoComs/" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + "/" + caanid;
GetBaoCom();
bool Check = CheckData();
if (Check == true)
{
kiemtratrangthai();
GetDataClientChuaUpdateServer();
}
else
{
try
{
Task <string> callTask = Task.Run(() => GetAllCheckBaoCom());
callTask.Wait();
string astr = callTask.Result;
DataTable dt = (DataTable)JsonConvert.DeserializeObject(astr, typeof(DataTable));
if (dt.Rows.Count > 0)
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
FileInfo filename = new FileInfo(pathfile);
Microsoft.Office.Interop.Excel.Application docExcel = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
Microsoft.Office.Interop.Excel.Workbook wb = docExcel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)docExcel.ActiveSheet;
ws.Cells[1, 1] = "id";
ws.Cells[1, 2] = "empid";
ws.Cells[1, 3] = "manhansu";
ws.Cells[1, 4] = "hoten";
ws.Cells[1, 5] = "phongid";
ws.Cells[1, 6] = "phong";
ws.Cells[1, 7] = "banid";
ws.Cells[1, 8] = "ban";
ws.Cells[1, 9] = "congdoanid";
ws.Cells[1, 10] = "congdoan";
ws.Cells[1, 11] = "khach";
ws.Cells[1, 12] = "ngay";
ws.Cells[1, 13] = "thang";
ws.Cells[1, 14] = "nam";
ws.Cells[1, 15] = "userid";
ws.Cells[1, 16] = "thoigiandat";
ws.Cells[1, 17] = "sudung";
ws.Cells[1, 18] = "dangky";
ws.Cells[1, 19] = "thoigiansudung";
ws.Cells[1, 20] = "soxuatandadung";
ws.Cells[1, 21] = "sotiendadung";
ws.Cells[1, 22] = "chot";
ws.Cells[1, 23] = "ghichu";
ws.Cells[1, 24] = "thucdontheobuaid";
ws.Cells[1, 25] = "thucdontheobua";
ws.Cells[1, 26] = "kieudoan";
ws.Cells[1, 27] = "buaanid";
ws.Cells[1, 28] = "buaan";
ws.Cells[1, 29] = "ca";
ws.Cells[1, 30] = "nhaanid";
ws.Cells[1, 31] = "nhaan";
ws.Cells[1, 32] = "loaidouong";
ws.Cells[1, 33] = "thanhtoan";
ws.Cells[1, 34] = "phongrieng";
ws.Cells[1, 35] = "dangkybosung";
ws.Cells[1, 36] = "trangthai1";
ws.Cells[1, 37] = "trangthai2";
var data = new object[dt.Rows.Count, dt.Columns.Count];
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
data[row, column] = dt.Rows[row][column].ToString();
}
}
var startCell = (Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1];
var endCell = (Microsoft.Office.Interop.Excel.Range)ws.Cells[dt.Rows.Count + 1, dt.Columns.Count];
var writeRange = ws.Range[startCell, endCell];
ws.Columns[3].NumberFormat = "@";
ws.Columns[19].NumberFormat = "@";
writeRange.Value2 = data;
wb.SaveAs(filename.FullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges);
wb.Close();
docExcel.Application.Quit();
}
kiemtratrangthai();
}
catch (Exception)
{
MessageBox.Show("Chưa có dữ liệu!");
lvClient.Items.Clear();
lvDongbo.Items.Clear();
lbClient.Text = "Dữ liệu Client : 0";
lbChuadongbo.Text = "Dữ liệu chưa đồng bộ : 0";
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//Проверяем наличие файла
// if (File.Exists(dir+"\\report-4.xls"))
if (File.Exists(dir))
{
//Создаём приложение.
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
//Открываем книгу.
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(dir, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Выбираем таблицу(лист).
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
//Очищаем поля
// textBox1.Clear();
{
//Ищем данные в столбце "С"
Microsoft.Office.Interop.Excel.Range range = ObjWorkSheet.get_Range("AA:AA").Find(textBox1.Text);
//Добавляем текст из нужных ячеек.
textBox2.Text = ObjWorkSheet.get_Range("DP" + range.Row.ToString()).Value2;
textBox3.Text = ObjWorkSheet.get_Range("DQ" + range.Row.ToString()).Value2;
textBox4.Text = ObjWorkSheet.get_Range("DR" + range.Row.ToString()).Value2;
textBox5.Text = ObjWorkSheet.get_Range("BO" + range.Row.ToString()).Value2;
textBox6.Text = ObjWorkSheet.get_Range("CU" + range.Row.ToString()).Value2;
textBox7.Text = ObjWorkSheet.get_Range("DU" + range.Row.ToString()).Value2;
textBox8.Text = ObjWorkSheet.get_Range("DO" + range.Row.ToString()).Value2;
textBox10.Text = ObjWorkSheet.get_Range("AI" + range.Row.ToString()).Value2;
//это чтобы форма прорисовывалась (не подвисала)...
System.Windows.Forms.Application.DoEvents();
}
//Удаляем приложение (выходим из экселя) - ато будет висеть в процессах!
ObjWorkBook.Close();
ObjExcel.Quit();
/*int i = 1;
* int k = 0;
* string stolb;
* excelapp = new Excel.Application();
* excelapp.Visible = false;
* excelappworkbooks = excelapp.Workbooks;
* excelappworkbook = excelapp.Workbooks.Open(Application.StartupPath + @"\template\2.xls",
* Type.Missing, true, Type.Missing,
* "WWWWW", "WWWWW", Type.Missing, Type.Missing, Type.Missing,
* Type.Missing, Type.Missing, Type.Missing, Type.Missing,
* Type.Missing, Type.Missing);
* excelsheets = excelappworkbook.Worksheets;
* excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
* excelcells = excelworksheet.Cells.Find("q", Missing.Value, Missing.Value, Excel.XlLookAt.xlPart, Missing.Value,
* Excel.XlSearchDirection.xlNext,
* Missing.Value, Missing.Value, Missing.Value);
*
* stolb = Convert.ToString(excelcells.Column);
* strok = Convert.ToString(excelcells.Rows.Row);
* System.Windows.Forms.MessageBox.Show(getAdres(Convert.ToInt32(poisk), (Convert.ToInt32(stolb)) - 1));
* excelapp.Quit();*/
}
else
{
MessageBox.Show(
"Файл не выбран или отсутсвует в каталоге", "Ошибка"
);
}
}
private void button1_Click(object sender, EventArgs e)
{
string fname = "";
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Excel File Dialog";
fdlg.Filter = "CSV files (*.csv)|*.csv|Excel Files|*.xls;*.xlsx";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fname = fdlg.FileName;
}
label3.Text = fname;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 2; j <= 2; j++)
{
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
studentEmailFromExcel.Add(xlRange.Cells[i, j].Value2.ToString());
foreach (string x in studentEmailFromExcel)
{
reportStudents.Add(x);
}
}
}
}
List <string> a = new List <string>();
foreach (string stednt in selectedstageinfo)
{
foreach (string reportResult in reportStudents)
{
if (stednt == reportResult)
{
a.Add(stednt);
}
}
}
foreach (var abs in selectedstageinfo)
{
foreach (var attendent in a)
{
if (attendent != abs)
{
abcence.Add(abs);
}
}
}
foreach (string k in abcence)
{
Console.WriteLine(k);
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
if (selectstudy.SelectedItem == null)
{
MessageBox.Show("Please Select a Stage");
}
MessageBox.Show("File Secssfully Added", "File Imported", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
label7.Text = "Жди.....";
this.label7.ForeColor = System.Drawing.Color.Orange;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(Path.Combine(mypath, @"pu\" + textBox1.Text + textBox2.Text + " " + dat + ".xlsx"), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet x = excel.Sheets["mk7d"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
if (checkBox1.Checked == true)
{
x.Cells[9, 2] = "*";
}
else
{
x.Cells[9, 2] = " ";
}
if (checkBox2.Checked == true)
{
x.Cells[13, 2] = "*";
}
else
{
x.Cells[13, 2] = " ";
}
if (checkBox3.Checked == true)
{
x.Cells[22, 2] = "*";
}
else
{
x.Cells[22, 2] = " ";
}
if (checkBox4.Checked == true)
{
x.Cells[32, 2] = "*";
}
else
{
x.Cells[32, 2] = " ";
}
if (checkBox5.Checked == true)
{
x.Cells[35, 2] = "*";
}
else
{
x.Cells[35, 2] = " ";
}
if (checkBox6.Checked == true)
{
x.Cells[49, 2] = "*";
}
else
{
x.Cells[49, 2] = " ";
}
if (checkBox7.Checked == true)
{
x.Cells[64, 2] = "*";
}
else
{
x.Cells[64, 2] = " ";
}
if (checkBox8.Checked == true)
{
x.Cells[71, 2] = "*";
}
else
{
x.Cells[71, 2] = " ";
}
if (checkBox9.Checked == true)
{
x.Cells[81, 2] = "*";
}
else
{
x.Cells[81, 2] = " ";
}
if (checkBox10.Checked == true)
{
x.Cells[93, 2] = "*";
}
else
{
x.Cells[93, 2] = " ";
}
x.Cells[56, 7] = textBox3.Text;
x.Cells[115, 7] = textBox3.Text;
x.Cells[100, 7] = textBox5.Text;
Microsoft.Office.Interop.Excel.Worksheet x2 = excel.Sheets["mk9d"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange2 = x2.UsedRange;
if (checkBox1.Checked == true)
{
x2.Cells[9, 2] = "*";
}
else
{
x2.Cells[9, 2] = " ";
}
if (checkBox2.Checked == true)
{
x.Cells[13, 2] = "*";
}
else
{
x.Cells[13, 2] = " ";
}
if (checkBox3.Checked == true)
{
x2.Cells[22, 2] = "*";
}
else
{
x2.Cells[22, 2] = " ";
}
if (checkBox4.Checked == true)
{
x2.Cells[32, 2] = "*";
}
else
{
x2.Cells[32, 2] = " ";
}
if (checkBox5.Checked == true)
{
x2.Cells[35, 2] = "*";
}
else
{
x2.Cells[35, 2] = " ";
}
if (checkBox6.Checked == true)
{
x2.Cells[49, 2] = "*";
}
else
{
x2.Cells[49, 2] = " ";
}
if (checkBox7.Checked == true)
{
x2.Cells[64, 2] = "*";
}
else
{
x2.Cells[64, 2] = " ";
}
if (checkBox8.Checked == true)
{
x2.Cells[71, 2] = "*";
}
else
{
x2.Cells[71, 2] = " ";
}
if (checkBox9.Checked == true)
{
x2.Cells[81, 2] = "*";
}
else
{
x2.Cells[81, 2] = " ";
}
if (checkBox10.Checked == true)
{
x2.Cells[93, 2] = "*";
}
else
{
x2.Cells[93, 2] = " ";
}
x2.Cells[56, 7] = textBox3.Text;
x2.Cells[115, 7] = textBox3.Text;
x2.Cells[92, 7] = textBox5.Text;
Microsoft.Office.Interop.Excel.Worksheet x3 = excel.Sheets["mk7"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange3 = x3.UsedRange;
if (checkBox1.Checked == true)
{
x3.Cells[9, 2] = "*";
}
else
{
x3.Cells[9, 2] = " ";
}
if (checkBox2.Checked == true)
{
x3.Cells[13, 2] = "*";
}
else
{
x3.Cells[13, 2] = " ";
}
if (checkBox3.Checked == true)
{
x3.Cells[22, 2] = "*";
}
else
{
x3.Cells[22, 2] = " ";
}
if (checkBox4.Checked == true)
{
x3.Cells[32, 2] = "*";
}
else
{
x3.Cells[32, 2] = " ";
}
if (checkBox5.Checked == true)
{
x3.Cells[35, 2] = "*";
}
else
{
x3.Cells[35, 2] = " ";
}
if (checkBox6.Checked == true)
{
x3.Cells[49, 2] = "*";
}
else
{
x3.Cells[49, 2] = " ";
}
if (checkBox7.Checked == true)
{
x3.Cells[64, 2] = "*";
}
else
{
x3.Cells[64, 2] = " ";
}
if (checkBox8.Checked == true)
{
x3.Cells[71, 2] = "*";
}
else
{
x3.Cells[71, 2] = " ";
}
if (checkBox9.Checked == true)
{
x3.Cells[81, 2] = "*";
}
else
{
x3.Cells[81, 2] = " ";
}
if (checkBox10.Checked == true)
{
x3.Cells[93, 2] = "*";
}
else
{
x3.Cells[93, 2] = " ";
}
x3.Cells[56, 7] = textBox3.Text;
x3.Cells[115, 7] = textBox3.Text;
x3.Cells[100, 7] = textBox5.Text;
Microsoft.Office.Interop.Excel.Worksheet x4 = excel.Sheets["mk9"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange4 = x4.UsedRange;
if (checkBox1.Checked == true)
{
x4.Cells[9, 2] = "*";
}
else
{
x4.Cells[9, 2] = " ";
}
if (checkBox2.Checked == true)
{
x4.Cells[13, 2] = "*";
}
else
{
x4.Cells[13, 2] = " ";
}
if (checkBox3.Checked == true)
{
x4.Cells[22, 2] = "*";
}
else
{
x4.Cells[22, 2] = " ";
}
if (checkBox4.Checked == true)
{
x4.Cells[32, 2] = "*";
}
else
{
x4.Cells[32, 2] = " ";
}
if (checkBox5.Checked == true)
{
x4.Cells[35, 2] = "*";
}
else
{
x4.Cells[35, 2] = " ";
}
if (checkBox6.Checked == true)
{
x4.Cells[49, 2] = "*";
}
else
{
x4.Cells[49, 2] = " ";
}
if (checkBox8.Checked == true)
{
x4.Cells[64, 2] = "*";
}
else
{
x4.Cells[64, 2] = " ";
}
if (checkBox9.Checked == true)
{
x4.Cells[74, 2] = "*";
}
else
{
x4.Cells[74, 2] = " ";
}
if (checkBox10.Checked == true)
{
x4.Cells[85, 2] = "*";
}
else
{
x4.Cells[85, 2] = " ";
}
x4.Cells[56, 7] = textBox3.Text;
x4.Cells[115, 7] = textBox3.Text;
x4.Cells[92, 7] = textBox5.Text;
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
label7.Text = "Данные добавлены";
this.label7.ForeColor = System.Drawing.Color.Green;
}
catch { MessageBox.Show("Что-то пошло не так как задумывалось, может название файла не то или фвйл ПУ не создан?", "Ошибонька"); }
}
public static void ReadExistingExcel(dynamic data)
{
string path = @"C:\Users\Meir\Downloads\ABISample\Copy of Boiler Batch OP-42 Form v01,2013 BLANK.XLSX";
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the existing sheets
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("OP-42");
mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv");
// mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
// Edit values for OP-42
mWSheet1.Cells[5, 20] = data.id.ToString();
mWSheet1.Cells[6, 21] = data.date.ToString();
mWSheet1.Cells[8, 7] = data.company.ToString();
mWSheet1.Cells[10, 7] = data.name.ToString();
mWSheet1.Cells[12, 6] = data.id.ToString();
mWSheet1.Cells[12, 9] = data.job.ToString();
mWSheet1.Cells[14, 7] = data.address.ToString();
//mWSheet1.Cells[15, 20] = data.number.ToString();
//mWSheet1.Cells[17, 21] = data.number.ToString();
mWSheet1.Cells[17, 10] = data.date.ToString();
mWSheet1.Cells[16, 5] = data.name_2.ToString();
mWSheet1.Cells[18, 10] = data.phone.ToString();
mWSheet1.Cells[18, 5] = data.email.ToString();
mWSheet1.Cells[30, 10] = data.date.ToString();
// Edit values for csv
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
// bottom data
for (int index = 1; index < 10; index++)
{
mWSheet2.Cells[0 + index, 1] = data.boro.ToString();
mWSheet2.Cells[0 + index, 2] = data.device.ToString();
mWSheet2.Cells[0 + index, 3] = data.md.ToString();
mWSheet2.Cells[0 + index, 4] = data.serial.ToString();
mWSheet2.Cells[0 + index, 5] = data.house.ToString();
mWSheet2.Cells[0 + index, 6] = data.street.ToString();
mWSheet2.Cells[0 + index, 7] = data.block.ToString();
mWSheet2.Cells[0 + index, 8] = data.lot.ToString();
mWSheet2.Cells[0 + index, 9] = data.date.ToString();
mWSheet2.Cells[0 + index, 10] = data.j.ToString();
mWSheet2.Cells[0 + index, 11] = data.k.ToString();
mWSheet2.Cells[0 + index, 12] = data.l.ToString();
mWSheet2.Cells[0 + index, 13] = data.m.ToString();
mWSheet2.Cells[0 + index, 14] = data.n.ToString();
mWSheet2.Cells[0 + index, 15] = data.o.ToString();
mWSheet2.Cells[0 + index, 16] = data.p.ToString();
mWSheet2.Cells[0 + index, 17] = data.q.ToString();
mWSheet2.Cells[0 + index, 18] = data.r.ToString();
mWSheet2.Cells[0 + index, 19] = data.location.ToString();
mWSheet2.Cells[0 + index, 20] = data.t.ToString();
}
SaveFileDialog saveFileDialog2 = new SaveFileDialog
{
Filter = "xlsx files|*.xlsx",
DefaultExt = "xlsx",
AddExtension = true
};
saveFileDialog2.ShowDialog();
string savePath = saveFileDialog2.FileName;
mWorkBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
mWorkBook.Close(true, "newABIExcel", false);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="file_name"></param>
/// <param name="sheet_name"></param>
public static void DataTableToExcel(System.Data.DataTable dt, string file_name, string sheet_name)
{
Microsoft.Office.Interop.Excel.Application Myxls = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook Mywkb = Myxls.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet MySht = (Worksheet)Mywkb.ActiveSheet;
MySht.Name = sheet_name;
Myxls.Visible = false;
Myxls.DisplayAlerts = false;
try
{
//写入表头
object[] arrHeader = new object[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
arrHeader[i] = dt.Columns[i].ColumnName;
}
MySht.Range[MySht.Cells[1, 1], MySht.Cells[1, dt.Columns.Count]].Value2 = arrHeader;
//写入表体数据
object[,] arrBody = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
arrBody[i, j] = dt.Rows[i][j].ToString();
}
}
MySht.Range[MySht.Cells[2, 1], MySht.Cells[dt.Rows.Count + 1, dt.Columns.Count]].Value2 = arrBody;
if (Mywkb != null)
{
Mywkb.SaveAs(file_name);
Mywkb.Close(Type.Missing, Type.Missing, Type.Missing);
Mywkb = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统提示");
}
finally
{
//彻底关闭Excel进程
if (Myxls != null)
{
Myxls.Quit();
try
{
if (Myxls != null)
{
int pid;
GetWindowThreadProcessId(new IntPtr(Myxls.Hwnd), out pid);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(pid);
p.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show("结束当前EXCEL进程失败：" + ex.Message);
}
Myxls = null;
}
GC.Collect();
}
}
protected int ReadEXCELFile()
{
//创建Application对象
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
xApp.Visible = false; //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
xApp.UserControl = true;
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(ViewState["ServerFileFullName"].ToString(),
oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = xBook.Sheets[1];
int index = 1;
ArrayList ResultTemp = new ArrayList();
ArrayList mymatchTemp = new ArrayList();
bool headsign;
int rowindex, colindex, temprowindex;
string text; string[] textresult;
try
{
switch (tableStyle.Items[tableStyle.SelectedIndex].Text)
{
case "段落方式":
myinfo.InnerText = "Excel文件请选择表格方式";
myinfo.Visible = true;
xBook.Close();
xApp.Quit();
return(2);
break;
case "表格方式：第一行是标签":
headsign = true;
for (rowindex = 1; rowindex <= worksheet.UsedRange.Rows.Count; rowindex++)
{
if (worksheet.UsedRange.Rows[rowindex].Cells.Count < 2)
{
continue;
}
if (headsign)
{
temprowindex = 2;
while (temprowindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count)
{
if (worksheet.Cells[rowindex, temprowindex].Value != null)
{
break;
}
temprowindex++;
}
if (temprowindex > worksheet.UsedRange.Rows[rowindex].Cells.Count)
{
continue;
}
headsign = false;
for (colindex = 1; colindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count; colindex++)
{
if (worksheet.Cells[rowindex, colindex].Value != null)
{
mymatchTemp.Add(worksheet.Cells[rowindex, colindex].Value);
}
}
}
else
{
for (temprowindex = 1; temprowindex <= mymatchTemp.Count; temprowindex++)
{
if (worksheet.Cells[rowindex, temprowindex].Value != null)
{
break;
}
}
if (temprowindex > mymatchTemp.Count)
{
continue;
}
for (colindex = 1; colindex <= mymatchTemp.Count; colindex++)
{
ResultTemp.Add(mymatchTemp[colindex - 1]);
temprowindex = rowindex;
//while (worksheet.Cells[temprowindex, colindex].Value == null) temprowindex--;
if (worksheet.Cells[temprowindex, colindex].Value == null)
{
ResultTemp.Add(" ");
}
else
{
ResultTemp.Add(worksheet.Cells[temprowindex, colindex].Value);
}
}
}
}
break;
case "表格方式：第一列是标签":
for (rowindex = 1; rowindex <= worksheet.UsedRange.Rows.Count; rowindex++)
{
if (worksheet.UsedRange.Rows[rowindex].Cells.Count == 1)
{
text = worksheet.UsedRange.Rows[rowindex].Cells[1].Range.Text;
textresult = Regex.Split(text, @"[\r\t^]+([\S ]*)[：:\r]+");
if (textresult.Length >= 2)
{
for (colindex = 0; colindex < textresult.Length; colindex++)
{
ResultTemp.Add(textresult[colindex]);
if (Regex.IsMatch(textresult[colindex], @"[企业主要产品|企业科技需求|企业技术难题]+"))
{
mymatchTemp.Add(textresult[colindex]);
}
}
}
}
else
{
for (colindex = 1; colindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count; colindex++)
{
if (colindex % 2 != 0)
{
mymatchTemp.Add(worksheet.UsedRange.Rows[rowindex].Cells[colindex].Range.Text);
}
ResultTemp.Add(worksheet.UsedRange.Rows[rowindex].Cells[colindex].Range.Text);
}
}
}
break;
}
result = new string[ResultTemp.Count];
mymatch = new string[mymatchTemp.Count];
for (index = 0; index < ResultTemp.Count; index++)
{
result[index] = ResultTemp[index].ToString();
}
for (index = 0; index < mymatchTemp.Count; index++)
{
mymatch[index] = mymatchTemp[index].ToString();
}
xBook.Close();
xApp.Quit();
return(1);
}
catch (Exception ex)
{
myinfo.InnerText = "出错了：请检查:1,多个数据之间格式是否相同；2,是否选择了正确的表格识别插入方式；3，表格是否规范（是否有未使用的单元格）";
myinfo.Visible = true;
xBook.Close();
xApp.Quit();
return(2);
}
}
private static void UpdateExcelForDynamics(DataTable dt)
{
//Instance reference for Excel Application
Microsoft.Office.Interop.Excel.Application objXL = null;
//Workbook refrence
Microsoft.Office.Interop.Excel.Workbook objWB = null;
DataSet ds = new DataSet();
try
{
string dynamicsFilePath = @ConfigurationManager.AppSettings["DynamicsExcelLocation"];
objXL = new Microsoft.Office.Interop.Excel.Application();
objWB = objXL.Workbooks.Open(dynamicsFilePath);//Your path to excel file.
// Only retrieve first worksheet with new fields
Microsoft.Office.Interop.Excel.Worksheet NewFieldsWorksheet = objWB.Worksheets[1];
// setting range for second row
Excel.Range xlRange = NewFieldsWorksheet.UsedRange;
int rowNumber = xlRange.Rows.Count + 1;
string prefix = ConfigurationManager.AppSettings["Prefix"];
string Entity = ConfigurationManager.AppSettings["EntityName"];
foreach (DataRow dtRow in dt.Rows)
{
string salesforceType = dtRow["Type"].ToString();
//Common fields
NewFieldsWorksheet.Cells[rowNumber, 2] = dtRow["Label"].ToString(); // Label Name
NewFieldsWorksheet.Cells[rowNumber, 3] = GetSchemaWithPrefix(dtRow["Label"].ToString()); // schema
NewFieldsWorksheet.Cells[rowNumber, 4] = GetDynamicsType(dtRow["Type"].ToString()); // field type
NewFieldsWorksheet.Cells[rowNumber, 5] = Entity; // entity
NewFieldsWorksheet.Cells[rowNumber, 7] = GetDynamicsRequired(dtRow["IsRequired"].ToString()); // field type
// Special Set Up based on Type
if (dtRow["Type"].ToString() == "STRING" || dtRow["Type"].ToString() == "TEXTAREA")
{
var lenght = int.Parse(dtRow["Length"].ToString());
NewFieldsWorksheet.Cells[rowNumber, 12] = dtRow["Length"].ToString(); // If text - Lenght
NewFieldsWorksheet.Cells[rowNumber, 13] = "Text";
if (lenght > 200 && lenght < 501) // Add Format text area
{
NewFieldsWorksheet.Cells[rowNumber, 13] = "Text Area";
}
if (lenght > 500) // For tet areas greater than 500
{
NewFieldsWorksheet.Cells[rowNumber, 4] = "Multiple lines of text";
}
}
if (dtRow["Type"].ToString() == "PHONE")
{
NewFieldsWorksheet.Cells[rowNumber, 12] = dtRow["Length"].ToString(); // If text - Lenght
NewFieldsWorksheet.Cells[rowNumber, 13] = "Phone";
}
if (dtRow["Type"].ToString() == "EMAIL")
{
NewFieldsWorksheet.Cells[rowNumber, 12] = dtRow["Length"].ToString(); // If text - Lenght
NewFieldsWorksheet.Cells[rowNumber, 13] = "Email";
}
if (dtRow["Type"].ToString() == "URL")
{
NewFieldsWorksheet.Cells[rowNumber, 12] = dtRow["Length"].ToString(); // If text - Lenght
NewFieldsWorksheet.Cells[rowNumber, 13] = "URL";
}
if (dtRow["Type"].ToString() == "DATE")
{
NewFieldsWorksheet.Cells[rowNumber, 40] = "Date Only";
}
if (dtRow["Type"].ToString() == "DATETIME")
{
NewFieldsWorksheet.Cells[rowNumber, 40] = "Date and Time";
}
if (dtRow["Type"].ToString() == "CURRENCY")
{
NewFieldsWorksheet.Cells[rowNumber, 35] = "2";
NewFieldsWorksheet.Cells[rowNumber, 36] = "-922337203685477";
NewFieldsWorksheet.Cells[rowNumber, 37] = "922337203685477";
}
if (dtRow["Type"].ToString() == "DOUBLE")
{
NewFieldsWorksheet.Cells[rowNumber, 27] = "2";
NewFieldsWorksheet.Cells[rowNumber, 28] = "0";
NewFieldsWorksheet.Cells[rowNumber, 29] = "1000000000";
}
if (dtRow["Type"].ToString() == "PERCENT")
{
NewFieldsWorksheet.Cells[rowNumber, 31] = "2";
NewFieldsWorksheet.Cells[rowNumber, 32] = "-100000000000";
NewFieldsWorksheet.Cells[rowNumber, 33] = "100000000000";
}
if (dtRow["Type"].ToString() == "BOOLEAN")
{
NewFieldsWorksheet.Cells[rowNumber, 20].Style.WrapText = true;
NewFieldsWorksheet.Cells[rowNumber, 20] = "0:False\r\n1:True";
}
if (dtRow["Type"].ToString() == "TIME")
{
NewFieldsWorksheet.Cells[rowNumber, 23] = "Timezone";
}
if (dtRow["Type"].ToString() == "PICKLIST" || dtRow["Type"].ToString() == "MULTIPICKLIST")
{
string[] stringSeparators = new string[] { "\r\n" };
string linebreak = "\r\n";
string optionlist = "";
string[] list = dtRow["PicklistValues"].ToString().Split(stringSeparators, StringSplitOptions.None);
int value = 100000000;
foreach (string option in list)
{
string OptionLine = value.ToString() + ":" + option + linebreak;
value++;
if (!string.IsNullOrEmpty(option))
{
optionlist += OptionLine;
}
}
NewFieldsWorksheet.Cells[rowNumber, 16].Style.WrapText = true;
NewFieldsWorksheet.Cells[rowNumber, 16] = optionlist;
NewFieldsWorksheet.Cells[rowNumber, 17] = "No";
}
if (salesforceType.Contains("REFERENCE"))
{
int initial = salesforceType.IndexOf("(") + 1;
int end = salesforceType.IndexOf(")");
string entityrelated = salesforceType.Substring(initial).Replace("__c)", "");
//string Cleanname = entityrelated.Replace("_", " ");
entityrelated = entityrelated.Replace(")", "").ToLower();
NewFieldsWorksheet.Cells[rowNumber, 42] = entityrelated;
NewFieldsWorksheet.Cells[rowNumber, 48] = "10000";
}
rowNumber++;
}
//// Disable file override confirmaton message
objXL.DisplayAlerts = false;
objWB.SaveAs(dynamicsFilePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
objWB.Close();
objXL.Quit();
Marshal.ReleaseComObject(NewFieldsWorksheet);
Marshal.ReleaseComObject(objWB);
Marshal.ReleaseComObject(objXL);
}
catch (Exception ex)
{
objWB.Saved = true;
//Closing work book
objWB.Close();
//Closing excel application
objXL.Quit();
}
}
public static DataSet TryDatasetforSpecificWorksheet()
{
//Instance reference for Excel Application
Microsoft.Office.Interop.Excel.Application objXL = null;
//Workbook refrence
Microsoft.Office.Interop.Excel.Workbook objWB = null;
DataSet ds = new DataSet();
try
{
objXL = new Microsoft.Office.Interop.Excel.Application();
string SalesforceFilePath = @ConfigurationManager.AppSettings["SalesforceExcelLocation"];
objWB = objXL.Workbooks.Open(SalesforceFilePath);//Your path to excel file.
// Only retrieve second worksheet with mapping information from field dump
Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[2];
//foreach (Microsoft.Office.Interop.Excel.Worksheet objSHT in objWB.Worksheets)
//{
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 3;
//If 1st Row Contains unique Headers for datatable include this part else remove it
//Start
for (int c = 1; c <= cols; c++)
{
int rowForHeaders = 3; // Marking columns headers
string colname = objSHT.Cells[rowForHeaders, c].Text;
string trimcolname = colname.Replace(" ", "");
dt.Columns.Add(trimcolname);
noofrow = rowForHeaders + 1;// Mark new row # in order to get information
}
//END
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Text;
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
//}
//Closing workbook
objWB.Close();
//Closing excel application
objXL.Quit();
return(ds);
}
catch (Exception ex)
{
objWB.Saved = true;
//Closing work book
objWB.Close();
//Closing excel application
objXL.Quit();
//Response.Write("Illegal permission");
return(ds);
}
}
private void Button1_Click(object sender, EventArgs e)
{
string name = tbiName.Text;
string nhaSanXuat = cbiManufacturer.SelectedValue.ToString();
string menhGia = cbiValue.SelectedValue.ToString();
string ngayNhap = diDate.Value.ToShortDateString();
string trangthai = cbiStatus.SelectedValue.ToString();
OpenFileDialog openFileDialog1 = new OpenFileDialog {
InitialDirectory = @"D:\",
Title = "Browse Text Files",
CheckFileExists = true,
CheckPathExists = true,
DefaultExt = "xlsx",
Filter = "xlsx files (*.xlsx)|*.xlsx",
FilterIndex = 2,
RestoreDirectory = true,
ReadOnlyChecked = true,
ShowReadOnly = true
};
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(openFileDialog1.FileName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string id = "";
string seri = "";
con.Open();
try {
SqlDataAdapter adapter = new SqlDataAdapter();
for (int i = 1; i <= rowCount; i++)
{
id = xlRange.Cells[i, 1].Value2.ToString();
seri = xlRange.Cells[i, 2].Value2.ToString();
string sql = sql = "insert into Main (ID,NAME,SERI,NHASANXUAT,MENHGIA,NGAYNHAP,TRANGTHAI) values(N'" + id + "',N'" + name + "',N'" + seri + "'," + nhaSanXuat + "," + menhGia + ",CAST(N'" + ngayNhap + "' AS Date)," + trangthai + ")";
adapter.InsertCommand = new SqlCommand(sql, con);
adapter.InsertCommand.ExecuteNonQuery();
}
MessageBox.Show("thanh cong");
} catch (Exception ex) {
MessageBox.Show(ex.ToString());
} finally {
con.Close();
//cleanup
loadDataGridView();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
/* string res = "C:\\Users\\twimy\\OneDrive\\Desktop\\FINKI\\iSolve\\C# Word templates\\Form.xlsx";
* Excel.Application xlApp;
* Excel.Workbook xlWorkBook;
* Excel.Worksheet xlWorkSheet;
*
* xlApp = new Excel.Application();
* xlWorkBook = xlApp.Workbooks.Open(res, 0, true, 5, "", "", true);
* xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
*/
Microsoft.Office.Interop.Word.Application app = new Microsoft.Office.Interop.Word.Application();
//dokumenti excel per numrimin e reshtave...
//string exceldoc = "C:\\Users\\twimy\\OneDrive\\Desktop\\FINKI\\iSolve\\C# Word templates\\Form.xlsx";
string exceldoc = textBox1.Text;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(exceldoc);
Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
int countRecords = userRange.Rows.Count;
//int add = countRecords +1;
//x.Cells[add, 1] = "Total Rows "+countRecords;
//progress bar
this.progressBar1.Maximum = 100;
//dokumenti template
Microsoft.Office.Interop.Word.Document doc = null;
object fileName = textBox2.Text;
//object fileName = "C:\\Users\\twimy\\OneDrive\\Desktop\\FINKI\\iSolve\\C# Word templates\\Template.docx";
object missing = Type.Missing;
for (int i = 0; i < countRecords; i++)
{
doc = app.Documents.Open(fileName, missing, missing);
app.Selection.Find.ClearFormatting();
app.Selection.Find.Replacement.ClearFormatting();
string[] tmp = new string[4];
tmp = readExcel(i);
app.Selection.Find.Execute("<ID>", missing, missing, missing, missing, missing, missing, missing, missing, tmp[0], 2);
app.Selection.Find.Execute("<name>", missing, missing, missing, missing, missing, missing, missing, missing, tmp[1], 2);
app.Selection.Find.Execute("<sex>", missing, missing, missing, missing, missing, missing, missing, missing, tmp[2], 2);
app.Selection.Find.Execute("<age>", missing, missing, missing, missing, missing, missing, missing, missing, tmp[3], 2);
object SaveAsFile = (object)textBox3.Text + "\\Report" + tmp[0] + ".doc";
doc.SaveAs2(SaveAsFile, missing, missing, missing);
this.progressBar1.Value += (100 / countRecords);
}
this.progressBar1.Value = 100;
MessageBox.Show("Fajllat u krijuan me sukses!");
doc.Close(false, missing, missing);
app.Quit(false, false, false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
private void BatchUpdater()
{
Loan loan = EncompassApplication.CurrentLoan;
//EncompassApplication.Session.Start()
//EncompassApplication.CurrentLoan.Session.StartInstance()
//var sessionStart = new EncompassApplication.CurrentLoan.Session;
Session sessionStart = new Session();
sessionStart.Start("https://TEBE11147866.ea.elliemae.net$TEBE11147866", "admin", "Y0uT@lk!ngT0M3?");
String currentUser = EncompassApplication.Session.UserID;
string repAddress = loan.Fields["CX.PC.BATCH.NAME"].Value.ToString() + ".csv";
//Excel prep and call
Microsoft.Office.Interop.Excel.Application userApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook userWorkbook = userApp.Workbooks.Open(@"\\ftwfs02\Groups\LLS-Scanned\Bulk Updating\Encompass Bulk Update Templates\" + repAddress);
Microsoft.Office.Interop.Excel._Worksheet userWorksheet = userWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range userRange = userWorksheet.UsedRange;
//userWorksheet.Cells.NumberFormat = "General";
try
{
//set shortcut for loan call
//Loan loan = EncompassApplication.CurrentLoan;
//row/column setup
int rCnt = 1;
int cCnt = 1;
int rowCount = userRange.Rows.Count;
int colCount = userRange.Columns.Count;
List <BatchUpdatePC> bUpdate = CDO.BatchUpdatePC.ToList();
string r1c2string = "";
string r1c3string = "";
string r1c4string = "";
string r1c5string = "";
string r1c6string = "";
//var batchT = loan.Fields["CX.PC.BATCH.TYPE"].Value;
//string batchType = batchT.ToString();
DateTime dateRan = new DateTime();
dateRan = DateTime.Now;
double colData1 = 0;
string colDatSt = colData1.ToString();
cCnt++;
var r1c2 = userWorksheet.Cells[rCnt, cCnt].Value2;
r1c2string = r1c2.ToString();
cCnt++;
var r1c3 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c3 != null)
{
r1c3string = r1c3.ToString();
cCnt++;
}
else
{
r1c3string = "null";
cCnt++;
}
var r1c4 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c4 != null)
{
r1c4string = r1c4.ToString();
cCnt++;
}
else
{
r1c4string = "null";
cCnt++;
}
var r1c5 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c5 != null)
{
r1c5string = r1c5.ToString();
cCnt++;
}
else
{
r1c5string = "";
cCnt++;
}
var r1c6 = userWorksheet.Cells[rCnt, cCnt].Value2;
if (r1c6 != null)
{
r1c6string = r1c6.ToString();
cCnt++;
}
else
{
r1c6string = "";
cCnt++;
}
cCnt = 1;
foreach (BatchUpdatePC item in bUpdate)
{
if (item.RepName.ToLower() == loan.Fields["CX.PC.BATCH.NAME"].Value.ToString().ToLower())
{
if (r1c2string.ToLower() != item.Col2.ToLower() | r1c3string.ToLower() != item.Col3.ToLower() | r1c4string.ToLower() != item.Col4.ToLower() | r1c5string.ToLower() != item.Col5.ToLower())
{
MessageBox.Show("This does not appear to be the correct format for this report. Please review each column in the Excel spreadsheet for accuracy.");
loan.Fields["CX.PC.BATCH.IRON.MOUNTAIN"].Value = "Fail";
}
else
{
rCnt = 2;
cCnt = 2;
var colData2 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData2string = "";
cCnt++;
var colData3 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData3string = "";
cCnt++;
var colData4 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData4string = "";
cCnt++;
var colData5 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData5string = "";
cCnt++;
var colData6 = userWorksheet.Cells[rCnt, cCnt].Text;
string colData6string = "";
cCnt = 1;
for (rCnt = 2; rCnt <= rowCount; rCnt++)
{
if (userWorksheet.Cells[rCnt, cCnt].Value2 is null)
{
break;
}
colData1 = (double)userWorksheet.Cells[rCnt, cCnt].Value2;
colDatSt = colData1.ToString();
if (colData1.ToString().Substring(0, 1) == "6")
{
colDatSt = "00" + colDatSt;
colData1 = Convert.ToDouble(colDatSt);
}
cCnt++;
colData2 = userWorksheet.Cells[rCnt, cCnt].Text;
colData2string = colData2.ToString();
//Additional columns as needed
cCnt++;
colData3 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData3 != "")
{
colData3string = colData3.ToString();
cCnt++;
}
colData4 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData4 != "")
{
colData4string = colData4.ToString();
cCnt++;
}
colData5 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData5 != "")
{
colData5string = colData5.ToString();
cCnt++;
}
colData6 = userWorksheet.Cells[rCnt, cCnt].Text;
if (colData6 != "")
{
colData6string = colData6.ToString();
cCnt++;
}
cCnt = 1;
StringFieldCriterion cri = new StringFieldCriterion();
cri.FieldName = "Loan.LoanNumber";
cri.Value = colDatSt;
BatchUpdate batch = new BatchUpdate(cri);
batch.Fields.Add(r1c2string, colData2string);
if (colData3string != "")
{
batch.Fields.Add(r1c3string, colData3string);
}
if (colData4string != "")
{
batch.Fields.Add(r1c4string, colData4string);
}
if (colData5string != "")
{
batch.Fields.Add(r1c5string, colData5string);
}
if (colData6string != "")
{
batch.Fields.Add(r1c6string, colData6string);
}
batch.Fields.Add("CX.PC.BATCH.COLL.TRACK", currentUser);
batch.Fields.Add("CX.PC.BATCH.COL.DATE", dateRan);
loan.Fields["CX.PC.BATCH.IRON.MOUNTAIN"].Value = "Fail";
EllieMae.Encompass.Automation.EncompassApplication.Session.Loans.SubmitBatchUpdate(batch);
loan.Fields["CX.PC.BATCH.IRON.MOUNTAIN"].Value = "Success";
}
}
}
}
}
catch (Exception)
{
MessageBox.Show("Something went wrong. Please click YES to save on the next popup, rebuild the spreadsheet and run the Batch Update again.");
}
finally
{
string fileName = @"\\ftwfs02\Groups\LLS-Scanned\Bulk Updating\Encompass Bulk Update Templates\" + repAddress;
//string folder = System.IO.Path.GetDirectoryName(fileName);
//if (System.IO.Directory.Exists(folder))
//{
//MessageBox.Show(loan.Fields["CX.PC.BATCH.IRON.MOUNTAIN"].Value.ToString());
userWorkbook.Close(true, fileName, null);
//}
userApp.Quit();
Marshal.ReleaseComObject(userWorksheet);
Marshal.ReleaseComObject(userWorkbook);
Marshal.ReleaseComObject(userApp);
sessionStart.End();
}
//userRange.Delete(XlDeleteShiftDirection.xlShiftUp);
//string fileName = @"H:\Encompass Support\Batch_Updater2.csv";
//string folder = System.IO.Path.GetDirectoryName(fileName);
//if (System.IO.Directory.Exists(folder))
//{
// userWorkbook.Close(true, fileName, null);
//}
//userApp.Quit();
//Marshal.ReleaseComObject(userWorksheet);
//Marshal.ReleaseComObject(userWorkbook);
//Marshal.ReleaseComObject(userApp);
//newSession.End();
}
//Блок печати
private void button3_Click(object sender, EventArgs e)
{
string nas = textBox14.Text;
string ind = textBox15.Text;
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(Path.Combine(mypath, @"pu\" + nas + ind + " " + dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy") + ".xlsx"), ReadOnly: false);
if (radioButton1.Checked == true) //9
{
if (radioButton6.Checked == true) //дорн
{
excel.ActiveWorkbook.PrintOutEx(6, 7);
excel.ActiveWorkbook.PrintOutEx(22, 22);
excel.ActiveWorkbook.PrintOutEx(29, 31);
excel.ActiveWorkbook.PrintOutEx(34, 35);
}
if (radioButton5.Checked == true)//без дорна
{
excel.ActiveWorkbook.PrintOutEx(16, 17);
excel.ActiveWorkbook.PrintOutEx(22, 22);
excel.ActiveWorkbook.PrintOutEx(30, 31);
excel.ActiveWorkbook.PrintOutEx(34, 35);
}
}
if (radioButton2.Checked == true) //7
{
if (radioButton6.Checked == true) //дорн
{
excel.ActiveWorkbook.PrintOutEx(1, 2);
excel.ActiveWorkbook.PrintOutEx(22, 22);
excel.ActiveWorkbook.PrintOutEx(29, 32);
excel.ActiveWorkbook.PrintOutEx(34, 35);
}
if (radioButton5.Checked == true)//без дорна
{
excel.ActiveWorkbook.PrintOutEx(11, 12);
excel.ActiveWorkbook.PrintOutEx(22, 22);
excel.ActiveWorkbook.PrintOutEx(30, 32);
excel.ActiveWorkbook.PrintOutEx(34, 35);
}
}
if (radioButton8.Checked == true)//30гр
{
excel.ActiveWorkbook.PrintOutEx(23, 23);
excel.ActiveWorkbook.PrintOutEx(46, 46);
}
if (radioButton7.Checked == true) //без 30гр
{
excel.ActiveWorkbook.PrintOutEx(24, 24);
excel.ActiveWorkbook.PrintOutEx(47, 47);
}
if (radioButton10.Checked == true)//2 фаски
{
excel.ActiveWorkbook.PrintOutEx(33, 33);
}
if (radioButton4.Checked == true) //большая выточка
{
if (radioButton6.Checked == true) //дорн
{
excel.ActiveWorkbook.PrintOutEx(25, 25);
}
if (radioButton5.Checked == true)//без дорна
{
excel.ActiveWorkbook.PrintOutEx(26, 26);
}
}
if (radioButton3.Checked == true) //маленькая выточка
{
if (radioButton6.Checked == true) //дорн
{
excel.ActiveWorkbook.PrintOutEx(27, 27);
}
if (radioButton5.Checked == true)//без дорна
{
excel.ActiveWorkbook.PrintOutEx(26, 28);
}
}
if (radioButton2.Checked == true) //7
{
if (radioButton4.Checked == true) //большая выточка
{
excel.ActiveWorkbook.PrintOutEx(36, 36);
}
if (radioButton3.Checked == true)//маленькая выточка
{
excel.ActiveWorkbook.PrintOutEx(38, 38);
}
}
if (radioButton1.Checked == true) //9
{
if (radioButton4.Checked == true) //большая выточка
{
excel.ActiveWorkbook.PrintOutEx(37, 37);
}
if (radioButton3.Checked == true)//маленькая выточка
{
excel.ActiveWorkbook.PrintOutEx(39, 39);
}
}
if (radioButton2.Checked == true) //7
{
if (radioButton11.Checked == true) //без супфины
{
excel.ActiveWorkbook.PrintOutEx(40, 41);
excel.ActiveWorkbook.PrintOutEx(45, 45);
}
if (radioButton12.Checked == true)//супфина
{
excel.ActiveWorkbook.PrintOutEx(43, 43);
excel.ActiveWorkbook.PrintOutEx(45, 45);
}
}
if (radioButton1.Checked == true) //9
{
if (radioButton11.Checked == true) //без супфины
{
excel.ActiveWorkbook.PrintOutEx(40, 40);
excel.ActiveWorkbook.PrintOutEx(42, 42);
}
if (radioButton12.Checked == true)//супфина
{
excel.ActiveWorkbook.PrintOutEx(44, 44);
}
}
MessageBox.Show("Голоса в моей голове и логика программы приказали мне перепутать все листы при выводе на печать. Страдай.", "Как-то так");
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
catch { MessageBox.Show("Что-то пошло не так.", "Ошибонька"); }
}
private void button1_Click(object sender, EventArgs e)//Создание файла ПУ
{
try
{
label18.Text = "Жди.....";
this.label18.ForeColor = System.Drawing.Color.Orange;
//Основные переменные
string nas = textBox14.Text;
string ind = textBox15.Text;
string raz = textBox17.Text;
string prov = textBox16.Text;
string g = textBox1.Text; //Габаритный размер
string k2 = textBox2.Text; //Размер до калибра 2
string k22 = Convert.ToString(Convert.ToDouble(k2) - 0.176); //Размер до калибра 2,2
string dku90 = textBox3.Text; //До кромки угла 90
string k2d = textBox4.Text; //От калибра 2 до дна колодца
string k22d = Convert.ToString(Convert.ToDouble(k2d) + 0.176); //От калибра 2,2 до дна колодца
string ts = textBox5.Text; //Толщина стенки
string rn = textBox7.Text; //Радиус носика
string sn = textBox8.Text; //Скругление плечей и носика
string k625n = textBox6.Text; //От клибра 6,25 до носика
string gtp = textBox9.Text; //Глубина топливоподвода
string dtp = textBox11.Text; //Диаметр топливоподвода
string gk = textBox10.Text; //Глубина до кармана
string dk = textBox13.Text; //Диаметр колодца без дорна
string rd = textBox12.Text; //Радиус дорна
//Припуски
double prnamicron = 0.15;
double prnapulem = 0.15;
double prnasfer = 0.3;
double prnaradnos = 0.3;
double prnauva = 0.07;
double diamkolpoddorn = 0.7;
try
{
con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Ts-03\\users\\OGK\\Кокшаров С.А\\db\\DB.accdb;Jet OLEDB:Database Password=6567604";
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
string query = "SELECT prnamicron, prnapulem, prnasfer, prnaradnos, prnauva, diamkolpoddorn from pripuski";
cmd.CommandText = query;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
prnamicron = Convert.ToDouble(reader[0]);
prnapulem = Convert.ToDouble(reader[1]);
prnasfer = Convert.ToDouble(reader[2]);
prnaradnos = Convert.ToDouble(reader[3]);
prnauva = Convert.ToDouble(reader[4]);
diamkolpoddorn = Convert.ToDouble(reader[5]);
}
con.Close();
}
catch (Exception) { MessageBox.Show("Ошибка", "Ошибка"); }
//Rika
double grika = Convert.ToDouble(g) + prnamicron + prnapulem + prnasfer; //Габаритный размер для рики
double dk85 = Convert.ToDouble(grika) - (Convert.ToDouble(k625n) + 0.41); //От торца до калибра 8,5
double dkr94 = Convert.ToDouble(k625n) + 0.56; //От края носика до кромки диаметра 9,4
double rnr = Convert.ToDouble(rn) + prnaradnos; //Радиус носика Rika
double snr = Convert.ToDouble(sn); //Скругление плечей и носика Rika
//Micron
double dk22m = Convert.ToDouble(k22) + prnapulem - 0.02; //От торца до калибра 2,2 Micron
double dk34m = Convert.ToDouble(dk22m) - 0.81; //От торца до калибра 3,4(0,81 разница между калибрами) Micron
double ddkz = Convert.ToDouble(dk22m) + 1.46; //От торца до дна колодца заготовки (детали под дорн)
double ddkd = Convert.ToDouble(k22d) + prnauva + dk22m; //От торца до дна колодца заготовки (детали без дорна)
double k22ddk = Convert.ToDouble(k22d) + prnauva; //От калибра 2,2 до дна колодца (детали без дорна)
double dkmd = diamkolpoddorn; //Диаметр колодца с дорном
double dkm = Convert.ToDouble(dk); //Диаметр колодца без дорна
//Дорнование
double k22ddkdorn = Convert.ToDouble(k22d) + prnauva; //От калибра 2,2 до дна колодца заготовки (детали под дорн)
double rddorn = Convert.ToDouble(rd); //Радиус дорна
//Топляк штифты
double gtps = Convert.ToDouble(gtp) + prnapulem; //Глубина топливоподвода
double dtps = Convert.ToDouble(dtp); //Диаметр топливоподвода
//Карман
double gke = Convert.ToDouble(gk) + prnapulem;//Глубина кармана
//
//Вставка в документ
//
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open((Path.Combine(mypath, @"mkrasp.xlsx")), ReadOnly: false);
//МК 7-ка дорн
Microsoft.Office.Interop.Excel.Worksheet m1 = excel.Sheets["mk7d"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRangem1 = m1.UsedRange;
m1.Cells[1, 8] = nas;
m1.Cells[2, 8] = ind;
m1.Cells[57, 8] = raz;
m1.Cells[58, 8] = prov;
m1.Cells[57, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
m1.Cells[58, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
//МК 9-ка дорн
Microsoft.Office.Interop.Excel.Worksheet m2 = excel.Sheets["mk9d"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRangem2 = m2.UsedRange;
m2.Cells[1, 8] = nas;
m2.Cells[2, 8] = ind;
m2.Cells[57, 8] = raz;
m2.Cells[58, 8] = prov;
m2.Cells[57, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
m2.Cells[58, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
//МК 7-ка без дорна
Microsoft.Office.Interop.Excel.Worksheet m3 = excel.Sheets["mk7"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRangem3 = m3.UsedRange;
m3.Cells[1, 8] = nas;
m3.Cells[2, 8] = ind;
m3.Cells[57, 8] = raz;
m3.Cells[58, 8] = prov;
m3.Cells[57, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
m3.Cells[58, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
//МК 9-ка без дорна
Microsoft.Office.Interop.Excel.Worksheet m4 = excel.Sheets["mk9"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRangem4 = m4.UsedRange;
m4.Cells[1, 8] = nas;
m4.Cells[2, 8] = ind;
m4.Cells[57, 8] = raz;
m4.Cells[58, 8] = prov;
m4.Cells[57, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
m4.Cells[58, 10] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
//010
Microsoft.Office.Interop.Excel.Worksheet x = excel.Sheets["010"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
x.Cells[58, 1] = nas;
x.Cells[59, 1] = ind;
x.Cells[58, 15] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
x.Cells[59, 15] = dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy");
x.Cells[58, 11] = raz;
x.Cells[59, 11] = prov;
//020-1(С углом 30)
Microsoft.Office.Interop.Excel.Worksheet x1 = excel.Sheets["020-1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange1 = x1.UsedRange;
x1.Cells[14, 11] = dkr94;
x1.Cells[16, 11] = snr;
x1.Cells[26, 11] = rnr;
x1.Cells[28, 4] = dk85;
//020-1(Без угла 30)
Microsoft.Office.Interop.Excel.Worksheet x2 = excel.Sheets["020-2"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange2 = x2.UsedRange;
x2.Cells[14, 11] = dkr94;
x2.Cells[16, 11] = snr;
x2.Cells[26, 11] = rnr;
x2.Cells[28, 4] = dk85;
//030-1(Большая выточка под штангу, заготовка под дорн)
Microsoft.Office.Interop.Excel.Worksheet x3 = excel.Sheets["030-1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange3 = x3.UsedRange;
x3.Cells[18, 1] = dkmd;
x3.Cells[23, 4] = dk34m;
x3.Cells[24, 6] = dk22m;
x3.Cells[26, 6] = ddkz;
//030-2(Большая выточка под штангу, заготовка без дорна)
Microsoft.Office.Interop.Excel.Worksheet x4 = excel.Sheets["030-2"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange4 = x4.UsedRange;
x4.Cells[18, 1] = dkm;
x4.Cells[23, 4] = dk34m;
x4.Cells[24, 6] = dk22m;
x4.Cells[26, 6] = ddkz;
x4.Cells[11, 2] = k22ddk;
//030-3(Маленькая выточка, заготовка под дорн)
Microsoft.Office.Interop.Excel.Worksheet x5 = excel.Sheets["030-3"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange5 = x5.UsedRange;
x5.Cells[18, 1] = dkmd;
x5.Cells[23, 4] = dk34m;
x5.Cells[24, 6] = dk22m;
x5.Cells[26, 6] = ddkz;
//030-4(Маленькая выточка, заготовка без дорна)
Microsoft.Office.Interop.Excel.Worksheet x6 = excel.Sheets["030-4"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange6 = x6.UsedRange;
x6.Cells[18, 1] = dkm;
x6.Cells[23, 4] = dk34m;
x6.Cells[24, 6] = dk22m;
x6.Cells[26, 6] = ddkz;
x6.Cells[11, 2] = k22ddk;
//041(Дорн)
Microsoft.Office.Interop.Excel.Worksheet x11 = excel.Sheets["041"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange11 = x11.UsedRange;
x11.Cells[20, 13] = k22ddkdorn;
x11.Cells[9, 11] = rd;
//045(Топливоподвод)
Microsoft.Office.Interop.Excel.Worksheet x12 = excel.Sheets["045"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange12 = x12.UsedRange;
x12.Cells[11, 2] = gtps;
x12.Cells[12, 4] = dtps;
//110(Карман)
Microsoft.Office.Interop.Excel.Worksheet x13 = excel.Sheets["110"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange13 = x13.UsedRange;
x13.Cells[25, 3] = gke;
//135-1(Большая выточка 7-ка)
Microsoft.Office.Interop.Excel.Worksheet x7 = excel.Sheets["135-1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange7 = x7.UsedRange;
if (rddorn == 0)
{
x7.Cells[16, 1] = "Ø" + dk;
}
else
{
x7.Cells[16, 1] = "R" + rddorn;
}
x7.Cells[22, 2] = k22ddk;
x7.Cells[21, 4] = dk34m;
x7.Cells[22, 5] = dk22m;
x7.Cells[8, 9] = gke;
x7.Cells[15, 11] = dtps;
//135-2(Большая выточка 9-ка)
Microsoft.Office.Interop.Excel.Worksheet x8 = excel.Sheets["135-2"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange8 = x8.UsedRange;
if (rddorn == 0)
{
x8.Cells[16, 1] = "Ø" + dk;
}
else
{
x8.Cells[16, 1] = "R" + rddorn;
}
x8.Cells[22, 2] = k22ddk;
x8.Cells[21, 4] = dk34m;
x8.Cells[22, 5] = dk22m;
x8.Cells[8, 9] = gke;
x8.Cells[15, 11] = dtps;
//135-3(Маленькая выточка 7-ка)
Microsoft.Office.Interop.Excel.Worksheet x9 = excel.Sheets["135-3"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange9 = x9.UsedRange;
if (rddorn == 0)
{
x9.Cells[16, 1] = "Ø" + dk;
}
else
{
x9.Cells[16, 1] = "R" + rddorn;
}
x9.Cells[22, 2] = k22ddk;
x9.Cells[21, 4] = dk34m;
x9.Cells[22, 5] = dk22m;
x9.Cells[8, 9] = gke;
x9.Cells[15, 11] = dtps;
//135-4(Маленькая выточка 9-ка)
Microsoft.Office.Interop.Excel.Worksheet x10 = excel.Sheets["135-4"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange10 = x10.UsedRange;
if (rddorn == 0)
{
x10.Cells[16, 1] = "Ø" + dk;
}
else
{
x10.Cells[16, 1] = "R" + rddorn;
}
x10.Cells[22, 2] = k22ddk;
x10.Cells[21, 4] = dk34m;
x10.Cells[22, 5] = dk22m;
x10.Cells[8, 9] = gke;
x10.Cells[15, 11] = dtps;
//175 (Пулемет)
Microsoft.Office.Interop.Excel.Worksheet x14 = excel.Sheets["175"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange14 = x14.UsedRange;
x14.Cells[18, 7] = Convert.ToDouble(dk22m) - 0.15;
//200-1 (Сфера 30гр)
Microsoft.Office.Interop.Excel.Worksheet x15 = excel.Sheets["200-1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange15 = x15.UsedRange;
x15.Cells[21, 6] = Convert.ToDouble(g) - 0.02;
x15.Cells[21, 13] = sn;
x15.Cells[19, 13] = rn;
x15.Cells[10, 9] = ts;
x15.Cells[12, 9] = k625n;
//200-2 (Сфера без 30гр)
Microsoft.Office.Interop.Excel.Worksheet x16 = excel.Sheets["200-2"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange16 = x16.UsedRange;
x16.Cells[21, 6] = Convert.ToDouble(g) - 0.02;
x16.Cells[21, 13] = sn;
x16.Cells[19, 13] = rn;
x16.Cells[10, 9] = ts;
x16.Cells[12, 9] = k625n;
sheet.Close(true, (Path.Combine(mypath, @"pu\" + nas + ind + " " + dateTimePicker1.Value.ToString("dd'.'MM'.'yyyy") + ".xlsx")), Type.Missing);
excel.Quit();
//Вставка наладка Rika30
Microsoft.Office.Interop.Excel.Application excel2 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet2 = excel2.Workbooks.Open((Path.Combine(mypath, @"rika30\Rika30.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r1 = excel2.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger1 = r1.UsedRange;
r1.Cells[3, 3] = grika;
r1.Cells[3, 2] = dk85;
r1.Cells[3, 5] = rnr;
r1.Cells[3, 4] = snr;
sheet2.Close(true, Type.Missing, Type.Missing);
excel2.Quit();
//Вставка наладка Rika
Microsoft.Office.Interop.Excel.Application excel4 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet4 = excel4.Workbooks.Open((Path.Combine(mypath, @"rika\Rika.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r4 = excel4.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger4 = r4.UsedRange;
r4.Cells[3, 3] = grika;
r4.Cells[3, 2] = dk85;
r4.Cells[3, 5] = rnr;
r4.Cells[3, 4] = snr;
sheet4.Close(true, Type.Missing, Type.Missing);
excel4.Quit();
//Вставка трафарет Rika30
Microsoft.Office.Interop.Excel.Application excel3 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet3 = excel3.Workbooks.Open((Path.Combine(mypath, @"rika30\Trafrika30.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r3 = excel3.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger3 = r3.UsedRange;
r3.Cells[5, 2] = k625n;
r3.Cells[5, 4] = snr;
r3.Cells[5, 6] = rnr;
sheet3.Close(true, Type.Missing, Type.Missing);
excel3.Quit();
//Вставка трафарет Rika
Microsoft.Office.Interop.Excel.Application excel5 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet5 = excel5.Workbooks.Open((Path.Combine(mypath, @"rika\Trafrika.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r5 = excel5.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger5 = r5.UsedRange;
r5.Cells[5, 2] = k625n;
r5.Cells[5, 4] = snr;
r5.Cells[5, 6] = rnr;
sheet5.Close(true, Type.Missing, Type.Missing);
excel5.Quit();
//Вставка трафарет окончательной шлифовки
Microsoft.Office.Interop.Excel.Application excel6 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet6 = excel6.Workbooks.Open((Path.Combine(mypath, @"okshlif\okshlif.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r6 = excel6.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger6 = r6.UsedRange;
r6.Cells[5, 2] = k625n;
r6.Cells[5, 4] = sn;
r6.Cells[5, 6] = rn;
sheet6.Close(true, Type.Missing, Type.Missing);
excel6.Quit();
//Вставка трафарет окончательной шлифовки
Microsoft.Office.Interop.Excel.Application excel7 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet7 = excel7.Workbooks.Open((Path.Combine(mypath, @"okshlif30\okshlif30.xlsx")), ReadOnly: false);
Microsoft.Office.Interop.Excel.Worksheet r7 = excel7.Sheets["Лист1"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRanger7 = r7.UsedRange;
r7.Cells[5, 2] = k625n;
r7.Cells[5, 4] = sn;
r7.Cells[5, 6] = rn;
sheet7.Close(true, Type.Missing, Type.Missing);
excel7.Quit();
label18.Text = "ПУ создан";
this.label18.ForeColor = System.Drawing.Color.Green;
button3.Enabled = true;
button4.Enabled = true;
button5.Enabled = true;
button7.Enabled = true;
}
catch
{ MessageBox.Show("А в полях точно все цифры?", "Ошибонька"); }
}
public static void AddDataToExcel(string SampleID, DateTime SampleDate, string PatientID, double Age, double[] BioData, int rowCount, string FilePath)
{
string path = FilePath;
//xlApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(path);
//Microsoft.Office.Interop.Excel._Worksheet sheet = excelWorkbook.Sheets[1];
//TrimRows(sheet);
//var LastRow = sheet.UsedRange.Rows.Count;
//LastRow = LastRow + sheet.UsedRange.Row - 1;
//for (int i = 1; i <= LastRow; i++)
//{
// //if (application.WorksheetFunction.CountA(sheet.Rows[i]) == 0)
// // (sheet.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete();
//}
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("biomarkers + added parameters");
//TrimRows(mWSheet1);
//Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
//int colCount = range.Columns.Count;
//int rowCount = range.Rows.Count;
rowCount = rowCount + 1;
mWSheet1.Cells[rowCount + 1, 1] = rowCount; //DigiWest ID
mWSheet1.Cells[rowCount + 1, 2] = SampleID; //SampleID
mWSheet1.Cells[rowCount + 1, 3] = ""; //Patient ID IOTA
mWSheet1.Cells[rowCount + 1, 4] = ""; //IOTA Score
mWSheet1.Cells[rowCount + 1, 5] = ""; //Certain or Uncertain
mWSheet1.Cells[rowCount + 1, 6] = SampleDate; //SampleDate
mWSheet1.Cells[rowCount + 1, 7] = ""; //DateOfBirth
mWSheet1.Cells[rowCount + 1, 8] = Age; //Age
mWSheet1.Cells[rowCount + 1, 9] = ""; //CA-125
mWSheet1.Cells[rowCount + 1, 10] = ""; //NeoOva Score
mWSheet1.Cells[rowCount + 1, 11] = BioData[0]; //Neopro1
mWSheet1.Cells[rowCount + 1, 12] = BioData[1]; //Neopro2
mWSheet1.Cells[rowCount + 1, 13] = BioData[2]; //Neopro3
mWSheet1.Cells[rowCount + 1, 14] = BioData[3]; //Neopro4
mWSheet1.Cells[rowCount + 1, 15] = BioData[4]; //Neopro5
mWSheet1.Cells[rowCount + 1, 16] = BioData[5]; //Neopro6
mWSheet1.Cells[rowCount + 1, 17] = PatientID; //PatientID
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void button1_Click(object sender, EventArgs e)
{
string connectionString = "";
string tmpcondi_Clus = "";
Object selectedItem = comboBoxClust.SelectedItem;
string seleClust = selectedItem.ToString();
switch (seleClust)
{
case "PIRAEUS": // For future use
tmpcondi_Clus = "and substring(t1.appnm,1,4) = 'PIRA' and substring(t1.termid,1,2) <> '73' ";
ExportName = "Piraeus_";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OutputDirectory + ExportName + timestamp + ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;';";
break;
case "EPOS":
tmpcondi_Clus = "and substring(t1.appnm,1,4) = 'EPOS' and substring(t1.termid,1,2) = '73' ";
ExportName = "EPOS_";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OutputDirectory + ExportName + timestamp + ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;';";
break;
case "EPOS_PIRAEUS":
tmpcondi_Clus = "and substring(t1.appnm,1,4) = 'EPOS' and t1.CLUSTERID in ('EPOS_PIRAEUS','EPOS_PIRAEUS_EPP') ";
ExportName = "EPOS_Piraeus_";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OutputDirectory + ExportName + timestamp + ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;';";
break;
case "ALL":
tmpcondi_Clus = "and substring(t1.appnm,1,4) = 'EPOS'";
ExportName = "ALL_EPOS_";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OutputDirectory + ExportName + timestamp + ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;';";
break;
}
Cursor.Current = Cursors.WaitCursor;
string sqlSelQuery =
"SELECT " +
"distinct t1.termid as TID, " +
"case (substring(t1.appnm,1,4)) " +
"when 'EPOS' then 'EPOS' " +
"when 'PIRA' then 'PIRAEUS BANK' " +
"else '????????' " +
"end as ClusterType, " +
"t1.CLUSTERID, " +
"t1.famnm as [TERMINAL TYPE], " +
"t2.PARNAMELOC, " +
"t2.value " +
"FROM " +
"vc30.relation t1 FULL OUTER JOIN vc30.PARAMETER t2 ON t1.TERMID = t2.PARTID " +
"where " +
"t2.PARNAMELOC in ( " +
templist +
" ) " +
"and t1.acccnt = -1 " +
"and len(t1.appnm) = 9 " +
tmpcondi_Clus +
//"and substring(t1.appnm,1,4) = 'EPOS' " +
//"and substring(t1.termid,1,2) = '73' " +
"and substring(t1.termid,1,1) <> ('T') " +
"order by t1.termid, t2.PARNAMELOC ";
//Select records so to write to excel
OleDbCommand sqlcmdSel_Excel = new OleDbCommand();
sqlcmdSel_Excel.Connection = VCConn;
sqlcmdSel_Excel.CommandTimeout = 0;
sqlcmdSel_Excel.CommandType = CommandType.Text;
sqlcmdSel_Excel.CommandText = sqlSelQuery;
OleDbDataReader rdrSelExcel = sqlcmdSel_Excel.ExecuteReader();
OleDbCommand dbCmd = new OleDbCommand("CREATE TABLE [VC Report] ([TID] char(255),[ClusterType] char(255),[CLUSTERID] char(255),[TERMINAL TYPE] char(255),[PARNAMELOC] char(255),[VALUE] char(255))");
OleDbConnection myConnection = new OleDbConnection(connectionString);
myConnection.Open();
dbCmd.Connection = myConnection;
dbCmd.ExecuteNonQuery();
while (rdrSelExcel.Read())
{
OleDbCommand myCommand = new OleDbCommand("Insert into [VC Report] ([TID],[ClusterType],[CLUSTERID],[TERMINAL TYPE],[PARNAMELOC],[VALUE]) " +
" values ('" + rdrSelExcel.GetValue(0).ToString() + "','" + rdrSelExcel.GetValue(1).ToString() + "','" + rdrSelExcel.GetValue(2).ToString() + "','" + rdrSelExcel.GetValue(3).ToString() + "','"
+ rdrSelExcel.GetValue(4).ToString() + "','" + rdrSelExcel.GetValue(5).ToString().Replace('\'', ' ') + "')");
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();
}
myConnection.Close();
dbCmd.Dispose();
rdrSelExcel.Close();
string spreadSheetVCLocation = @"\\GRAT1-FPS-SV2O\Common\POS Support\" + ExportName + timestamp + ".xlsx";
Microsoft.Office.Interop.Excel.Application excel_ = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel_.Workbooks.Open(spreadSheetVCLocation, 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);
Microsoft.Office.Interop.Excel.Worksheet x = excel_.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
// Autofit
x.Columns.AutoFit();
//Bold
x.get_Range("A1", "I1").EntireRow.Font.Bold = true;
sheet.Close(true, Type.Missing, Type.Missing);
excel_.Quit();
listView_Parm.Clear();
listView_Parm.Columns.Add("Parameter");
MessageBox.Show("Export finished!");
Cursor.Current = Cursors.Default;
}
private void WriteExcelData()
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Title = "Save REPORT File";
saveFile.DefaultExt = "xlsx";
saveFile.Filter = "Excel (*.xlsx)|*.xlsx";
saveFile.FilterIndex = 0;
saveFile.RestoreDirectory = true;
string path = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
if (saveFile.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range Range2 = null;
object missing = Type.Missing;
Random Rnd = new Random();
int RndNum = Rnd.Next();
byte[] Template = Properties.Resources.Report;
File.WriteAllBytes(Path.GetTempPath() + RndNum + ".xlsx", Template);
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.DisplayAlerts = false;
wb = excel.Workbooks.Open(Path.GetTempPath() + RndNum + ".xlsx",
missing, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing);
// 원천기술 분석
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.get_Item("Page 5");
ws.PageSetup.Zoom = false;
int cntTechnologyLevel = mainFrm.CriticalTechnology1.Count;
for (int i = 0; i < cntTechnologyLevel; i++)
{
string[] temp = new string[] { mainFrm.CriticalTechnology1[i],
string.Empty,
mainFrm.CriticalTechnology2[i] + "%",
mainFrm.CriticalTechnology3[i] + "%",
mainFrm.CriticalTechnology4[i],
mainFrm.CriticalTechnology5[i],
mainFrm.CriticalTechnology6[i],
mainFrm.CriticalTechnology7[i],
mainFrm.CriticalTechnology8[i],
mainFrm.CriticalTechnology9[i] };
Range2 = ws.Range["B" + (15 + i), "K" + (15 + i)];
Range2.Value = temp;
}
// 기술 수준 파악은 최대가 4개임
if (cntTechnologyLevel < 4)
{
for (int i = 4; i > cntTechnologyLevel; i--)
{
Range2 = ws.Range["B" + (15 + i - 1), "K" + (15 + i - 1)];
Range2.EntireRow.Delete();
}
}
int cntTechnology = mainFrm.OrigrinalTechnology1.Count;
// 원천기술 식별은 5개가 Default이며, 5개가 초과할 경우에는 Row를 Insert하고
if (cntTechnology > 5)
{
for (int i = 0; i < cntTechnology - 5; i++)
{
Range2 = ws.Range["B6", "K6"];
Range2.Insert();
}
}
else if (cntTechnology < 5) // 5개 보다 작을때는 그만큼 Row를 삭제해야함.
{
for (int i = 5; i > cntTechnology; i--)
{
Range2 = ws.Range["B" + (5 + i - 1), "K" + (5 + i - 1)];
Range2.EntireRow.Delete();
}
}
for (int i = 0; i < cntTechnology; i++)
{
string[] temp = new string[] { mainFrm.OrigrinalTechnology1[i], mainFrm.OrigrinalTechnology2[i] };
Range2 = ws.Range["B" + (5 + i), "K" + (5 + i)];
Range2.Value = temp;
}
wb.SaveAs(saveFile.FileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
wb.Close(false, missing, missing);
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
wb.Close(false, missing, missing);
excel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Range2);
excel = null;
wb = null;
ws = null;
Range2 = null;
}
}
}
internal static void exportarInventarioCompleto()
{
if ((classGeneradorDeReportes.pathPlantillaInventarios == "") || classGeneradorDeReportes.pathPlantillaInventarios == null)
{
classGeneradorDeReportes.getpaths();
}
//Creamos un objeto misvalue para facilitar las configuraciones.
object misValue = System.Reflection.Missing.Value;
//Creamos nuestro DataTable
DataTable tablaParaExportacion = new DataTable();
string selectInventario = "select " +
"a.clave " +
", a.cliente " +
", a.material " +
", a.calibre " +
", a.color " +
", a.corte " +
", a.orientacion " +
", a.perfil " +
", a.frecuencia " +
", a.amplitud " +
", a.lubricante " +
", a.mazos " +
", a.kilos " +
", DATE_FORMAT(a.fechaDeFabricacion, '%d/%m/%Y') as fechaDeFabricacion " +
", a.lote " +
", a.fila " +
", b.nombre " +
", DATE_FORMAT(a.fecha_de_registro, '%d/%m/%Y') as fecha_de_registro " +
", DATE_FORMAT(a.fecha_de_modificacion, '%d/%m/%Y') as fecha_de_modificacion" +
", DATE_FORMAT(a.fecha_de_recepcion_de_material, '%d/%m/%Y') as fecha_de_recepcion_de_material " +
", a.diametro_mazo " +
", c.nombre as presentacion " +
", a.comentarios as Comentarios " +
", a.pedidopmm as Pedido " +
"from lu_productos a " +
"left join lu_almacenes b on a.id_almacen=b.id_almacen " +
"left join cf_presentaciones c on a.id_presentacion = c.id_presentacion " +
"where a.mazos<>0; ";
//"inner join lu_almacenes b on a.id_almacen=b.id_almacen where a.mazos<>0; ";
tablaParaExportacion = Database.runSelectQuery(selectInventario);
//En caso de devolver una tabla nula, debemos construirla de nuevo para evitar errores.
if (tablaParaExportacion == null)
{
tablaParaExportacion = new DataTable();
}
//Creamos una nueva aplicación de excel.
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//Abrimos la plantilla de reportes y creamos un nuevo workbook para mostrar ahí el reporte.
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(classGeneradorDeReportes.pathPlantillaInventarios);
Microsoft.Office.Interop.Excel.Workbook workbookExportacion;
//Obtenemos todas las hojas de la plantilla
Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets;
//Obtenemos la primera hoja de la plantilla
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = xlApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Se copia la hoja actual y se coloca automáticamente en un nuevo workbook.
xlWorkSheet.Copy(Type.Missing, Type.Missing);
//Cerramos la plantilla original de excel.
xlApp.DisplayAlerts = false;
xlWorkBook.Save();
xlWorkBook.Close(0);
//Comenzamos a trabajar con el workbook de exportación
//Asignamos un identificador para nuestro workbook
workbookExportacion = xlApp.ActiveWorkbook;
Microsoft.Office.Interop.Excel.Sheets sheetsExportacion = workbookExportacion.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheetExportacion = xlApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Primeras 10 columnas de la tabla
//Copiamos la tabla de exportación al portaPapeles
ClassUtilidades.CopyDataTableToClipboard(tablaParaExportacion);
Microsoft.Office.Interop.Excel.Range CR = sheetExportacion.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range;
CR.Select();
//string textos = Clipboard.GetText();
sheetExportacion.Paste(CR);
Microsoft.Office.Interop.Excel.Range formatRange = sheetExportacion.get_Range("L1", "M1");
formatRange.EntireColumn.NumberFormat = "0.00";
Microsoft.Office.Interop.Excel.Range formatRange2 = sheetExportacion.get_Range("P1", "P1");
formatRange2.EntireColumn.NumberFormat = "0";
Microsoft.Office.Interop.Excel.Range formatRange3 = sheetExportacion.get_Range("N1", "N1");
formatRange3.EntireColumn.NumberFormat = "dd/mm/yyyy";
Microsoft.Office.Interop.Excel.Range formatRange4 = sheetExportacion.get_Range("R1", "T1");
formatRange4.EntireColumn.NumberFormat = "dd/mm/yyyy";
formatRange.NumberFormat = "dd/mm/yyyy";
//Iteramos para poner el formato correcto de número en las celdas.
for (int x = 0; x < tablaParaExportacion.Rows.Count; x++)
{
DateTime fecha = DateTime.Parse(tablaParaExportacion.Rows[x]["fechaDeFabricacion"].ToString());
sheetExportacion.Cells[1 + (x + 1), 14] = fecha.ToOADate();
DateTime fecha_de_recepcion = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_recepcion_de_material"].ToString());
sheetExportacion.Cells[1 + (x + 1), 18] = fecha_de_recepcion.ToOADate();
DateTime fecha_de_registro = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_registro"].ToString());
sheetExportacion.Cells[1 + (x + 1), 19] = fecha_de_registro.ToOADate();
DateTime fecha_de_modificacion = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_modificacion"].ToString());
sheetExportacion.Cells[1 + (x + 1), 20] = fecha_de_modificacion.ToOADate();
sheetExportacion.Cells[2 + x, 12] = tablaParaExportacion.Rows[x]["mazos"].ToString();
sheetExportacion.Cells[2 + x, 13] = tablaParaExportacion.Rows[x]["kilos"].ToString();
sheetExportacion.Cells[2 + x, 16] = tablaParaExportacion.Rows[x]["fila"].ToString();
}
//Últimas columnas de la tabla
//Copiamos la tabla de exportación al portaPapeles
/*classDataBaseManager.CopyDataTableToClipboard(tablaParaExportacion2);
* CR = sheetExportacion.Cells[5, 11] as Microsoft.Office.Interop.Excel.Range;
* CR.Select();
* sheetExportacion.Paste(CR, Clipboard.GetText());
*/
int cantidadRegistros = tablaParaExportacion.Rows.Count;
int ultimaCelda = (cantidadRegistros) + 4;
//
//Establecemos los márgenes para la impresión.
sheetExportacion.PageSetup.PrintArea = "A1:M" + ultimaCelda;
Microsoft.Office.Interop.Excel.Range aRange = sheetExportacion.get_Range("A5", "M" + ultimaCelda);
aRange.Rows.AutoFit();
xlApp.DisplayAlerts = false;
//Colocamos los bordes de las celdas
sheetExportacion.Range[sheetExportacion.Cells[5, 1], sheetExportacion.Cells[ultimaCelda + 5, 13]].borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
sheetExportacion.Range[sheetExportacion.Cells[5, 1], sheetExportacion.Cells[ultimaCelda + 5, 13]].borders.Weight = 2d;
string rutaPDF = System.Windows.Forms.Application.StartupPath + "\\ultimoReporte.pdf";
//MessageBox.Show("Guardado en " + rutaPDF);
sheetExportacion.ExportAsFixedFormat(
Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
rutaPDF,
Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
true,
false,
Type.Missing,
Type.Missing,
false);
xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
xlApp.Visible = true;
//Cerramos el Excel para mostrar solo el PDF.
//ClassUtilidades.matarProcesoDeExcel(xlApp);
//Mostramos el PDF
//ClassUtilidades.verPDF(rutaPDF);
}
static void Main(string[] args)
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\db\Database11.accdb; Persist Security Info=False;";
string sql = @"select * from Users where SocialNumber = '12345543211234'";
object id = null;
object name = null;
object birthDate = null;
object PhoneNumber = null;
object Address = null;
object SocialNumber = null;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
// выводим названия столбцов
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}", reader.GetName(0), reader.GetName(1), reader.GetName(2), reader.GetName(3), reader.GetName(4), reader.GetName(5));
while (reader.Read()) // построчно считываем данные
{
id = reader.GetValue(0);
name = reader.GetValue(1);
birthDate = reader.GetValue(2);
PhoneNumber = reader.GetValue(3);
Address = reader.GetValue(4);
SocialNumber = reader.GetValue(5);
Console.WriteLine("{0} \t{1} \t{2} \t{3} \t{4} \t{5}", id, name, birthDate, PhoneNumber, Address, SocialNumber);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(@"C:\Users\User\Documents\Visual Studio 2010\Projects\ConsoleApplication1\Test\Template\example.xlsx");
Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
x.Cells[3, 2] = id;
x.Cells[4, 2] = name;
x.Cells[5, 2] = birthDate;
x.Cells[6, 2] = PhoneNumber;
x.Cells[7, 2] = Address;
x.Cells[8, 2] = SocialNumber;
x.Cells[4, 4] = id;
x.Cells[4, 5] = name;
x.Cells[4, 6] = birthDate;
x.Cells[4, 7] = PhoneNumber;
x.Cells[4, 8] = Address;
x.Cells[4, 9] = SocialNumber;
sheet.SaveAs(@"C:\Users\User\Documents\Visual Studio 2010\Projects\Test\ConsoleApplication1\Result\Example.xlsx");
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
Console.Read();
}
}
/// <summary>
/// 将DataTable追加到EXCEL页上
/// </summary>
/// <param name="FilePath">文件路径</param>
/// <param name="SheetName">页名</param>
/// <param name="dt">数据表</param>
/// <param name="Cover">根据数据表名称，是否覆盖EXCEL的页</param>
private void appendTable2Excel(String FilePath, String SheetName, DataTable dt, bool Cover)
{
if (!File.Exists(FilePath))
{
throw new Exception("EXCEL文件路径不存在！请检查此路径是否正确：" + FilePath);
}
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open(FilePath);
excel.Visible = false;
//关闭提示框，关联到删除sheet、关闭EXCEL进程
excel.DisplayAlerts = false;
for (int i = 0, len = wb.Sheets.Count; i < len; i++)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(i + 1);
if (s.Name.Equals(SheetName) && Cover)
{
s.Delete();
len--;
}
else if (s.Name.Equals(SheetName) && !Cover)
{
return;
}
}
int maxIndex = wb.Sheets.Count;
Microsoft.Office.Interop.Excel.Worksheet lastSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(maxIndex);
wb.Worksheets.Add(System.Reflection.Missing.Value, lastSheet, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet newSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(maxIndex + 1);
newSheet.Name = SheetName;
for (int i = 0, len = dt.Columns.Count; i < len; i++)
{
Microsoft.Office.Interop.Excel.Range range = newSheet.Cells[1, i + 1];
range.NumberFormatLocal = "@";
newSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
for (int i = 0, len = dt.Rows.Count; i < len; i++)
{
for (int j = 0, len2 = dt.Columns.Count; j < len2; j++)
{
Microsoft.Office.Interop.Excel.Range range = newSheet.Cells[i + 2, j + 1];
range.NumberFormatLocal = "@";
newSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
wb.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
catch (Exception ex)
{
if (null != wb)
{
wb.Close(false, Type.Missing, Type.Missing);
}
if (null != excel)
{
excel.Quit();
}
throw ex;
}
}
public void append(string filePath, int index2, User user)
{
int index = 3;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(filePath, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"", true, false, 0, true, false, false);
xlBigSheet = xlWorkBook.Worksheets;
string x = "Sheet1";
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(1);
xlWorksheet.Cells[index, 1] = user.name;
xlWorksheet.Cells[index, 2] = user.tname;
xlWorksheet.Cells[index, 3] = user.login;
xlWorksheet.Cells[index, 4] = user.pass;
xlWorksheet.Cells[index, 5] = user.question;
xlWorksheet.Cells[index, 6] = user.answer;
xlWorksheet.Cells[index, 7] = user.date_reg;
xlWorksheet.Cells[index, 8] = user.prog_id;
xlWorksheet.Cells[index, 9] = user.prog_pass;
xlWorksheet.Cells[index, 10] = user.callback_url;
xlWorkBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue,
misValue, misValue);
xlWorkBook.Close(misValue, misValue, misValue);
xlWorkBook = null;
xlApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
//Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//if (xlApp == null)
//{
// throw new Exception("Excel is not properly installed!!");
//}
//xlApp = new Excel.Application();
//xlWorkbook = xlApp.Workbooks.Open(filePath);
//xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
//xlRange = xlWorksheet.UsedRange;
//if (xlWorksheet.Cells[index, 3].Value2.ToString().Trim() != "")
// index++;
//xlWorksheet.Cells[index, 1] = user.name;
//xlWorksheet.Cells[index, 2] = user.tname;
//xlWorksheet.Cells[index, 3] = user.login;
//xlWorksheet.Cells[index, 4] = user.pass;
//xlWorksheet.Cells[index, 5] = user.question;
//xlWorksheet.Cells[index, 6] = user.answer;
//xlWorksheet.Cells[index, 7] = user.date_reg;
//xlWorksheet.Cells[index, 8] = user.prog_id;
//xlWorksheet.Cells[index, 9] = user.prog_pass;
//xlWorksheet.Cells[index, 10] = user.callback_url;
//xlWorkbook.Save();
//xlWorkbook.Close(true);
}
public void Print(object sender, EventArgs e)
{
if (((FrmMAIN)this.MdiParent).ActiveMdiChild == this)
{
if (dgvCost.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
try
{
int i, j;
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "SaveSalesCost";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add();
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int k = 1; k < dgvCost.ColumnCount; k++)
{
xlWorkSheet.Cells[1, k] = dgvCost.Columns[k].HeaderText.ToString();
}
for (i = 0; i < dgvCost.RowCount; i++)
{
for (j = 0; j < dgvCost.ColumnCount - 1; j++)
{
//if (j == 3)
// continue;
if (dgvCost[j, i].Value != null)
{
xlWorkSheet.Cells[i + 2, j + 1] = dgvCost[j, i].Value.ToString();
}
}
}
xlWorkBook.SaveAs(saveFileDialog1.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlWorkBook.Close(true);
xlApp.Quit();
MessageBox.Show("출력되었습니다.", "출력 완료", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception err)
{
MessageBox.Show("출력에 실패하였습니다.", "출력 실패", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (xlApp != null)
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//Вводимые переменные
//string ind;
string dat = Convert.ToString(dateTimePicker1);
string zad = textBox1.Text;
string nrvs = textBox2.Text;
string nkr = textBox3.Text;
string nig = textBox4.Text;
string hodig = textBox5.Text;
string hodigv = textBox6.Text;
string hodign = textBox7.Text;
string vhvost = textBox10.Text;
string vhvostv = textBox9.Text;
string vhvostn = textBox8.Text;
string prnahod = textBox13.Text;
string prnahodv = textBox12.Text;
string prnahodn = textBox11.Text;
string gidropl = textBox14.Text;
string mfv = textBox15.Text;
string mfn = textBox16.Text;
string zag = textBox17.Text;
string procpolir = textBox18.Text;
string procdrosel = textBox19.Text;
string pmarkbosch = textBox26.Text;
string kmarkbosch = textBox25.Text;
string pmarkazpi = textBox24.Text;
string kmarkazpi = textBox23.Text;
string uvp1 = maskedTextBox1.Text;
string uvp2 = maskedTextBox2.Text;
string uvp3 = maskedTextBox3.Text;
string uvp4 = maskedTextBox4.Text;
string uvp5 = maskedTextBox5.Text;
string uvp6 = maskedTextBox6.Text;
string uvp7 = maskedTextBox7.Text;
string uvp8 = maskedTextBox8.Text;
string uvp9 = maskedTextBox9.Text;
string uvp10 = maskedTextBox10.Text;
string uvsh1 = maskedTextBox11.Text;
string uvsh2 = maskedTextBox12.Text;
string uvsh3 = maskedTextBox13.Text;
string uvsh4 = maskedTextBox14.Text;
string uvsh5 = maskedTextBox15.Text;
string uvsh6 = maskedTextBox16.Text;
string uvsh7 = maskedTextBox17.Text;
string uvsh8 = maskedTextBox18.Text;
string uvsh9 = maskedTextBox19.Text;
string uvsh10 = maskedTextBox20.Text;
double ki1 = 0;
double ki2 = 0;
double ki3 = 0;
double ki4 = 0;
double ki5 = 0;
double ki6 = 0;
double ki7 = 0;
double ki8 = 0;
double ki9 = 0;
double ki10 = 0;
string diamotv = textBox54.Text;
if (textBox15.TextLength == 0 || textBox16.TextLength == 0)
{
MessageBox.Show("Введите mf");
return;
}
//Вычисляемые переменные
double pmfrvs1 = Convert.ToDouble(mfn);
double pmfrvs2 = Convert.ToDouble(mfv);
double ptrvs1 = 0.202 / pmfrvs1;
double ptrvs2 = 0.202 / pmfrvs2;
double pqrvs1 = Convert.ToDouble(xurma(pmfrvs1));
double pqrvs2 = Convert.ToDouble(xurma(pmfrvs2));
double pmfpol1 = pmfrvs1 * (Convert.ToDouble(procdrosel) / 100) + pmfrvs1;
double pmfpol2 = pmfrvs2 * (Convert.ToDouble(procdrosel) / 100) + pmfrvs2;
double ptpol1 = 0.202 / pmfpol1;
double ptpol2 = 0.202 / pmfpol2;
double pqpol1 = Convert.ToDouble(xurma(pmfpol1));
double pqpol2 = Convert.ToDouble(xurma(pmfpol2));
double pmfpr1 = pmfpol1 - (pmfpol1 * (Convert.ToDouble(procpolir) / 100));
double pmfpr2 = pmfpol2 - (pmfpol2 * (Convert.ToDouble(procpolir) / 100));
double ptpr1 = 0.202 / pmfpr1;
double ptpr2 = 0.202 / pmfpr2;
double pqpr1 = Convert.ToDouble(xurma(pmfpr1));
double pqpr2 = Convert.ToDouble(xurma(pmfpr2));
double raz = 0.176;
double raz2 = 0.395;
if (radioButton5.Checked)
{
if (textBox20.Text != "")
{
ki1 = Convert.ToDouble(textBox20.Text) - raz2;
}
if (textBox21.Text != "")
{
ki2 = Convert.ToDouble(textBox21.Text) - raz2;
}
if (textBox22.Text != "")
{
ki3 = Convert.ToDouble(textBox22.Text) - raz2;
}
if (textBox27.Text != "")
{
ki4 = Convert.ToDouble(textBox27.Text) - raz2;
}
if (textBox28.Text != "")
{
ki5 = Convert.ToDouble(textBox28.Text) - raz2;
}
if (textBox29.Text != "")
{
ki6 = Convert.ToDouble(textBox29.Text) - raz2;
}
if (textBox30.Text != "")
{
ki7 = Convert.ToDouble(textBox30.Text) - raz2;
}
if (textBox31.Text != "")
{
ki8 = Convert.ToDouble(textBox31.Text) - raz2;
}
if (textBox32.Text != "")
{
ki9 = Convert.ToDouble(textBox32.Text) - raz2;
}
if (textBox33.Text != "")
{
ki10 = Convert.ToDouble(textBox33.Text) - raz2;
}
}
if (radioButton1.Checked)
{
if (textBox20.Text != "")
{
ki1 = raz + Convert.ToDouble(textBox20.Text);
}
if (textBox21.Text != "")
{
ki2 = raz + Convert.ToDouble(textBox21.Text);
}
if (textBox22.Text != "")
{
ki3 = raz + Convert.ToDouble(textBox22.Text);
}
if (textBox27.Text != "")
{
ki4 = raz + Convert.ToDouble(textBox27.Text);
}
if (textBox28.Text != "")
{
ki5 = raz + Convert.ToDouble(textBox28.Text);
}
if (textBox29.Text != "")
{
ki6 = raz + Convert.ToDouble(textBox29.Text);
}
if (textBox30.Text != "")
{
ki7 = raz + Convert.ToDouble(textBox30.Text);
}
if (textBox31.Text != "")
{
ki8 = raz + Convert.ToDouble(textBox31.Text);
}
if (textBox32.Text != "")
{
ki9 = raz + Convert.ToDouble(textBox32.Text);
}
if (textBox33.Text != "")
{
ki10 = raz + Convert.ToDouble(textBox33.Text);
}
}
if (radioButton2.Checked)
{
if (textBox20.Text != "")
{
ki1 = Convert.ToDouble(textBox20.Text);
}
if (textBox21.Text != "")
{
ki2 = Convert.ToDouble(textBox21.Text);
}
if (textBox22.Text != "")
{
ki3 = Convert.ToDouble(textBox22.Text);
}
if (textBox27.Text != "")
{
ki4 = Convert.ToDouble(textBox27.Text);
}
if (textBox28.Text != "")
{
ki5 = Convert.ToDouble(textBox28.Text);
}
if (textBox29.Text != "")
{
ki6 = Convert.ToDouble(textBox29.Text);
}
if (textBox30.Text != "")
{
ki7 = Convert.ToDouble(textBox30.Text);
}
if (textBox31.Text != "")
{
ki8 = Convert.ToDouble(textBox31.Text);
}
if (textBox32.Text != "")
{
ki9 = Convert.ToDouble(textBox32.Text);
}
if (textBox33.Text != "")
{
ki10 = Convert.ToDouble(textBox33.Text);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open((Path.Combine(mypath, "appur.xlsm")), ReadOnly: false, Password: "******");
Microsoft.Office.Interop.Excel.Worksheet x = excel.Sheets["variable"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
//x.Cells[2, 2] = dat;
x.Cells[2, 3] = zad;
x.Cells[2, 4] = nrvs;
x.Cells[2, 5] = nkr;
x.Cells[2, 6] = nig;
x.Cells[2, 7] = hodig;
x.Cells[2, 8] = hodigv;
x.Cells[2, 9] = hodign;
x.Cells[2, 10] = vhvost;
x.Cells[2, 11] = vhvostv;
x.Cells[2, 12] = vhvostn;
x.Cells[2, 13] = prnahod;
x.Cells[2, 14] = prnahodv;
x.Cells[2, 15] = prnahodn;
x.Cells[2, 16] = gidropl;
x.Cells[2, 17] = mfn;
x.Cells[2, 18] = mfv;
x.Cells[2, 19] = zag;
x.Cells[2, 20] = procpolir;
x.Cells[2, 21] = procdrosel;
x.Cells[2, 22] = pmarkbosch;
x.Cells[2, 23] = kmarkbosch;
x.Cells[2, 24] = pmarkazpi;
x.Cells[2, 25] = kmarkazpi;
x.Cells[2, 26] = uvp1;
x.Cells[2, 27] = uvp2;
x.Cells[2, 28] = uvp3;
x.Cells[2, 29] = uvp4;
x.Cells[2, 30] = uvp5;
x.Cells[2, 31] = uvp6;
x.Cells[2, 32] = uvp7;
x.Cells[2, 33] = uvp8;
x.Cells[2, 34] = uvp9;
x.Cells[2, 35] = uvp10;
x.Cells[2, 36] = uvsh1;
x.Cells[2, 37] = uvsh2;
x.Cells[2, 38] = uvsh3;
x.Cells[2, 39] = uvsh4;
x.Cells[2, 40] = uvsh5;
x.Cells[2, 41] = uvsh6;
x.Cells[2, 42] = uvsh7;
x.Cells[2, 43] = uvsh8;
x.Cells[2, 44] = uvsh9;
x.Cells[2, 45] = uvsh10;
x.Cells[2, 46] = ki1;
x.Cells[2, 47] = ki2;
x.Cells[2, 48] = ki3;
x.Cells[2, 49] = ki4;
x.Cells[2, 50] = ki5;
x.Cells[2, 51] = ki6;
x.Cells[2, 52] = ki7;
x.Cells[2, 53] = ki8;
x.Cells[2, 54] = ki9;
x.Cells[2, 55] = ki10;
x.Cells[2, 56] = diamotv;
x.Cells[2, 57] = pmfpr1;
x.Cells[2, 58] = pmfpr2;
x.Cells[2, 59] = ptpr1;
x.Cells[2, 60] = ptpr2;
x.Cells[2, 61] = pqpr1;
x.Cells[2, 62] = pqpr2;
x.Cells[2, 63] = pmfpol1;
x.Cells[2, 64] = pmfpol2;
x.Cells[2, 65] = ptpol1;
x.Cells[2, 66] = ptpol2;
x.Cells[2, 67] = pqpol1;
x.Cells[2, 68] = pqpol2;
x.Cells[2, 69] = pmfrvs1;
x.Cells[2, 70] = pmfrvs2;
x.Cells[2, 71] = ptrvs1;
x.Cells[2, 72] = ptrvs2;
x.Cells[2, 73] = pqrvs1;
x.Cells[2, 74] = pqrvs2;
Microsoft.Office.Interop.Excel.Worksheet x2 = excel.Sheets["pu"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange2 = x2.UsedRange;
x2.Cells[56, 11] = textBox34.Text;
//Печать
if (checkBox1.Checked)
{
excel.ActiveWorkbook.PrintOutEx(17, 17);
}
if (checkBox2.Checked)
{
excel.ActiveWorkbook.PrintOutEx(12, 12);
}
if (checkBox3.Checked)
{
excel.ActiveWorkbook.PrintOutEx(13, 13);
}
if (checkBox4.Checked)
{
excel.ActiveWorkbook.PrintOutEx(10, 10);
}
if (checkBox8.Checked)
{
excel.ActiveWorkbook.PrintOutEx(11, 11);
}
if (checkBox7.Checked)
{
excel.ActiveWorkbook.PrintOutEx(14, 14);
}
if (checkBox6.Checked)
{
excel.ActiveWorkbook.PrintOutEx(18, 18);
}
//excel.ActiveWorkbook.PrintOutEx(4, 4);
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
/// <summary>
/// 将DataTable中的数据导出到Excel（支持Excel2003和Excel2007）特点： 稳定 可导入 缺点：速度慢 (王伦清)
/// </summary>
/// <param name="dt"> DataTable</param>
/// <param name="url">Excel保存的路径DataTable</param>
/// <returns>导出成功返回True，否则返回false</ returns >
public bool ExportExcel2(DataTable dt, string url)
{
bool falge = false;
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置Excel不可见
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
//设置Excel字段类型全部为字符串
objsheet.Cells.NumberFormat = "@";
//向Excel中写入表格的标头
int displayColumnsCount = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
objExcel.Cells[1, displayColumnsCount] = dt.Columns[i].ColumnName.Trim();
displayColumnsCount++;
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row < dt.Rows.Count; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < dt.Columns.Count; col++)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{ }
}
}
//保存文件
objWorkbook.SaveAs(url, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
falge = true;
}
catch (Exception ex)
{
falge = false;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
//杀死进程
KillProcess("Excel");
objsheet = null;
objWorkbook = null;
objExcel = null;
}
return(falge);
}
/*
* get excel data that user insert
* input : (string) fileName <--- fullpath
* (string) workSheetName <--- worksheet can use "" (Emptry string) for worksheet that active or first
* (int) firstDataRowNumber <--- number of first row in excel that user want to import
* (int) colDateTime <--- number of column that is date data
* (int) colTimeIn <--- number of column that is time in data
* (int) colTimeOut <--- number of column that is time out data
* (int) colEmpNo <--- number of column that is Employee Number data
* (int) colEmpName <--- number of column that is Employee Name data
* output type : Datatable
* output : data of excel that import
* col[0] : date(string)
* col[1] : time in
* col[2] : time out
* col[3] : raw date(int)
*/
public DataTable LoadExcelFile_special(string fileName, string worksheetName, int firstDataRowNumber, int colDateTime, int colTime, int colTimeFlag, string colEmpNo, string colEmpName)
{
DataTable dt = new DataTable();
Microsoft.Office.Interop.Excel.Application ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkbook = ExcelApplication.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet ExcelWorksheet = null;
string WorksheetName = worksheetName;
if (string.IsNullOrWhiteSpace(worksheetName)) // check worksheetname is empty?
{
WorksheetName = ExcelWorkbook.ActiveSheet.Name; // set activeworksheet to worksheetname
}
ExcelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkbook.Worksheets[WorksheetName];
dt.TableName = WorksheetName;
Dictionary <string, int> Columns = new Dictionary <string, int>();
//----------------------------- get date in month--------------------------------------
string eeDate = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[firstDataRowNumber, colDateTime]).Value2);
//Console.WriteLine(eeDate);
double edate = double.Parse(eeDate);
fileDatetime = DateTime.FromOADate(edate);
int dayMonth = System.DateTime.DaysInMonth(fileDatetime.Year, fileDatetime.Month);
//----------------------------- end date in month--------------------------------------
//----------------------------- get emp no --------------------------------------------
lb_Emp_No.Text = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[getIntFromAddr(colEmpNo), GetColumnNumber(getStringFromAddr(colEmpNo))]).Value2);
lb_Emp_No.Text = Regex.Match(lb_Emp_No.Text, @"\d+").Value;
//----------------------------- end emp no --------------------------------------------
//----------------------------- get emp name --------------------------------------------
lb_Name.Text = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[getIntFromAddr(colEmpName), GetColumnNumber(getStringFromAddr(colEmpName))]).Value2);
//----------------------------- end emp name --------------------------------------------
//----------------------------- get project Name ----------------------------------------
if (tb_Groupbox_Data_Project.Enabled == true)
{
ProjectName = tb_Groupbox_Data_Project.Text;
}
else
{
ProjectName = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[getIntFromAddr(tb_Groupbox_Cell_Project.Text), GetColumnNumber(getStringFromAddr(tb_Groupbox_Cell_Project.Text))]).Value2);
}
//----------------------------- end project Name ----------------------------------------
//----------------------------- get site start time ----------------------------------------
if (tb_Groupbox_Data_Site_Start.Enabled == true)
{
SiteStartTime = tb_Groupbox_Data_Site_Start.Text;
}
else
{
SiteStartTime = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[getIntFromAddr(tb_Groupbox_Cell_Site_Start.Text), GetColumnNumber(getStringFromAddr(tb_Groupbox_Cell_Site_Start.Text))]).Value2);
}
//----------------------------- end site start time ----------------------------------------
//----------------------------- get site stop time ----------------------------------------
if (tb_Groupbox_Data_Site_Stop.Enabled == true)
{
SiteStopTime = tb_Groupbox_Data_Site_Stop.Text;
}
else
{
SiteStopTime = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[getIntFromAddr(tb_Groupbox_Cell_Site_Stop.Text), GetColumnNumber(getStringFromAddr(tb_Groupbox_Cell_Site_Stop.Text))]).Value2);
}
//----------------------------- end site stop time ----------------------------------------
ExcelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkbook.Worksheets[WorksheetName];
dt.Columns.Add("Date");
dt.Columns.Add("Time IN");
dt.Columns.Add("Time Out");
dt.Columns.Add("Date Raw");
int last_row = 0;
last_row = ExcelWorksheet.Cells.Find("Total", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
true, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
for (int i = 0; i < last_row - 1; i++)
{
try
{
DataRow Row = dt.NewRow();
string sDate;
double date;
try
{
sDate = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[i + firstDataRowNumber, colDateTime]).Value2);
date = double.Parse(sDate);
Row[0] = DateTime.FromOADate(date).ToString("dd/MM/yyyy");
Row[3] = date;
}
catch
{
}
try
{
string flag = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[i + firstDataRowNumber, colTimeFlag]).Value2).ToLower();
if (flag == "in")
{
sDate = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[i + firstDataRowNumber, colTime]).Value2);
date = double.Parse(sDate);
Row[1] = DateTime.FromOADate(date).ToString("HH:mm");
}
else
{
sDate = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[i + firstDataRowNumber, colTime]).Value2);
date = double.Parse(sDate);
Row[2] = DateTime.FromOADate(date).ToString("HH:mm");
}
}
catch
{
}
if (!string.IsNullOrWhiteSpace(Row[0].ToString()))
{
dt.Rows.Add(Row);
}
}
catch (Exception ex)
{
dt.Reset();
}
}
for (int i = 0; i < dayMonth; i++)
{
DateTime date_raw = new DateTime(fileDatetime.Year, fileDatetime.Month, i + 1);
bool check = true;
string date_string = date_raw.ToString("dd/MM/yyyy");
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[j][0].ToString() == date_string)
{
check = false;
}
}
if (check)
{
dt.Rows.Add(date_string, "", "", date_raw);
}
}
// Clean up
try { ExcelWorksheet = null; } catch { }
try { ExcelWorkbook.Close(false, misValue, misValue); } catch { }
try { ExcelWorkbook = null; } catch { }
try { ExcelApplication = null; } catch { }
return(dt);
}
// Add your test methods here...
public void WriteToExcelAcc()
{
var todayDate = DateTime.Now.ToString("MMDD");
String myPath = Utility.filepath;
var buildnum = Utility.currentBuild;
var row = Utility.row;
//var row = Data.IterationIndex + 2;
var column = 2;
var sheetName = Utility.plan + "_" + Utility.eventType + "_" + buildnum + "_" + Utility.currentDomain;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(myPath);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[Utility.sheetIndex];
Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
System.Threading.Thread.Sleep(1000);
ActiveBrowser.RefreshDomTree();
Utility.func_comment = string.Join(",", Utility.resultsData);
Console.Out.WriteLine("______Func Comment is: " + Utility.func_comment);
Log.WriteLine("______Func Comment is: " + Utility.func_comment);
xlWorksheet.Cells[row, column] = Utility.opentime;
if (Utility.opentime > 7)
{
xlRange.Interior.Color = Excel.XlRgbColor.rgbRed;
}
if (Utility.saveflag == "normal")
{
column = 3;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = Utility.savetime;
if (Utility.savetime > 10)
{
xlRange.Interior.Color = Excel.XlRgbColor.rgbRed;
}
column = 5;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = Utility.func_comment;
xlRange.Font.Color = Excel.XlRgbColor.rgbGreen;
if (Utility.error_flag)
{
xlRange.Font.Color = Excel.XlRgbColor.rgbRed;
}
}
else if (Utility.saveflag == "nosaveNoOther")
{
column = 3;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = "N/a";
column = 5;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = Utility.func_comment;
xlRange.Font.Color = Excel.XlRgbColor.rgbGreen;
if (Utility.error_flag)
{
xlRange.Font.Color = Excel.XlRgbColor.rgbRed;
}
Utility.row = Utility.row + 1;
//Utility.row = Data.IterationIndex + 2;
}
else if (Utility.saveflag == "nosaveButOther")
{
column = 3;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = Utility.savetime;
if (Utility.savetime > 10)
{
xlRange.Interior.Color = Excel.XlRgbColor.rgbRed;
}
column = 4;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
string s = string.Format("Time counted for '{0}'", Utility.comment);
xlWorksheet.Cells[row, column] = s;
column = 5;
xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[row, column];
xlWorksheet.Cells[row, column] = Utility.func_comment;
xlRange.Font.Color = Excel.XlRgbColor.rgbGreen;
if (Utility.error_flag)
{
xlRange.Font.Color = Excel.XlRgbColor.rgbRed;
}
}
Utility.func_comment = "";
Utility.error_flag = false;
Utility.saveflag = "normal";
Utility.resultsData.Clear();
excelApp.Visible = true;
excelApp.ActiveWorkbook.Save();
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
excelApp.Quit();
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
public void WriteToExcelPerformance_CodedStep1()
{
string dataSourcePath = this.ExecutionContext.DeploymentDirectory + @"\Data\PerformData.xls";
var todayDate = DateTime.Now.ToString("MMDD");
var timeToConvert = DateTime.Now;
var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
var todayDatetime1 = TimeZoneInfo.ConvertTime(timeToConvert, est);
var todayDatetime = todayDatetime1.ToString("yyyy-MM-dd hh:mm tt");
var filename = todayDate + @"PerformData.xls";
string myPath = @"C:\\MatrixTestReport\\" + filename;
if (!System.IO.File.Exists(myPath))
{
System.IO.File.Copy(dataSourcePath, myPath);
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(myPath);
System.Threading.Thread.Sleep(1000);
ActiveBrowser.RefreshDomTree();
Utility.row = Convert.ToInt32(Data["Row1"]);
excelApp.Cells[Utility.row, 2] = Data["Student"].ToString();
excelApp.Cells[Utility.row, 4] = todayDatetime;
excelApp.Cells[Utility.row, 6] = Utility.currentBuild;
excelApp.Cells[Utility.row, 7] = Utility.perfData[0];
excelApp.Cells[Utility.row, 8] = Utility.perfData[1];
excelApp.Cells[Utility.row, 9] = Utility.perfData[2];
excelApp.Cells[Utility.row, 10] = Utility.perfData[3];
excelApp.Cells[Utility.row, 11] = Utility.perfData[4];
excelApp.Cells[Utility.row, 12] = Utility.perfData[5];
excelApp.Cells[Utility.row, 13] = Utility.perfData[6];
excelApp.Cells[Utility.row, 14] = Utility.perfData[7];
excelApp.Cells[Utility.row, 15] = Utility.perfData[8];
excelApp.Cells[Utility.row, 16] = Utility.perfData[9];
excelApp.Cells[Utility.row, 17] = Utility.perfData[10];
excelApp.Cells[Utility.row, 18] = Utility.perfData[11];
excelApp.Cells[Utility.row, 19] = Utility.perfData[12];
excelApp.Cells[Utility.row, 20] = Utility.perfData[13];
excelApp.Cells[Utility.row, 21] = Utility.perfData[14];
Utility.func_comment = "";
Utility.error_flag = false;
Utility.saveflag = "normal";
Utility.perfData.Clear();
//Utility.row ++;
excelApp.Visible = true;
excelApp.ActiveWorkbook.Save();
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
excelApp.Quit();
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
public string ExcelExport(System.Data.DataTable DT, string title)
{
try
{
//创建Excel
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing);
//创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
//如果数据中存在数字类型 可以让它变文本格式显示
ExcelSheet.Cells.NumberFormat = "@";
//设置工作表名
ExcelSheet.Name = title;
//设置Sheet标题
string start = "A1";
string end = ChangeASC(DT.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
ExcelSheet.Cells[1, 1] = title; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//写表头
for (int m = 1; m <= DT.Columns.Count; m++)
{
ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();
start = "A2";
end = ChangeASC(DT.Columns.Count) + "2";
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 15; //设置字体大小
_Range.Font.Bold = true; //加粗
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
//写数据
for (int i = 0; i < DT.Rows.Count; i++)
{
for (int j = 1; j <= DT.Columns.Count; j++)
{
//Excel单元格第一个从索引1开始
// if (j == 0) j = 1;
ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();
}
}
//表格属性设置
for (int n = 0; n < DT.Rows.Count + 1; n++)
{
start = "A" + (n + 3).ToString();
end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();
//获取Excel多个单元格区域
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 12; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 _Range.EntireColumn.AutoFit(); //自动调整列宽
}
ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
//弹出保存对话框,并保存文件
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
sfd.DefaultExt = ".xlsx";
sfd.Filter = "Office 2007 File|*.xlsx|Office 2000-2003 File|*.xls|所有文件|*.*";
if (sfd.ShowDialog() == true)
{
if (sfd.FileName != "")
{
ExcelBook.SaveAs(sfd.FileName); //将其进行保存到指定的路径
// MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示");
}
}
//释放可能还没释放的进程
ExcelBook.Close();
ExcelApp.Quit();
// PubHelper.Instance.KillAllExcel(ExcelApp);
return(sfd.FileName);
}
catch
{
// MessageBox.Show("导出文件保存失败！", "警告！");
return(null);
}
}
public void importa()
{
try
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
//openfile.ShowDialog();
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
txtFilePath.Text = openfile.FileName;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.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...........
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
string strCellData = "";
double douCellData;
int rowCnt = 0;
int colCnt = 0;
DataTable dt = new DataTable();
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.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 Microsoft.Office.Interop.Excel.Range).Value2;
strData += strCellData + "|";
}
catch (Exception ex)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += douCellData.ToString() + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
dt.Rows.Add(strData.Split('|'));
}
gvData.ItemsSource = dt;
excelBook.Close(true, null, null);
excelApp.Quit();
var alert = new RadDesktopAlert();
alert.Header = "NOTIFICACIÓN";
alert.Content = "El archivo se cargó exitosamente.";
alert.ShowDuration = 3000;
RadDesktopAlertManager manager = new RadDesktopAlertManager();
manager.ShowAlert(alert);
}
else
{
btGuardar.Visibility = Visibility.Hidden;
btCancelar.Visibility = Visibility.Hidden;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
private void imprimeDesdeExcel()
{
enImpresion = true;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathPlantillaReporte, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathsDictionary["plantillaHallazgos"]);
Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets;
Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveSheet;
xlApp.Visible = true;
xlWorkBook.Saved = true;
object misValue = System.Reflection.Missing.Value;
//Modificamos los valores del excel
xlWorkSheet.Cells[3, 3] = dateTimePickerFecha.Text;
//Renglon 3 columna 7
xlWorkSheet.Cells[3, 7] = registroHistorial.turno;
xlWorkSheet.Cells[4, 5] = registroHistorial.identificadoPor;
xlWorkSheet.Cells[5, 3] = registroHistorial.cliente;
xlWorkSheet.Cells[5, 8] = registroHistorial.pedido;
xlWorkSheet.Cells[6, 5] = registroHistorial.responsable;
xlWorkSheet.Cells[7, 4] = registroHistorial.supervisorEnTurno;
xlWorkSheet.Cells[7, 8] = registroHistorial.operadorEnTurno;
xlWorkSheet.Cells[8, 5] = registroHistorial.inspectorDeCalidadEnTurno;
xlWorkSheet.Cells[8, 8] = registroHistorial.ayudanteEnTurno;
xlWorkSheet.Cells[10, 3] = registroHistorial.material;
xlWorkSheet.Cells[11, 3] = registroHistorial.calibre;
xlWorkSheet.Cells[12, 3] = registroHistorial.color;
xlWorkSheet.Cells[13, 3] = registroHistorial.perfil;
xlWorkSheet.Cells[10, 7] = registroHistorial.corte;
xlWorkSheet.Cells[11, 7] = registroHistorial.lotes;
//xlWorkSheet.Rows("9:9").EntireRow.AutoFit
xlWorkSheet.Cells[12, 7] = registroHistorial.cantidadInicial;
xlWorkSheet.Cells[12, 9] = registroHistorial.unidadInicial;
xlWorkSheet.Cells[13, 7] = registroHistorial.cantidadFinal;
xlWorkSheet.Cells[13, 9] = registroHistorial.unidadFinal;
xlWorkSheet.Cells[15, 5] = registroHistorial.disposicion;
xlWorkSheet.Cells[17, 1] = registroHistorial.descripcion;
xlWorkSheet.Cells[19, 1] = registroHistorial.inspeccionesRealizadas;
xlWorkSheet.Cells[21, 1] = registroHistorial.probableCausa;
xlWorkSheet.Cells[23, 1] = registroHistorial.observaciones;
xlWorkSheet.Cells[25, 1] = registroHistorial.enterado;
/*
* int indice = 0;
* for (int x = 0; x < arregloEnterado.Length; x++ )
* {
* if (arregloEnterado[x].nombre == comboBoxIdentificadoPor.Text)
* {
* indice = x;
* break;
* }
* }
*/
xlWorkSheet.Cells[26, 1] = arregloEnterado[comboBoxEnterado.SelectedIndex].puesto;
xlWorkSheet.Cells[25, 8] = "Mauro Corona Martínez";
xlWorkSheet.Cells[26, 8] = "Jefe de Control de Calidad";
Excel.Range aRange = xlWorkSheet.get_Range("A11", "Z23");
aRange.Rows.AutoFit();
xlApp.DisplayAlerts = false;
xlWorkBook.Save();
xlApp.Dialogs[Excel.XlBuiltInDialog.xlDialogPrintPreview].Show();
//xlWorkBook.PrintPreview();
//xlWorkBook.PrintPreview(true);
//Cerramos la aplicación
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
enImpresion = false;
}
