/* Экспорт данных из dataGridView1 в Excel */
private void btn_Export_Click(object sender, EventArgs e)
{
bool okData = false;
this.Cursor = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
if (dgv_Sales.RowCount > 0)
{
for (int i = 0; i < dgv_Sales.ColumnCount - 4; i++)
{
ExcelApp.Cells[1, i + 1] = dgv_Sales.Columns[i].HeaderText;
}
for (int i = 0; i < dgv_Sales.Rows.Count; i++)
{
for (int j = 0; j < dgv_Sales.ColumnCount - 4; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dgv_Sales.Rows[i].Cells[j].Value;
}
}
saveFileDialog1.FileName = lb_StartDate.Text + "-" + lb_EndDate.Text + " Business Report";
okData = true;
}
else
{
MessageBox.Show("Нет данных для экспорта!", "Ошибка");
}
saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*";
this.Cursor = Cursors.Default;
if (okData)
{
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
ExcelWorkBook.Close(false);
}
else
{
// получаем выбранный файл
string filename = saveFileDialog1.FileName;
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close(false);
MessageBox.Show("Успешно сохранено!", "Успех");
}
}
}
public void ConvertToExcel(string name)
{
string fullPath = Path.Combine(Server.MapPath("~/Images/"), name);
var file = System.IO.File.OpenRead(fullPath);
Image picture = Image.FromStream(file, true, true);
Bitmap Bpicture = (Bitmap)picture;
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(Missing.Value);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.ActiveSheet;
int[,] ExcelArray = new int[Bpicture.Width, Bpicture.Height];
for (int i = 0; i < Bpicture.Width; i++)
{
for (int j = 0; j < Bpicture.Height; j++)
{
ExcelArray[i, j] = Bpicture.GetPixel(i, j).R;
}
}
Excel.Range rng = ExcelWorkSheet.Cells[1, "A"];
rng = rng.Resize[Bpicture.Width, Bpicture.Height];
rng.Value = ExcelArray;
string filenameExcel = name.Split('.').First() + ".xlsx";
string fullfilename = Server.MapPath("~/ExcelFiles/" + filenameExcel);
if (System.IO.File.Exists(fullfilename))
{
System.IO.File.Delete(fullfilename);
}
ExcelWorkBook.SaveAs(fullfilename, Excel.XlFileFormat.xlWorkbookDefault);
ExcelWorkBook.Close(false, Type.Missing, Type.Missing);
ExcelApp.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook);
}
public static void ReadData(ClientContext context)
{
Excel.Application ExcelApp;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range ExcelRange;
ExcelApp = new Excel.Application();
ExcelWorkBook = ExcelApp.Workbooks.Open(@"D:\harsha853\SharePointAssessment.xlsx");
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
int MaximumRows = ExcelWorkSheet.UsedRange.Rows.Count;
int MaximumColumns = ExcelWorkSheet.UsedRange.Columns.Count;
ExcelRange = ExcelWorkSheet.UsedRange;
string Reason;
string UploadStatus;
for (int Row = 2; Row < MaximumRows; Row++)
{
string FilePath = (ExcelRange.Cells[Row, 1] as Excel.Range).Value2;
string Status = (ExcelRange.Cells[Row, 2] as Excel.Range).Value2;
string CreatedBy = (ExcelRange.Cells[Row, 3] as Excel.Range).Value2;
string Department = (ExcelRange.Cells[Row, 6] as Excel.Range).Value2;
AddFilesFromExcel(context, FilePath, CreatedBy, Status, Department, out Reason);
UploadStatus = String.IsNullOrEmpty(Reason) ? "File Uploaded Successfully" : "Failed to Upload File";
ExcelRange.Cells[Row, 4] = UploadStatus;
ExcelRange.Cells[Row, 5] = Reason;
}
ExcelWorkBook.Save();
ExcelWorkBook.Close();
ExcelApp.Quit();
}
/* Экспорт в *.xlsx */
private void btn_Export_Click(object sender, EventArgs e)
{
this.Enabled = false;
label3.Visible = true;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
for (int i = 3; i < dgv_Keywords.ColumnCount; i++)
{
ExcelApp.Cells[i - 2] = dgv_Keywords.Columns[i].HeaderText;
ExcelWorkSheet.Columns[i + 1].ColumnWidth = 12;
ExcelWorkSheet.Columns[i + 1].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
for (int i = 0; i < dgv_Keywords.Rows.Count; i++)
{
for (int j = 3; j < dgv_Keywords.ColumnCount; j++)
{
ExcelApp.Cells[i + 2, j - 2] = dgv_Keywords.Rows[i].Cells[j].Value;
}
}
ExcelWorkSheet.get_Range("A1", "A1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet.get_Range("B1", "B1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet.get_Range("C1", "C1").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet.Columns[1].ColumnWidth = 30;
ExcelWorkSheet.Columns[2].ColumnWidth = 30;
ExcelWorkSheet.Columns[3].ColumnWidth = 50;
saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*";
saveFileDialog1.FileName = "Keyword Book 1";
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
ExcelWorkBook.Close(false);
}
else
{
// получаем выбранный файл
string filename = saveFileDialog1.FileName;
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close(false);
MessageBox.Show("Успешно сохранено!", "Успех");
}
label3.Visible = false;
this.Enabled = true;
}
private void StartMatching()
{
for (int i = 0; i < ordersList.Count; i++)
{
for (int j = 0; j < ordersListstring.Count; j++)
{
if (ordersList[i].AmazonOrderId.Equals(ordersListstring[j]))
{
resultList.Add(new OrdersModel());
resultList[resultList.Count - 1] = ordersList[i];
j = ordersListstring.Count;
}
}
}
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
int row = 1;
for (int i = 0; i < resultList.Count; i++)
{
for (int j = 0; j < resultList[i].FieldCount; j++)
{
ExcelApp.Cells[row, j + 1] = resultList[i].GetOrders(j);
}
row++;
}
af.saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*";
af.saveFileDialog1.FileName = "Orders and Refunds Book 1";
if (af.saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
ExcelWorkBook.Close(false);
}
else
{
// получаем выбранный файл
string filename = af.saveFileDialog1.FileName;
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close(false);
MessageBox.Show("Успешно сохранено!", "Успех");
}
}
public void Close()
{
if (ExcelWorkBook != null)
{
ExcelWorkBook.Close();
}
if (ExcelApp != null)
{
ExcelApp.Quit();
}
}
/* Экспорт ключей в *.xls */
private void btn_Export_Click(object sender, EventArgs e)
{
progressBar1.Maximum = dgv_Keywords.RowCount;
progressBar1.Value = 0;
progressBar1.Visible = true;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelApp.Cells[1, 1] = "KEYWORD";
ExcelApp.Cells[1, 2] = "VALUE";
for (int i = 0; i < dgv_Keywords.Rows.Count; i++)
{
for (int j = 0; j < dgv_Keywords.ColumnCount; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dgv_Keywords.Rows[i].Cells[j].Value;
}
progressBar1.Value++;
}
//Вызываем нашу созданную эксельку.
//ExcelApp.Visible = true;
//ExcelApp.UserControl = true;
saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*";
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
}
else
{
// получаем выбранный файл
string filename = saveFileDialog1.FileName;
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close(false);
MessageBox.Show("Успешно сохранено!", "Успех");
}
progressBar1.Value = 0;
progressBar1.Visible = false;
}
//Сохранить в excel
private void SaveFileXlsToolStripMenuItem_Click(object sender, EventArgs e)
{
//Выибраем диркторию и имя
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
return;
}
string filenameSave = saveFileDialog1.FileName;
//Создаем объекты приложения, книги, листа
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelworkSheet;
//Создаем саму книгу
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Создаем лист в книги
ExcelworkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
//Заполняем лист
for (int j = 0; j < dataGridView4.Columns.Count; j++)
{
//Заголовки таблиц
ExcelApp.Cells[1, j + 1] = dataGridView4.Columns[j].HeaderCell.Value;
for (int i = 0; i < dataGridView4.Rows.Count; i++)
{
//Данные из таблицы
ExcelApp.Cells[i + 2, j + 1] = dataGridView4.Rows[i].Cells[j].Value;
}
}
//Сохраняем или заменяем существующий файл
ExcelWorkBook.SaveAs(filenameSave);
//Закрываем
ExcelWorkBook.Close();
ExcelApp.Quit();
WriteLogFile("Сохранение данных в файл " + filenameSave);
}
/* Экспорт PnL в Excel */
private void exportToolStripMenuItem_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelApp.Cells[1, 1] = "Тип";
ExcelApp.Cells[1, 2] = "Описание";
for (int i = 2; i < dataGridView1.ColumnCount; i++)
{
ExcelApp.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
saveFileDialog1.Filter = "Excel(*.xlsx)|*.xlsx|All files(*.*)|*.*";
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
}
else
{
// получаем выбранный файл
string filename = saveFileDialog1.FileName;
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close(false);
MessageBox.Show("Успешно сохранено!", "Успех");
}
}
private void button5_Click(object sender, EventArgs e)
{
string str;
int rCnt;
int cCnt;
OpenFileDialog opf = new OpenFileDialog();
opf.Filter = "Excel (*.XLSX)|*.XLSX";
opf.ShowDialog();
System.Data.DataTable tb = new System.Data.DataTable();
string filename = opf.FileName;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
Microsoft.Office.Interop.Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
dataGridView4.Rows.Add(1);
for (cCnt = 1; cCnt <= 5; cCnt++)
{
str = (string)(ExcelRange.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
dataGridView4.Rows[rCnt - 1].Cells[cCnt - 1].Value = str;
}
}
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
releaseObject(ExcelWorkSheet);
releaseObject(ExcelWorkBook);
releaseObject(ExcelApp);
}
/// <summary>
///
/// </summary>
/// <param name="DGW"></param>
public void ExcelToDataGridView(DataGridView DGW)
{
int rCnt;
int cCnt;
try
{
OpenFileDialog opf = new OpenFileDialog();
opf.Title = "Open excel file";
opf.Filter = "Файл Excel|*.XLSX;*.XLS";
opf.ShowDialog();
System.Data.DataTable tb = new System.Data.DataTable();
string filename = opf.FileName;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
Microsoft.Office.Interop.Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
for (rCnt = 2; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
DGW.Rows.Add(1);
for (cCnt = 1; cCnt <= 2; cCnt++)
{
DGW.Rows[rCnt - 2].Cells[cCnt - 1].Value = ExcelApp.Cells[rCnt, cCnt].Value;
}
}
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw;
}
}
public void Procesar()
{
StringBuilder sbLinea;
sbLinea = new StringBuilder("");
CargaSegurosSuraDA da = new CargaSegurosSuraDA();
SqlConnection cn = GetConnection();
cn.Open();
SqlTransaction trans = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
string path = sourcePath;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApplication excelApplication = new ExcelApplication();
ExcelWorkBook wb = excelApplication.OpenWorkBook(path, ExcelMode.Full);
try
{
// traer la cantidad de empresas atraves del campo codigo
ConveniosPlanEmpleadorDA conveniosPlanEmpleadorDA = new ConveniosPlanEmpleadorDA();
string codigosEmpresas = "COD_EMP_CON";
DataTable dtCodigosEmpresas = conveniosPlanEmpleadorDA.ObtenerTablaGeneral(codigosEmpresas);
int numeroEmpresas = dtCodigosEmpresas.Rows.Count;
object[] row;
int rowIndex;
string codigoParticipacion;
string nombreFondo;
DataRow[] drFondo;
DataTable dtFondosDepositos;
DataRow[] drFondoDeposito;
string idFondo;
string codigoTablatablaGeneralDepositos = "FONDO_EQUIV";
string mes = "";
string anio = "";
string estadoFondos = "ACT";
DataTable dtFondos = conveniosPlanEmpleadorDA.ListarFondos(estadoFondos);
CargaSegurosSuraDA cargaSegurosSuraDA = new CargaSegurosSuraDA();
if (numeroEmpresas == 0)
{
//no hay empresas
}
else
{
string periodo;
string[] periodoElementos;
string tipoMovimiento = "I";
string tipoAporte = ""; //"P"
double montoAporte;
string montoAporteCadena;
string nombreEmpresa;
bool existenRegistrosHoja = true;
string codigoEmpresa = "";
int grabo_registro = 0;
int numeroHojas = 0;
string parametro = "";
string constanteCP = "CP_";
int cantidadHojas = wb.GetSheetCount();
if (cantidadHojas == 3)
{
numeroHojas = wb.GetSheetCount();
}
else
{
numeroHojas = numeroEmpresas * 2 + 1;
}
//for (int i = 1; i <= numeroEmpresas; i++)
//{
for (int i = 1; i <= numeroHojas; i++)
{
ExcelWorkSheet sheet = wb.GetSheet(i);
// ExcelWorkSheet sheet = null;
nombreEmpresa = sheet.getName();
parametro = nombreEmpresa.Substring(0, 3);
StreamWriter sw = null;
//if (i == 1)
//{
// periodo = sheet.GetString("B8").Substring(4, 10);
// periodoElementos = periodo.Split('/');
// anio = periodoElementos[2].Trim();
// mes = periodoElementos[1].Trim();
//}
//codigoEmpresa = sheet.GetString("B1").Trim();
//DataTable dtDetalleConvenio = conveniosPlanEmpleadorDA.ObtenerDetalleConvenios(codigoEmpresa, Convert.ToInt32(mes), Convert.ToInt32(anio));
rowIndex = 13;//13
row = sheet.GetRow(rowIndex++, 'B', 'O');
int cont = 0;
while (row[0] != null & parametro != constanteCP)
{
try
{
ConvenioTD.ConvenioRow drConvenio = new ConvenioTD().Convenio.NewConvenioRow();
drConvenio.CUC = row[0].ToString();
drConvenio.MATRICULA = row[1] == null ? "" : row[1].ToString();
drConvenio.NOMBRE = row[2] == null ? "" : row[2].ToString();
drConvenio.NOMBRES = row[3].ToString();
drConvenio.APELLIDOS = row[4].ToString();
drConvenio.MONTO = Convert.ToDouble(row[5]);
drConvenio.FONDO = row[6].ToString();
drConvenio.ESTADO = row[7].ToString();
drConvenio.PARTICIPANTE = Convert.ToDouble(row[8]);
drConvenio.EMPLEADOR = Convert.ToDouble(row[9]);
drConvenio.ANTIGUEDAD = Convert.ToDouble(row[10]);
drConvenio.SALARIO_PROM = Convert.ToDouble(row[11]);
drConvenio.EDAD = Convert.ToDouble(row[12]);
drConvenio.BONO_ANUAL = Convert.ToDouble(row[13]);
cargaSegurosSuraDA.CargaRegistroConvenio(drConvenio, cn, trans);
row = sheet.GetRow(rowIndex++, 'B', 'O');
cont = cont + 1;
grabo_registro = grabo_registro + 1;
}
catch (Exception ex)
{
throw new Exception(string.Format("Error en la línea {0}. Detalle " + ex.Message, rowIndex - 1));
}
// trans.Commit();
}
if (cont > 0)
{
if (sw != null)
{
sw.Close();
}
}
existenRegistrosHoja = true;
}
if (grabo_registro > 0)
{
trans.Commit();
}
}
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
wb.Close();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
excelApplication.Close();
}
}
static void Main(string[] args)
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["UMS"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM ExcelGenerate", sqlConnection);
SqlCommand sqlCommand2 = new SqlCommand("SELECT * FROM UserInfo", sqlConnection);
SqlCommand sqlCommandDel = new SqlCommand("DELETE FROM ExcelGenerate", sqlConnection);
/*sqlCommand.CommandType = CommandType.StoredProcedure;*/
/*sqlCommand.Parameters.AddWithValue("@UserId", userId);*/
sqlConnection.Open();
var reader = sqlCommand.ExecuteReader();
if (reader.Read())
{
string fileName = reader.GetString(0);
string sendEmailTo = reader.GetString(1);
sqlConnection.Close();
sqlConnection.Open();
var getAll = sqlCommand2.ExecuteReader();
string OpenAtDownload = "";
/* System.Diagnostics.Debug.WriteLine("Is it empty: " + fileName.IsNullOrWhiteSpace());
* if (fileName == "" || fileName.IsNullOrWhiteSpace() == true) { fileName = "UserList"; }*/
if (fileName == "" || fileName == null)
{
fileName = "UserList";
}
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application ExcelExport = new Microsoft.Office.Interop.Excel.Application();
ExcelWorkBook = ExcelExport.Workbooks.Add(misValue);
ExcelWorkSheet = ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells.NumberFormat = "@";
var rowColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ExcelWorkSheet.get_Range("A1", "K1").Interior.Color = rowColor;
ExcelWorkSheet.get_Range("A1", "K1").Font.Bold = true;
ExcelWorkSheet.Cells[1, 1] = "UserName";
ExcelWorkSheet.Cells[1, 2] = "FirstName";
ExcelWorkSheet.Cells[1, 3] = "LastName";
ExcelWorkSheet.Cells[1, 4] = "Email";
ExcelWorkSheet.Cells[1, 5] = "Address";
ExcelWorkSheet.Cells[1, 6] = "City";
ExcelWorkSheet.Cells[1, 7] = "Country";
ExcelWorkSheet.Cells[1, 8] = "ZipCode";
ExcelWorkSheet.Cells[1, 9] = "PhoneNumber";
ExcelWorkSheet.Cells[1, 10] = "Role";
ExcelWorkSheet.Cells[1, 11] = "Status";
/* var userInfoes = db.UserInfoes.Include(u => u.Role).Include(u => u.Status).Include(u => u.UserCredential);
* userInfoes.ToList();*/
int row = 2;
while (getAll.Read())
{
ExcelWorkSheet.Cells[row, 1] = getAll.GetString(1);
ExcelWorkSheet.Cells[row, 2] = getAll.GetString(2);
ExcelWorkSheet.Cells[row, 3] = getAll.GetString(3);
ExcelWorkSheet.Cells[row, 4] = getAll.GetString(4);
ExcelWorkSheet.Cells[row, 5] = getAll.GetString(5);
ExcelWorkSheet.Cells[row, 6] = getAll.GetString(6);
ExcelWorkSheet.Cells[row, 7] = getAll.GetString(7);
ExcelWorkSheet.Cells[row, 8] = getAll.GetString(8);
ExcelWorkSheet.Cells[row, 9] = getAll.GetString(9);
ExcelWorkSheet.Cells[row, 10] = getAll.GetInt32(10);
ExcelWorkSheet.Cells[row, 11] = getAll.GetInt32(11);
row++;
}
ExcelWorkSheet.Columns.AutoFit();
/*string isEmailSend = "";*/
sqlConnection.Close();
sqlConnection.Open();
try
{
ExcelWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
if (sendEmailTo != null)
{
try
{
ExcelWorkBook.SendMail(sendEmailTo, "User List From the User Management System");
/*isEmailSend = "Email was sent.";*/
}
catch
{
/*isEmailSend = "Email was not sent.";*/
}
}
if (OpenAtDownload == "true")
{
ExcelWorkBook.WebPagePreview();
/*System.Diagnostics.Process.Start($"E:/Documents/{fileName}.xls"); */
}
/*ExcelWorkBook.WebPagePreview();*/
ExcelWorkBook.Close(true, misValue, misValue);
ExcelExport.Quit();
Marshal.ReleaseComObject(ExcelWorkSheet);
Marshal.ReleaseComObject(ExcelWorkBook);
Marshal.ReleaseComObject(ExcelExport);
sqlCommandDel.ExecuteReader();
sqlConnection.Close();
Environment.Exit(0);
/*return RedirectToAction("Index", new { a = "Excel Document was created sucessfully and it should be available in your Documents folder.", color = "green", emailStatus = isEmailSend });*/
}
catch
{
ExcelWorkBook.Close(true, misValue, misValue);
ExcelExport.Quit();
Marshal.ReleaseComObject(ExcelWorkSheet);
Marshal.ReleaseComObject(ExcelWorkBook);
Marshal.ReleaseComObject(ExcelExport);
/*System.Diagnostics.Debug.WriteLine("Excel Document was not created.");*/
sqlCommandDel.ExecuteReader();
sqlConnection.Close();
/*return RedirectToAction("Index", new { a = "Excel Document was not created.", color = "red", emailStatus = isEmailSend });*/
Environment.Exit(0);
}
}
}
private void B_browse_Click(object sender, EventArgs e)
{
string str;
int rCnt;
int cCnt;
OpenFileDialog opf = new OpenFileDialog();
opf.Filter = "Файл Excel|*.XLSX;*.XLS";
if (opf.ShowDialog() == DialogResult.OK)
{
DataTable tb = new System.Data.DataTable();
string filename = opf.FileName;
Excel.Application ExcelApp = new Excel.Application();
Excel._Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range ExcelRange;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelRange = ExcelWorkSheet.UsedRange;
progressBar1.Maximum = ExcelRange.Rows.Count;
progressBar1.Value = 1;
dgv2.Visible = false;
dgv2.Rows.Clear();
for (rCnt = 2; rCnt <= ExcelRange.Rows.Count; rCnt++)
{
dgv2.Rows.Add(1);
for (cCnt = 1; cCnt <= 21; cCnt++)
{
str = (string)(ExcelRange.Cells[rCnt, cCnt] as Excel.Range).Text;
dgv2.Rows[rCnt - 2].Cells[0].Value = rCnt - 1;
if (cCnt == 11)
{
if (str.Contains("+") || str.ToLower().Contains("да") || str.ToLower().Contains("t"))
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = true;
}
else
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = false;
}
}
else
{
dgv2.Rows[rCnt - 2].Cells[cCnt].Value = str;
}
}
progressBar1.Value++;
}
dgv2.Visible = true;
MessageBox.Show($"Загружено {ExcelRange.Rows.Count - 1} записей!", "Внимание!");
ExcelWorkBook.Close(true, null, null);
ExcelApp.Quit();
releaseObject(ExcelWorkSheet);
releaseObject(ExcelWorkBook);
releaseObject(ExcelApp);
b_import.Enabled = true;
}
}
private int CreateExcelFile()
{
bool okData = false;
string path = "D:\\";
string fileName = "";
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook ExcelWorkBook;
Worksheet ExcelWorkSheet1;
Worksheet ExcelWorkSheet2;
Worksheet ExcelWorkSheet3;
Worksheet ExcelWorkSheet4;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
ExcelWorkBook.Worksheets.Add();
ExcelWorkBook.Worksheets.Add();
ExcelWorkBook.Worksheets.Add();
//Таблица.
ExcelWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(2);
ExcelWorkSheet3 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(3);
ExcelWorkSheet4 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(4);
int broadcnt = 0;
int phrasecnt = 0;
int exactcnt = 0;
int othercnt = 0;
if (resultList.Count > 0)
{
for (int i = 0; i < resultList.Count; i++)
{
if (resultList[i].matchType.ToLower().Equals("broad"))
{
for (int j = 0; j < resultList[0].ColumnCount; j++)
{
if (j >= 0 && j <= 5)
{
ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = resultList[i].GetVal(j).ToString();
}
else if (j >= 6 && j <= 8)
{
ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = (int)resultList[i].GetVal(j);
}
else if (j == 9)
{
ExcelWorkSheet1.Cells[broadcnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2);
}
}
broadcnt++;
}
else if (resultList[i].matchType.ToLower().Equals("phrase"))
{
for (int j = 0; j < resultList[0].ColumnCount; j++)
{
if (j >= 0 && j <= 5)
{
ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = resultList[i].GetVal(j).ToString();
}
else if (j >= 6 && j <= 8)
{
ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = (int)resultList[i].GetVal(j);
}
else if (j == 9)
{
ExcelWorkSheet2.Cells[phrasecnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2);
}
}
phrasecnt++;
}
else if (resultList[i].matchType.ToLower().Equals("exact"))
{
for (int j = 0; j < resultList[0].ColumnCount; j++)
{
if (j >= 0 && j <= 5)
{
ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = resultList[i].GetVal(j).ToString();
}
else if (j >= 6 && j <= 8)
{
ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = (int)resultList[i].GetVal(j);
}
else if (j == 9)
{
ExcelWorkSheet3.Cells[exactcnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2);
}
}
exactcnt++;
}
else
{
for (int j = 0; j < resultList[0].ColumnCount; j++)
{
if (j >= 0 && j <= 5)
{
ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = resultList[i].GetVal(j).ToString();
}
else if (j >= 6 && j <= 8)
{
ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = (int)resultList[i].GetVal(j);
}
else if (j == 9)
{
ExcelWorkSheet4.Cells[othercnt + 2, j + 1] = Math.Round((double)resultList[i].GetVal(j), 2);
}
}
othercnt++;
}
}
fileName = "Advertising Alarm Report - " + startNew.ToString("dd-MM-yyyy") + "-" + endNew.ToString("dd-MM-yyyy") + " (" + DateTime.Now.ToString("HH-mm-ss") + ")";
okData = true;
}
ExcelWorkSheet1.Cells[1, 1] = "Товар";
ExcelWorkSheet1.Cells[1, 2] = "Маркетплейс";
ExcelWorkSheet1.Cells[1, 3] = "Campaign";
ExcelWorkSheet1.Cells[1, 4] = "AdGroup";
ExcelWorkSheet1.Cells[1, 5] = "Targeting";
ExcelWorkSheet1.Cells[1, 6] = "Match Type";
ExcelWorkSheet1.Cells[1, 7] = "Позапрошлая неделя";
ExcelWorkSheet1.Cells[1, 8] = "Прошлая неделя";
ExcelWorkSheet1.Cells[1, 9] = "Разница";
ExcelWorkSheet1.Cells[1, 10] = "Разница %";
ExcelWorkSheet1.Name = "Broad";
ExcelWorkSheet1.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet1.Columns[1].ColumnWidth = 32;
ExcelWorkSheet1.Columns[2].ColumnWidth = 22;
ExcelWorkSheet1.Columns[3].ColumnWidth = 31;
ExcelWorkSheet1.Columns[4].ColumnWidth = 17;
ExcelWorkSheet1.Columns[5].ColumnWidth = 23;
ExcelWorkSheet1.Columns[6].ColumnWidth = 13;
ExcelWorkSheet1.Columns[7].ColumnWidth = 13;
ExcelWorkSheet1.Columns[8].ColumnWidth = 13;
ExcelWorkSheet1.Columns[9].ColumnWidth = 13;
ExcelWorkSheet1.Columns[10].ColumnWidth = 13;
ExcelWorkSheet2.Cells[1, 1] = "Товар";
ExcelWorkSheet2.Cells[1, 2] = "Маркетплейс";
ExcelWorkSheet2.Cells[1, 3] = "Campaign";
ExcelWorkSheet2.Cells[1, 4] = "AdGroup";
ExcelWorkSheet2.Cells[1, 5] = "Targeting";
ExcelWorkSheet2.Cells[1, 6] = "Match Type";
ExcelWorkSheet2.Cells[1, 7] = "Позапрошлая неделя";
ExcelWorkSheet2.Cells[1, 8] = "Прошлая неделя";
ExcelWorkSheet2.Cells[1, 9] = "Разница";
ExcelWorkSheet2.Cells[1, 10] = "Разница %";
ExcelWorkSheet2.Name = "Phrase";
ExcelWorkSheet2.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet2.Columns[1].ColumnWidth = 32;
ExcelWorkSheet2.Columns[2].ColumnWidth = 22;
ExcelWorkSheet2.Columns[3].ColumnWidth = 31;
ExcelWorkSheet2.Columns[4].ColumnWidth = 17;
ExcelWorkSheet2.Columns[5].ColumnWidth = 23;
ExcelWorkSheet2.Columns[6].ColumnWidth = 13;
ExcelWorkSheet2.Columns[7].ColumnWidth = 13;
ExcelWorkSheet2.Columns[8].ColumnWidth = 13;
ExcelWorkSheet2.Columns[9].ColumnWidth = 13;
ExcelWorkSheet2.Columns[10].ColumnWidth = 13;
ExcelWorkSheet3.Cells[1, 1] = "Товар";
ExcelWorkSheet3.Cells[1, 2] = "Маркетплейс";
ExcelWorkSheet3.Cells[1, 3] = "Campaign";
ExcelWorkSheet3.Cells[1, 4] = "AdGroup";
ExcelWorkSheet3.Cells[1, 5] = "Targeting";
ExcelWorkSheet3.Cells[1, 6] = "Match Type";
ExcelWorkSheet3.Cells[1, 7] = "Позапрошлая неделя";
ExcelWorkSheet3.Cells[1, 8] = "Прошлая неделя";
ExcelWorkSheet3.Cells[1, 9] = "Разница";
ExcelWorkSheet3.Cells[1, 10] = "Разница %";
ExcelWorkSheet3.Name = "Exact";
ExcelWorkSheet3.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet3.Columns[1].ColumnWidth = 32;
ExcelWorkSheet3.Columns[2].ColumnWidth = 22;
ExcelWorkSheet3.Columns[3].ColumnWidth = 31;
ExcelWorkSheet3.Columns[4].ColumnWidth = 17;
ExcelWorkSheet3.Columns[5].ColumnWidth = 23;
ExcelWorkSheet3.Columns[6].ColumnWidth = 13;
ExcelWorkSheet3.Columns[7].ColumnWidth = 13;
ExcelWorkSheet3.Columns[8].ColumnWidth = 13;
ExcelWorkSheet3.Columns[9].ColumnWidth = 13;
ExcelWorkSheet3.Columns[10].ColumnWidth = 13;
ExcelWorkSheet4.Cells[1, 1] = "Товар";
ExcelWorkSheet4.Cells[1, 2] = "Маркетплейс";
ExcelWorkSheet4.Cells[1, 3] = "Campaign";
ExcelWorkSheet4.Cells[1, 4] = "AdGroup";
ExcelWorkSheet4.Cells[1, 5] = "Targeting";
ExcelWorkSheet4.Cells[1, 6] = "Match Type";
ExcelWorkSheet4.Cells[1, 7] = "Позапрошлая неделя";
ExcelWorkSheet4.Cells[1, 8] = "Прошлая неделя";
ExcelWorkSheet4.Cells[1, 9] = "Разница";
ExcelWorkSheet4.Cells[1, 10] = "Разница %";
ExcelWorkSheet4.Name = "Other";
ExcelWorkSheet4.get_Range("G2", "J5000").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelWorkSheet4.Columns[1].ColumnWidth = 32;
ExcelWorkSheet4.Columns[2].ColumnWidth = 22;
ExcelWorkSheet4.Columns[3].ColumnWidth = 31;
ExcelWorkSheet4.Columns[4].ColumnWidth = 17;
ExcelWorkSheet4.Columns[5].ColumnWidth = 23;
ExcelWorkSheet4.Columns[6].ColumnWidth = 13;
ExcelWorkSheet4.Columns[7].ColumnWidth = 13;
ExcelWorkSheet4.Columns[8].ColumnWidth = 13;
ExcelWorkSheet4.Columns[9].ColumnWidth = 13;
ExcelWorkSheet4.Columns[10].ColumnWidth = 13;
if (okData)
{
try
{
path = path + fileName;
ExcelWorkBook.SaveAs(@path);
ExcelWorkBook.Close(false);
return(1);
}
catch (Exception ex)
{
return(0);
}
}
else
{
return(0);
}
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 指定されたExcelファイルを読み込む
/// </summary>
private void ReadExcel()
{
//Excelファイル選択し、選択されなければ終了。
if (this.openFileExcel.ShowDialog() != DialogResult.OK)
{
return;
}
Address_DS ads = new Address_DS();
Address_DS errords = new Address_DS();
// ExcelWorkBookオープン
ExcelWorkBook wBook = new ExcelWorkBook();
wBook.Open(this.openFileExcel.FileName);
try
{
//１行目を読み込む
ExcelWorkSheet sheet = wBook.WorkSheets[0];
ArrayList columnList = new ArrayList();
for (int col = sheet.FirstColumn; col <= sheet.LastColumn; col++)
{
if (sheet.Cells[0, col].Value == null)
{
continue;
}
columnList.Add(sheet.Cells[0, col].Value);
}
//読み込み行がない場合は空のデータセットを返す。
if (columnList.Count == 0)
{
MessageBox.Show(this, "読み込み可能な行がありません。");
return;
}
using (ColumnSelectForm f = new ColumnSelectForm(columnList))
{
//行選択画面を表示し、ユーザに読み込む行を指定してもらう
if (f.ShowDialog() != DialogResult.OK)
{
//選択されなければ、空のデータセットを返す。
return;
}
int readStartRowIndex = 0;
if (f.ReadFirstRow == true)
{
readStartRowIndex = 0;
}
else
{
readStartRowIndex = 1;
}
//ユーザが指定した列を読み込む
for (int row = readStartRowIndex; row <= sheet.LastRow; row++)
{
//NULLやすでに取り込まれたメールアドレスははずす。
if (!((sheet.Cells[row, f.SelectColumnIndex].Value == null) ||
ads.Address.FindByMailAddress(sheet.Cells[row, f.SelectColumnIndex].Value.ToString()) != null))
{
string mailAddr = sheet.Cells[row, f.SelectColumnIndex].Value.ToString();
//メールアドレス形式のアドレスのみ登録
if (TKMP.Writer.MailAddressCollection.IsAddressPattern(mailAddr) == true)
{
Address_DS.AddressRow aRow = ads.Address.NewAddressRow();
aRow.MailAddress = mailAddr;
ads.Address.AddAddressRow(aRow);
}
else
{
//メールアドレス形式でないものは、エラーリストに保存
Address_DS.AddressRow aeRow = errords.Address.NewAddressRow();
aeRow.MailAddress = mailAddr;
errords.Address.AddAddressRow(aeRow);
}
}
}
}
}
finally
{
// ExcelWorkBookクローズ
wBook.Close();
}
//Excelファイルを読み取る。
this._addressds.Merge(ads);
//エラー情報をセットする。
this._errorForm.ErrorList = errords;
}
private static void ReadExcel(ClientContext clientContext)
{
try
{
PathLocation pathLocation = new PathLocation();
List list = clientContext.Web.Lists.GetByTitle(pathLocation.DocumentLibrary);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
string FileserverUrl = list.RootFolder.ServerRelativeUrl + "/" + pathLocation.ExcelFileName;
Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(FileserverUrl);
ClientResult <System.IO.Stream> ExcelData = file.OpenBinaryStream();
clientContext.Load(file);
clientContext.ExecuteQuery();
using (var package = new OfficeOpenXml.ExcelPackage())
{
using (System.IO.MemoryStream Memorystream = new System.IO.MemoryStream())
{
// to read the data of the online excel sheet
if (ExcelData != null)
{
ExcelData.Value.CopyTo(Memorystream);
package.Load(Memorystream);
var WorkSheet = package.Workbook.Worksheets.First();
DataTable table1 = new DataTable();
bool IsHeadingRowAvailable = true;
foreach (var firstRowCell in WorkSheet.Cells[1, 1, 1, WorkSheet.Dimension.End.Column])
{
table1.Columns.Add(IsHeadingRowAvailable ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
string FileUploadStatus;
string Reason = "";
//to open the local excel file which was downloaded, Using Excel Service
Excel.Application Excelapplication;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range range;
Excelapplication = new Excel.Application();
var Excellocalpath = System.IO.Path.Combine(pathLocation.ExcelFilePath, pathLocation.ExcelFileName);
ExcelWorkBook = Excelapplication.Workbooks.Open(Excellocalpath);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
range = ExcelWorkSheet.UsedRange;
var startRow = IsHeadingRowAvailable ? 2 : 1;// if Row Head Is available Then Row Starts From 2 else 1
for (var RowNumber = startRow; RowNumber <= WorkSheet.Dimension.End.Row; RowNumber++)
{
// getting the rows info which starts from 2
var WorkSheetRow = WorkSheet.Cells[RowNumber, 1, RowNumber, WorkSheet.Dimension.End.Column];
// storing the data based on column number of each row
string filetoupload = WorkSheetRow[RowNumber, 1].Text; //FilePath of File to Be Uploaded
string status = WorkSheetRow[RowNumber, 2].Text; //To read The Status Of The File
string[] values = status.Split(','); //Getting Multiple Status info Storing Seperately By splitting with ,
string CreatedBy = WorkSheetRow[RowNumber, 3].Text; //Getting info of the person who created the File
string deptfilebelongs = WorkSheetRow[RowNumber, 4].Text; //File Belongs To Particular Department
int split = filetoupload.LastIndexOf('.'); //To Get The Type Of the File
string filename = split < 0 ? filetoupload : filetoupload.Substring(0, split);
string type = split < 0 ? "" : filetoupload.Substring(split + 1);
System.IO.FileInfo filesize = new System.IO.FileInfo(filetoupload);// Getting the Size of Each file
long size = filesize.Length;
try
{
if (size >= 1000 && size <= 20000)//uploading files based on the filesize(Bytes)
{
List documentlibrary = clientContext.Web.Lists.GetByTitle("UploadedDocument");
var filecreationinfo = new FileCreationInformation();
filecreationinfo.Content = System.IO.File.ReadAllBytes(filetoupload);
filecreationinfo.Overwrite = true;
filecreationinfo.Url = Path.Combine("UploadedDocument/", Path.GetFileName(filetoupload));
Microsoft.SharePoint.Client.File files = documentlibrary.RootFolder.Files.Add(filecreationinfo);
ListItem listItem = files.ListItemAllFields;
// updating the DocumentLibrary with Following Fields
listItem["Dept"] = deptfilebelongs;
listItem["FileType"] = type;
listItem["Status"] = values;
listItem.Update();
clientContext.Load(files);
clientContext.ExecuteQuery();
Console.WriteLine("FileUploaded");
Reason = "";
}
else
{
Console.WriteLine("FileSizeNotInRange");
Reason = "FileSizeNotInRange";
}
}
catch (Exception ex)
{
Reason = ex.Message;
}
finally
{
FileUploadStatus = String.IsNullOrEmpty(Reason) ? "Uploaded" : "Failed";
range.Cells[RowNumber, 5] = FileUploadStatus;
range.Cells[RowNumber, 6] = Reason;
}
}
//closing the Local Excel File Which Was Updated.
ExcelWorkBook.Save();
ExcelWorkBook.Close();
Excelapplication.Quit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Obtiene los registros de asientos tipo VT de ingresos a partir de un archivo Excel.
/// </summary>
/// <param name="dtAsientos">Tabla en la que se volcará la información de los asientos a generar.</param>
/// <param name="archivo">Ruta y nombre del archivo Excel que contiene la información del reporte de ingresos para obtener los asientos.</param>
public void ObtenerAsientosVTIngresos(Procesos.TD.Asiento.LISTA_ASIENTODataTable dtAsientos, string archivo)
{
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("es-PE");
CultureInfo originalCulture = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
ExcelApplication app = new ExcelApplication();
ExcelWorkBook wb = app.OpenWorkBook(archivo, ExcelMode.Full);
ExcelWorkSheet sheet = wb.GetSheet(1);
String fechaAsiento = sheet.GetString("G3"); //convertir a fecha
DataTable dt = sheet.LeerTabla("B", "I", 5, sheet.ObtenerUltimaFilaTabla("B", 6), true);
/*String factura = sheet.GetString("G2");
* string[] partesFactura = ObtenerPartesFactura(factura);
* string numeros = partesFactura[0];
* string letras = partesFactura[1];
*/
ParametrosDA parametrosDA = new ParametrosDA();
DataTable dtParametroRUC = parametrosDA.ObtenerValoresParametros(ConstantesING.TABLA_TIPO_DOCUMENTO, ConstantesING.TIPO_DOCUMENTO_RUC);
String tipoDocumentoSunat = dtParametroRUC.Rows[0]["CAMPO1"].ToString();
//int correlativoFactura = Convert.ToInt32(numeros);
wb.Close();
app.Close();
Hashtable ht = new Hashtable();
foreach (DataRow dr in dt.Rows)
{
string fondo = dr["Fondo"].ToString().Trim();
if (!ht.ContainsKey(fondo))
{
Asiento.LISTA_ASIENTORow row = dtAsientos.NewLISTA_ASIENTORow();
//string numeroFactura = letras + correlativoFactura.ToString().PadLeft(numeros.Length, '0');
//correlativoFactura++;
object total = dt.Compute("SUM(Ingresos)", "Fondo = '" + fondo + "'");
//object totalImpuesto = dt.Compute("SUM(IGV)", "Fondo = '" + fondo + "'");
object totalPN = dt.Compute("SUM(Ingresos)", "Fondo = '" + fondo + "' and TipoPersona = 'PN'");
//object impuestoPN = dt.Compute("SUM(IGV)", "Fondo = '" + fondo + "' and TipoPersona = 'PN'");
object totalPJ = dt.Compute("SUM(Ingresos)", "Fondo = '" + fondo + "' and TipoPersona = 'PJ'");
//object impuestoPJ = dt.Compute("SUM(IGV)", "Fondo = '" + fondo + "' and TipoPersona = 'PJ'");
//object subtotal = Convert.ToDecimal(total) - Convert.ToDecimal(totalImpuesto);
object igv = dt.Compute("SUM(IGV)", "Fondo = '" + fondo + "'");
row.TIPO_ASIENTO = "VT_ING";
row.UUID = "";
row.ESTADO = "";
row.FONDO = fondo;
row.FECHA_PROCESO = DateTime.ParseExact(fechaAsiento, "dd/MM/yyyy", CultureInfo.InvariantCulture).Date;
row.ID_OPERACION = "";
row.MONEDA = dr["Moneda"].ToString() == "SOL" ? "PEN" : "USD";
row.TOTAL = total == null || total == DBNull.Value ? 0 : Decimal.Round(Convert.ToDecimal(total), 2);
row.MONTO1 = total == null || total == DBNull.Value ? 0 : Decimal.Round(Convert.ToDecimal(total), 2);
//row.MONTO2 = totalImpuesto == null || totalImpuesto == DBNull.Value ? 0 : Convert.ToDecimal(totalImpuesto);
//row.MONTO3 = totalPN == null || totalPN == DBNull.Value ? 0 : Convert.ToDecimal(totalPN);
//row.MONTO4 = totalPJ == null || totalPJ == DBNull.Value ? 0 : Convert.ToDecimal(totalPJ);
row.MONTO2 = totalPN == null || totalPN == DBNull.Value ? 0 : Decimal.Round(Convert.ToDecimal(totalPN), 2);
row.MONTO3 = totalPJ == null || totalPJ == DBNull.Value ? 0 : row.MONTO1 - row.MONTO2;
row.MONTO4 = 0;
row.MONTO5 = 0;
//ABRIR EN CASO SE JUNTEN LOS MONTO 3 Y MONTO4
row.REFERENCIA = "";// numeroFactura;
row.REFERENCIA1 = tipoDocumentoSunat;
//row.MONTO3 = subtotal == null || subtotal == DBNull.Value ? 0 : Convert.ToDecimal(subtotal);
row.TEXTO1 = row.FECHA_PROCESO.ToString("MM");
row.TEXTO2 = row.FECHA_PROCESO.ToString("yyyy");
DataTable dtFondo = parametrosDA.ObtenerValoresParametros(ConstantesING.FONDO, fondo);
row.TIPO_DOCUMENTO = ConstantesING.TIPO_DOCUMENTO_RUC;
row.CODIGO_CLIENTE = dtFondo.Rows[0]["CAMPO2"].ToString();
row.RAZON_SOCIAL = dtFondo.Rows[0]["CAMPO3"].ToString();
row.DIRECCION = ConfigurationSettings.AppSettings[ConstantesING.DIRECCION_FONDOS];
row.DISTRITO = ConfigurationSettings.AppSettings[ConstantesING.DISTRITO_FONDOS];
row.PROVINCIA = ConfigurationSettings.AppSettings[ConstantesING.PROVINCIA_FONDOS];
row.DEPARTAMENTO = ConfigurationSettings.AppSettings[ConstantesING.DEPARTAMENTO_FONDOS];
row.CODIGO_PAIS = ConstantesING.CODIGO_PERU;
row.CORREO = ConfigurationSettings.AppSettings[ConstantesING.CORREO_FONDOS];
row.ID_PAIS = ConfigurationSettings.AppSettings[ConstantesING.ID_PAIS_FONDOS];
row.ID_DEPARTAMENTO = ConfigurationSettings.AppSettings[ConstantesING.ID_DEPARTAMENTO_FONDOS];
row.ID_CIUDAD = ConfigurationSettings.AppSettings[ConstantesING.ID_CIUDAD_FONDOS];
row.ID_DISTRITO = ConfigurationSettings.AppSettings[ConstantesING.ID_DISTRITO_FONDOS];
row.UBIGEO = ConfigurationSettings.AppSettings[ConstantesING.UBIGEO_FONDOS];
row.IGVF = Decimal.ToDouble(Decimal.Round(Convert.ToDecimal(igv), 2));
row.TOTALF = Decimal.ToDouble(row.TOTAL);
row.SUBF = row.TOTALF - row.IGVF;
dtAsientos.AddLISTA_ASIENTORow(row);
ht.Add(fondo, fondo);
}
}
da.Server = INGFondos.Constants.Conexiones.ServidorSAP;
da.Database = INGFondos.Constants.Conexiones.BaseDeDatosSAP;
da.ListarControlAsiento(dtAsientos, "VT_ING", DateTime.ParseExact(fechaAsiento, "dd/MM/yyyy", CultureInfo.InvariantCulture).Date);
da.Server = INGFondos.Constants.Conexiones.ServidorOperaciones;
da.Database = INGFondos.Constants.Conexiones.BaseDeDatosOperaciones;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Thread.CurrentThread.CurrentCulture = originalCulture;
}
}
//OT 8372-PSC001
//public void generarArchivo(string rutaArchivo, string rutaArchivoDestino)
public void generarArchivoXFondo(string rutaArchivo, string rutaArchivoDestino)
{
StringBuilder sbLinea;
sbLinea = new StringBuilder("");
//StreamWriter sw = null;
string path = rutaArchivo;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApplication excelApplication = new ExcelApplication();
ExcelWorkBook wb = excelApplication.OpenWorkBook(path, ExcelMode.Full);
try
{
ConveniosPlanEmpleadorDA conveniosPlanEmpleadorDA = new ConveniosPlanEmpleadorDA();
string codigosEmpresas = "COD_EMP_CON";
DataTable dtCodigosEmpresas = conveniosPlanEmpleadorDA.ObtenerTablaGeneral(codigosEmpresas);
int numeroEmpresas = dtCodigosEmpresas.Rows.Count;
object[] row;
int rowIndex;
string codigoParticipacion;
string nombreFondo;
DataRow[] drFondo;
DataTable dtFondosDepositos;
DataRow[] drFondoDeposito;
string idFondo;
string codigoTablatablaGeneralDepositos = "FONDO_EQUIV";
string mes = "";
string anio = "";
string estadoFondos = "ACT";
DataTable dtFondos = conveniosPlanEmpleadorDA.ListarFondos(estadoFondos);
//OT 8372 INI
DataTable dtRegistrosHoja;//almacena el contenido de una hoja del excel
#region Definir las columnas del DataTable dtRegistrosHoja
dtRegistrosHoja = new DataTable();
dtRegistrosHoja.Columns.Add("CODIGO_PARTICIPACION", typeof(string));
dtRegistrosHoja.Columns.Add("NOMBRE_FONDO", typeof(string));
dtRegistrosHoja.Columns.Add("MONTO_APORTE", typeof(decimal));
#endregion
//OT 8372 FIN
if (numeroEmpresas == 0)
{
//no hay empresas
}
else
{
string periodo;
string[] periodoElementos;
string tipoMovimiento = "I";
string tipoAporte = ""; //"P"
double montoAporte;
string montoAporteCadena;
string nombreEmpresa;
bool existenRegistrosHoja = true;
string codigoEmpresa = "";
string constanteCP = "CP_";
// int numeroHojas = numeroEmpresas * 2 + 1;
int numeroHojas = 0;
//OT 6891 inicio
int cantidadHojas = wb.GetSheetCount();
if (cantidadHojas == 3)
{
numeroHojas = wb.GetSheetCount();
}
else
{
numeroHojas = numeroEmpresas * 2 + 1;
}
//for (int i = 1; i <= numeroEmpresas; i++)
for (int i = 1; i <= numeroHojas; i++)
{
//ExcelWorkSheet sheet = wb.GetSheet(i + 1);
//Toma a partir de la 4ta hoja porque las 3 primeras son plantillas vacías.
//ExcelWorkSheet sheet = wb.GetSheet(i + 1);
//Toma a partir de la 4ta hoja porque las 3 primeras son plantillas vacías.
// OT 6891
ExcelWorkSheet sheet = null;
if (cantidadHojas == 3)
{
sheet = wb.GetSheet(i);
}
else
{
sheet = wb.GetSheet(i + 1);
}
// OT 6891 fin
nombreEmpresa = sheet.getName();
if (nombreEmpresa.StartsWith(constanteCP))
{
//StreamWriter sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + nombreEmpresa + "_" + anio + mes + ".txt");
StreamWriter sw = null;
String estadoCabecera;
//if (i == 1)
//{
periodo = sheet.GetString("B8").Substring(4, 10);
periodoElementos = periodo.Split('/');
anio = periodoElementos[2].Trim();
mes = periodoElementos[1].Trim();
//}
codigoEmpresa = sheet.GetString("B1").Trim();
DataTable dtDetalleConvenio = conveniosPlanEmpleadorDA.ObtenerDetalleConvenios(codigoEmpresa, Convert.ToInt32(mes), Convert.ToInt32(anio));
rowIndex = 13;//13
//row = sheet.GetRow(rowIndex++, 'B', 'J'); //OT 6134
row = sheet.GetRow(rowIndex++, 'B', 'E');
int cont = 0;
while (row[0] != null)
{
//if (row[8].ToString().Trim() == "EFE")
//if (row[3].ToString().Trim() == "EFE")
estadoCabecera = ObtienePestañaEmpresa(wb, nombreEmpresa.Substring(constanteCP.Length), numeroHojas, row[0].ToString().Substring(0, 8));
if (estadoCabecera == "EFE")
{
if (existenRegistrosHoja)
{
codigoEmpresa = sheet.GetString("B1").Trim();
//OT 8372 INI
//sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + nombreEmpresa.Substring(3, nombreEmpresa.Length - 3) + "_" + codigoEmpresa + "_" + anio + mes + ".txt");
//OT 8372 FIN
existenRegistrosHoja = false;
}
//codigoParticipacion = row[1].ToString().Trim();
codigoParticipacion = row[0].ToString().Trim();
//nombreFondo = row[7].ToString().Trim();//OT 6134
nombreFondo = row[1].ToString().Trim();
//montoAporte = Convert.ToDouble(row[6].ToString().Trim()); //OT 6134
montoAporte = Convert.ToDouble(row[2].ToString().Trim()); //OT 6134
//OT 8372 INI
#region Agregar un registro al DataTable dtRegistrosHoja
DataRow newRegistroHoja = dtRegistrosHoja.NewRow();
newRegistroHoja["CODIGO_PARTICIPACION"] = codigoParticipacion;
newRegistroHoja["NOMBRE_FONDO"] = nombreFondo;
newRegistroHoja["MONTO_APORTE"] = montoAporte;
dtRegistrosHoja.Rows.Add(newRegistroHoja);
#endregion
#region Se comenta código: El procesamiento de los registros ahora se hará al terminar de leer los registros de toda la hoja
/*
* string filtro = "CODIGO = '" + codigoParticipacion + "' AND FONDO = '" + nombreFondo + "' AND MONTO_CONCEPTO = " + montoAporte.ToString();
* DataRow[] dDetalleConvenio = dtDetalleConvenio.Select(filtro, "ID_CONVENIO");
* //Decimal idConvenio =0;
* Decimal idConvenioDetalle = 0;
* foreach (DataRow dr in dDetalleConvenio)
* {
* //if (idConvenio == 0|| idConvenio > 0 && idConvenio == Convert.ToDecimal(dr["ID_CONVENIO"]))
* if (idConvenioDetalle == 0 || idConvenioDetalle > 0 && idConvenioDetalle == Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]))
* {
* idConvenioDetalle = Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]);
*
* sbLinea.Append(codigoParticipacion.Replace("-", "").Trim());
* drFondo = dtFondos.Select("NOMBRE = '" + nombreFondo + "'");
* dtFondosDepositos = conveniosPlanEmpleadorDA.ObtenerTablaGeneralDepositos(codigoTablatablaGeneralDepositos);
* idFondo = drFondo[0]["ID"].ToString();
* drFondoDeposito = dtFondosDepositos.Select("DESCRIPCION_CORTA = '" + idFondo + "'");
* sbLinea.Append(drFondoDeposito[0]["LLAVE_TABLA"].ToString());
*
* sbLinea.Append(anio + mes);
* sbLinea.Append(tipoMovimiento);
*
* tipoAporte = dr["CODIGO_CONCEPTO"].ToString().Trim();
* sbLinea.Append(tipoAporte);
*
* montoAporte = Convert.ToDouble(dr["MONTO_CONCEPTO"]);
* montoAporteCadena = montoAporte.ToString("000000.00");
* montoAporteCadena = montoAporteCadena.Replace(".", "");
* sbLinea.Append(montoAporteCadena);
*
* sw.WriteLine(sbLinea);
* sbLinea.Length = 0;
* }
* else
* break;
* }
*
* for (int j = dtDetalleConvenio.Rows.Count - 1; j >= 0; --j)
* {
* DataRow drDetalleConvenio = dtDetalleConvenio.Rows[j];
* if (idConvenioDetalle == Convert.ToDecimal(drDetalleConvenio["ID_CONVENIO_DETALLE"]))
* dtDetalleConvenio.Rows.Remove(drDetalleConvenio);
* }
*/
#endregion
//OT 8372 FIN
}
//row = sheet.GetRow(rowIndex++, 'B', 'J');//OT 6134
row = sheet.GetRow(rowIndex++, 'B', 'E');
cont = cont + 1;
}
//OT 8372 INI
#region Procesar todos los registros de la hoja almacenadas en el DataTable dtRegistrosHoja
//Obtener los fondos que aparecen en el DataTable dtRegistrosHoja
var ListaFondos = (from DataRow dRow in dtRegistrosHoja.Rows
select dRow["NOMBRE_FONDO"]).Distinct();
if (ListaFondos != null)
{
string filtroXNombreFondo;
foreach (var objNombreFondo in ListaFondos)
{
filtroXNombreFondo = "NOMBRE_FONDO = '" + objNombreFondo + "'";
DataRow[] dRegistrosXFondo = dtRegistrosHoja.Select(filtroXNombreFondo, "CODIGO_PARTICIPACION");
//Crear un archivo plano .txt por cada fondo
//OT 8372-PSC001 INICIO
/*
* sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + nombreEmpresa.Substring(3, nombreEmpresa.Length - 3)
+ "_" + codigoEmpresa + "_" + objNombreFondo + "_" + anio + mes + ".txt");
*/
sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + "F" + "_" + objNombreFondo + "._"
+ nombreEmpresa.Substring(3, nombreEmpresa.Length - 3)
+ "_" + codigoEmpresa + "_" + anio + mes + ".txt");
//OT 8372-PSC001 FIN
foreach (var dRegistroXFondo in dRegistrosXFondo)
{
#region procesar un registro
string filtro = "CODIGO = '" + dRegistroXFondo["CODIGO_PARTICIPACION"]
+ "' AND FONDO = '" + dRegistroXFondo["NOMBRE_FONDO"]
+ "' AND MONTO_CONCEPTO = " + dRegistroXFondo["MONTO_APORTE"].ToString();
DataRow[] dDetalleConvenio = dtDetalleConvenio.Select(filtro, "ID_CONVENIO");
//Decimal idConvenio =0;
Decimal idConvenioDetalle = 0;
foreach (DataRow dr in dDetalleConvenio)
{
//if (idConvenio == 0|| idConvenio > 0 && idConvenio == Convert.ToDecimal(dr["ID_CONVENIO"]))
if (idConvenioDetalle == 0 || idConvenioDetalle > 0 && idConvenioDetalle == Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]))
{
idConvenioDetalle = Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]);
sbLinea.Append(dRegistroXFondo["CODIGO_PARTICIPACION"].ToString().Replace("-", "").Trim());
drFondo = dtFondos.Select("NOMBRE = '" + dRegistroXFondo["NOMBRE_FONDO"] + "'");
dtFondosDepositos = conveniosPlanEmpleadorDA.ObtenerTablaGeneralDepositos(codigoTablatablaGeneralDepositos);
idFondo = drFondo[0]["ID"].ToString();
drFondoDeposito = dtFondosDepositos.Select("DESCRIPCION_CORTA = '" + idFondo + "'");
sbLinea.Append(drFondoDeposito[0]["LLAVE_TABLA"].ToString());
sbLinea.Append(anio + mes);
sbLinea.Append(tipoMovimiento);
tipoAporte = dr["CODIGO_CONCEPTO"].ToString().Trim();
sbLinea.Append(tipoAporte);
montoAporte = Convert.ToDouble(dr["MONTO_CONCEPTO"]);
montoAporteCadena = montoAporte.ToString("000000.00");
montoAporteCadena = montoAporteCadena.Replace(".", "");
sbLinea.Append(montoAporteCadena);
sw.WriteLine(sbLinea);
sbLinea.Length = 0;
}
else
{
break;
}
}
for (int j = dtDetalleConvenio.Rows.Count - 1; j >= 0; --j)
{
DataRow drDetalleConvenio = dtDetalleConvenio.Rows[j];
if (idConvenioDetalle == Convert.ToDecimal(drDetalleConvenio["ID_CONVENIO_DETALLE"]))
{
dtDetalleConvenio.Rows.Remove(drDetalleConvenio);
}
}
#endregion
}
if (sw != null)
{
sw.Close();
}
}
dtRegistrosHoja.Rows.Clear();
}
#endregion
//OT 8372 FIN
if (cont > 0)
{
if (sw != null)
{
sw.Close();
}
}
existenRegistrosHoja = true;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
wb.Close();
excelApplication.Close();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
}
//OT 8372-PSC001
public void generarArchivoUnico(string rutaArchivo, string rutaArchivoDestino)
{
StringBuilder sbLinea;
sbLinea = new StringBuilder("");
//StreamWriter sw = null;
string path = rutaArchivo;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApplication excelApplication = new ExcelApplication();
ExcelWorkBook wb = excelApplication.OpenWorkBook(path, ExcelMode.Full);
try
{
ConveniosPlanEmpleadorDA conveniosPlanEmpleadorDA = new ConveniosPlanEmpleadorDA();
string codigosEmpresas = "COD_EMP_CON";
DataTable dtCodigosEmpresas = conveniosPlanEmpleadorDA.ObtenerTablaGeneral(codigosEmpresas);
int numeroEmpresas = dtCodigosEmpresas.Rows.Count;
object[] row;
int rowIndex;
string codigoParticipacion;
string nombreFondo;
DataRow[] drFondo;
DataTable dtFondosDepositos;
DataRow[] drFondoDeposito;
string idFondo;
string codigoTablatablaGeneralDepositos = "FONDO_EQUIV";
string mes = "";
string anio = "";
string estadoFondos = "ACT";
DataTable dtFondos = conveniosPlanEmpleadorDA.ListarFondos(estadoFondos);
if (numeroEmpresas == 0)
{
//no hay empresas
}
else
{
string periodo;
string[] periodoElementos;
string tipoMovimiento = "I";
string tipoAporte = ""; //"P"
double montoAporte;
string montoAporteCadena;
string nombreEmpresa;
bool existenRegistrosHoja = true;
string codigoEmpresa = "";
string constanteCP = "CP_";
// int numeroHojas = numeroEmpresas * 2 + 1;
int numeroHojas = 0;
//OT 6891 inicio
int cantidadHojas = wb.GetSheetCount();
if (cantidadHojas == 3)
{
numeroHojas = wb.GetSheetCount();
}
else
{
numeroHojas = numeroEmpresas * 2 + 1;
}
//for (int i = 1; i <= numeroEmpresas; i++)
for (int i = 1; i <= numeroHojas; i++)
{
//ExcelWorkSheet sheet = wb.GetSheet(i + 1);
//Toma a partir de la 4ta hoja porque las 3 primeras son plantillas vacías.
//ExcelWorkSheet sheet = wb.GetSheet(i + 1);
//Toma a partir de la 4ta hoja porque las 3 primeras son plantillas vacías.
// OT 6891
ExcelWorkSheet sheet = null;
if (cantidadHojas == 3)
{
sheet = wb.GetSheet(i);
}
else
{
sheet = wb.GetSheet(i + 1);
}
// OT 6891 fin
nombreEmpresa = sheet.getName();
if (nombreEmpresa.StartsWith(constanteCP))
{
//StreamWriter sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + nombreEmpresa + "_" + anio + mes + ".txt");
StreamWriter sw = null;
String estadoCabecera;
//if (i == 1)
//{
periodo = sheet.GetString("B8").Substring(4, 10);
periodoElementos = periodo.Split('/');
anio = periodoElementos[2].Trim();
mes = periodoElementos[1].Trim();
//}
codigoEmpresa = sheet.GetString("B1").Trim();
DataTable dtDetalleConvenio = conveniosPlanEmpleadorDA.ObtenerDetalleConvenios(codigoEmpresa, Convert.ToInt32(mes), Convert.ToInt32(anio));
rowIndex = 13;//13
//row = sheet.GetRow(rowIndex++, 'B', 'J'); //OT 6134
row = sheet.GetRow(rowIndex++, 'B', 'E');
int cont = 0;
while (row[0] != null)
{
//if (row[8].ToString().Trim() == "EFE")
//if (row[3].ToString().Trim() == "EFE")
estadoCabecera = ObtienePestañaEmpresa(wb, nombreEmpresa.Substring(constanteCP.Length), numeroHojas, row[0].ToString().Substring(0, 8));
if (estadoCabecera == "EFE")
{
if (existenRegistrosHoja)
{
codigoEmpresa = sheet.GetString("B1").Trim();
//OT 8372-PSC001 INICIO
//sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + nombreEmpresa.Substring(3, nombreEmpresa.Length - 3) + "_" + codigoEmpresa + "_" + anio + mes + ".txt");
sw = new StreamWriter(rutaArchivoDestino + "\\" + "PCP_" + "T" + "_"
+ nombreEmpresa.Substring(3, nombreEmpresa.Length - 3) + "_" + codigoEmpresa + "_" + anio + mes + ".txt");
//OT 8372-PSC001 FIN
existenRegistrosHoja = false;
}
//codigoParticipacion = row[1].ToString().Trim();
codigoParticipacion = row[0].ToString().Trim();
//nombreFondo = row[7].ToString().Trim();//OT 6134
nombreFondo = row[1].ToString().Trim();
//montoAporte = Convert.ToDouble(row[6].ToString().Trim()); //OT 6134
montoAporte = Convert.ToDouble(row[2].ToString().Trim()); //OT 6134
string filtro = "CODIGO = '" + codigoParticipacion + "' AND FONDO = '" + nombreFondo + "' AND MONTO_CONCEPTO = " + montoAporte.ToString();
DataRow[] dDetalleConvenio = dtDetalleConvenio.Select(filtro, "ID_CONVENIO");
//Decimal idConvenio =0;
Decimal idConvenioDetalle = 0;
foreach (DataRow dr in dDetalleConvenio)
{
//if (idConvenio == 0|| idConvenio > 0 && idConvenio == Convert.ToDecimal(dr["ID_CONVENIO"]))
if (idConvenioDetalle == 0 || idConvenioDetalle > 0 && idConvenioDetalle == Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]))
{
idConvenioDetalle = Convert.ToDecimal(dr["ID_CONVENIO_DETALLE"]);
sbLinea.Append(codigoParticipacion.Replace("-", "").Trim());
drFondo = dtFondos.Select("NOMBRE = '" + nombreFondo + "'");
dtFondosDepositos = conveniosPlanEmpleadorDA.ObtenerTablaGeneralDepositos(codigoTablatablaGeneralDepositos);
idFondo = drFondo[0]["ID"].ToString();
drFondoDeposito = dtFondosDepositos.Select("DESCRIPCION_CORTA = '" + idFondo + "'");
sbLinea.Append(drFondoDeposito[0]["LLAVE_TABLA"].ToString());
sbLinea.Append(anio + mes);
sbLinea.Append(tipoMovimiento);
tipoAporte = dr["CODIGO_CONCEPTO"].ToString().Trim();
sbLinea.Append(tipoAporte);
montoAporte = Convert.ToDouble(dr["MONTO_CONCEPTO"]);
montoAporteCadena = montoAporte.ToString("000000.00");
montoAporteCadena = montoAporteCadena.Replace(".", "");
sbLinea.Append(montoAporteCadena);
sw.WriteLine(sbLinea);
sbLinea.Length = 0;
}
else
{
break;
}
}
for (int j = dtDetalleConvenio.Rows.Count - 1; j >= 0; --j)
{
DataRow drDetalleConvenio = dtDetalleConvenio.Rows[j];
if (idConvenioDetalle == Convert.ToDecimal(drDetalleConvenio["ID_CONVENIO_DETALLE"]))
{
dtDetalleConvenio.Rows.Remove(drDetalleConvenio);
}
}
}
//row = sheet.GetRow(rowIndex++, 'B', 'J');//OT 6134
row = sheet.GetRow(rowIndex++, 'B', 'E');
cont = cont + 1;
}
if (cont > 0)
{
if (sw != null)
{
sw.Close();
}
}
existenRegistrosHoja = true;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
wb.Close();
excelApplication.Close();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
}
/// <summary>
/// �w�肳�ꂽExcel�t�@�C����ǂݍ���
/// </summary>
private void ReadExcel()
{
//Excel�t�@�C���I����A�I�����Ȃ���ΏI���B
if (this.openFileExcel.ShowDialog() != DialogResult.OK)
{
return;
}
Address_DS ads = new Address_DS();
Address_DS errords = new Address_DS();
// ExcelWorkBook�I�[�v��
ExcelWorkBook wBook = new ExcelWorkBook();
wBook.Open(this.openFileExcel.FileName);
try
{
//�P�s�ڂ�ǂݍ���
ExcelWorkSheet sheet = wBook.WorkSheets[0];
ArrayList columnList = new ArrayList();
for (int col = sheet.FirstColumn; col <= sheet.LastColumn; col++)
{
if (sheet.Cells[0, col].Value == null)
{
continue;
}
columnList.Add(sheet.Cells[0, col].Value);
}
//�ǂݍ��ݍs���Ȃ��ꍇ�͋�̃f�[�^�Z�b�g��Ԃ��B
if (columnList.Count == 0)
{
MessageBox.Show(this, "�ǂݍ��݉\�ȍs������܂���B");
return;
}
using (ColumnSelectForm f = new ColumnSelectForm(columnList))
{
//�s�I���ʂ�\�����A���[�U�ɓǂݍ��ލs��w�肵�Ă�炤
if (f.ShowDialog() != DialogResult.OK)
{
//�I�����Ȃ���A��̃f�[�^�Z�b�g��Ԃ��B
return ;
}
int readStartRowIndex = 0;
if (f.ReadFirstRow == true) readStartRowIndex = 0;
else readStartRowIndex = 1;
//���[�U���w�肵�����ǂݍ���
for (int row = readStartRowIndex; row <= sheet.LastRow; row++)
{
//NULL�₷�łɎ�荞�܂ꂽ���[���A�h���X�͂͂����B
if (!((sheet.Cells[row, f.SelectColumnIndex].Value == null) ||
ads.Address.FindByMailAddress(sheet.Cells[row, f.SelectColumnIndex].Value.ToString()) != null))
{
string mailAddr = sheet.Cells[row, f.SelectColumnIndex].Value.ToString();
//���[���A�h���X�`���̃A�h���X�̂ݓo�^
if (TKMP.Writer.MailAddressCollection.IsAddressPattern(mailAddr) == true)
{
Address_DS.AddressRow aRow = ads.Address.NewAddressRow();
aRow.MailAddress = mailAddr;
ads.Address.AddAddressRow(aRow);
}
else
{
//���[���A�h���X�`���łȂ���̂́A�G���[���X�g�ɕۑ�
Address_DS.AddressRow aeRow = errords.Address.NewAddressRow();
aeRow.MailAddress = mailAddr;
errords.Address.AddAddressRow(aeRow);
}
}
}
}
}
finally
{
// ExcelWorkBook�N���[�Y
wBook.Close();
}
//Excel�t�@�C����ǂݎ��B
this._addressds.Merge(ads);
//�G���[����Z�b�g����B
this._errorForm.ErrorList = errords;
}
private void SaveIntervalExcel_Click(object sender, RoutedEventArgs e)
{
if (intervalControl.Open)
{
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Документ Excel(*.xlsx)|*.xlsx";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
string filename = sfd.FileName;
int rows = intervalControl.IntervalGrid.Count();
double[] lBorder = intervalControl.IntervalGrid.Select(p => p.leftBorder).ToArray();
double[] rBorder = intervalControl.IntervalGrid.Select(p => p.rightBorder).ToArray();
double[] frequency = intervalControl.IntervalGrid.Select(p => p.frequency).ToArray();
double[] accumulatedFrequency = intervalControl.IntervalGrid.Select(p => p.accumulatedFrequency).ToArray();
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[1, 1] = "Левая граница";
ExcelWorkSheet.Cells[1, 2] = "Правая граница";
ExcelWorkSheet.Cells[1, 3] = "Частота";
ExcelWorkSheet.Cells[1, 4] = "Накопленная частота";
ExcelWorkSheet.Cells[1, 5] = "Расчеты";
ExcelWorkSheet.Cells[1, 1].Font.Bold = true;
ExcelWorkSheet.Cells[1, 2].Font.Bold = true;
ExcelWorkSheet.Cells[1, 3].Font.Bold = true;
ExcelWorkSheet.Cells[1, 4].Font.Bold = true;
ExcelWorkSheet.Cells[1, 5].Font.Bold = true;
for (int i = 0; i < rows; i++)
{
ExcelWorkSheet.Cells[i + 2, 1] = lBorder[i];
ExcelWorkSheet.Cells[i + 2, 2] = rBorder[i];
ExcelWorkSheet.Cells[i + 2, 3] = frequency[i];
ExcelWorkSheet.Cells[i + 2, 4] = accumulatedFrequency[i];
}
ExcelWorkSheet.Cells[2, 5] = intervalControl.tbAverageValue.Text;
ExcelWorkSheet.Cells[3, 5] = intervalControl.tbMode.Text;
ExcelWorkSheet.Cells[4, 5] = intervalControl.tbMedian.Text;
ExcelWorkSheet.Cells[5, 5] = intervalControl.tbRangeOfVariation.Text;
ExcelWorkSheet.Cells[6, 5] = intervalControl.tbMeanLinearDeviation.Text;
ExcelWorkSheet.Cells[7, 5] = intervalControl.tbDispersion.Text;
ExcelWorkSheet.Cells[8, 5] = intervalControl.tbStandardDeviation.Text;
ExcelWorkSheet.Cells[9, 5] = intervalControl.tbCoefficientVariation.Text;
ExcelWorkSheet.Cells[10, 5] = intervalControl.tbNormalCoefficientAsymmetry.Text;
ExcelWorkSheet.Cells[11, 5] = intervalControl.tbEstimationCoefficientAsymmetry.Text;
ExcelWorkSheet.Cells[12, 5] = intervalControl.tbDegreeAsymmetry.Text;
ExcelWorkSheet.Cells[13, 5] = intervalControl.tbMaterialityAsymmetry.Text;
ExcelWorkSheet.Cells[14, 5] = intervalControl.tbExcess.Text;
ExcelWorkSheet.Cells[15, 5] = intervalControl.tbExcessError.Text;
ExcelWorkSheet.Columns.AutoFit();
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close();
ExcelApp.Quit();
}
else
{
dialogError.IsOpen = true;
}
}
private void HideDeletePage(ExcelApp.Application ExcelApp, IEnumerable <cParameter> ResPar, string[] DeletePages, string[] HidePages, StringBuilder pSuccess, StringBuilder pError, ref bool Result, string pPathCopy = null, bool IsShort = false)
{
ExcelApp.Workbook ExcelWorkBook;
foreach (var el in ResPar)
{
ExcelWorkBook = ExcelApp.Workbooks.Open(el.FileName);
if (IsShort)
{
// var s = Path.GetDirectoryName(el.FileName);
string FN = Path.Combine(Path.GetDirectoryName(el.FileName), Path.GetFileNameWithoutExtension(el.FileName) + "_short" + Path.GetExtension(el.FileName));
el.FileName = FN;
}
if (DeletePages != null)
{
foreach (var page in DeletePages)
{
try
{
ExcelApp.Worksheet worksheet = (ExcelApp.Worksheet)ExcelWorkBook.Worksheets[page];
worksheet.Delete();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
catch (Exception ex)
{
Result = false;
var err = $"{DateTime.Now} Delete Page={page} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}";
pError.Append(err);
pSuccess.Append(err);
}
}
}
//Ховаєм сторінки
if (HidePages != null)
{
foreach (var page in HidePages)
{
try
{
ExcelApp.Worksheet worksheet = (ExcelApp.Worksheet)ExcelWorkBook.Worksheets[page];
worksheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
catch (Exception ex)
{
Result = false;
var err = $"{DateTime.Now} Hide Page={page} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}";
pError.Append(err);
pSuccess.Append(err);
}
}
}
try
{
ExcelWorkBook.SaveAs(el.FileName);
}
catch (Exception ex)
{
Result = false;
var err = $"{DateTime.Now} Save={el.FileName} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}";
pError.Append(err);
pSuccess.Append(err);
}
if (!string.IsNullOrEmpty(pPathCopy))
{
string NewFile = Path.Combine(pPathCopy, Path.GetFileName(el.FileName));
try
{
if (File.Exists(NewFile))
{
File.Delete(NewFile);
}
ExcelWorkBook.SaveAs(NewFile);
el.CopyFileName = NewFile;
}
catch (Exception ex)
{
Result = false;
var err = $"{DateTime.Now} SaveCopy={NewFile} {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}";
pError.Append(err);
pSuccess.Append(err);
}
}
if (ExcelWorkBook != null)
{
ExcelWorkBook.Close(false);
}
if (ExcelWorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook);
ExcelWorkBook = null;
}
}
}
public void PrepararPlantilla(string sourcePath, string savePath, string codigoUsuario)
{
ExcelApplication excelApplication = new ExcelApplication();
ExcelWorkBook workBook = null;
workBook = excelApplication.OpenWorkBook(sourcePath, ExcelMode.Full);
ExcelWorkSheet sheet = workBook.GetSheet(2);
ExcelWorkSheet sheetCucs = workBook.GetSheet(3);
//Obtiene los prospectos vacios
CargaContratoDA cargaContratoDA = new CargaContratoDA();
DataTable dtProspectos = cargaContratoDA.ObtenerListaProspectosVacios();
sheetCucs.EscribirDatatable("B", 2, dtProspectos);
DataTable dtDISTRITO = cargaContratoDA.ObtenerListaDistritos();
int indis = 2;
foreach (DataRow dr in dtDISTRITO.Rows)
{
sheet.SetValue(string.Format("W{0}", indis), dr["NOMBRE"]);
sheet.SetValue(string.Format("X{0}", indis), dr["ID"]);
sheet.SetValue(string.Format("Y{0}", indis++), dr["CIUDAD"]);
}
FondoDA fondoDA = new FondoDA();
DataTable dtTiposDocumentos = fondoDA.ObtenerLista("TPDOCS");
int index = 2;
foreach (DataRow dr in dtTiposDocumentos.Rows)
{
sheet.SetValue(string.Format("K{0}", index), dr["DESCRIPCION_LARGA"]);
sheet.SetValue(string.Format("L{0}", index++), dr["LLAVE_TABLA"]);
}
index = 2;
DataTable dtAgenciasOrigen = fondoDA.ObtenerLista("AGEORI");
foreach (DataRow dr in dtAgenciasOrigen.Rows)
{
sheet.SetValue(string.Format("H{0}", index), dr["DESCRIPCION_LARGA"]);
sheet.SetValue(string.Format("I{0}", index++), dr["LLAVE_TABLA"]);
}
index = 2;
DataTable dtFondos = cargaContratoDA.ObtenerListaFondos();
DataRow[] drFondos = dtFondos.Select("1=1", "NOMBRE");
foreach (DataRow dr in drFondos)
{
sheet.SetValue(string.Format("E{0}", index), dr["NOMBRE"]);
sheet.SetValue(string.Format("F{0}", index++), dr["ID"]);
}
sheet.Hide();
excelApplication.GetWorkBook(1).SaveAs(savePath);
workBook.Close();
excelApplication.Close();
workBook = excelApplication.OpenWorkBook(savePath, ExcelMode.Full);
excelApplication.Show();
}