public void Close()
{
File = "";
Worksheets.Clear();
_workbook?.Close();
_sheet = null;
}
public bool CreateExcel(List<Po> pos)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Po po in pos)
{
xlWorkSheet.Cells[po.Row, po.Column] = po.Value;
}
xlWorkBook.SaveAs("C:\\newcat2", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
int test = 0;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
private static void ConvertExcel(string fileName, string sourceFile)
{
Microsoft.Office.Interop.Excel.Workbook excelDoc = null;
Microsoft.Office.Interop.Excel.Application excel = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application
{
Visible = false,
DisplayAlerts = false
};
excelDoc = excel.Workbooks.Open(sourceFile, ReadOnly: true, Delimiter: ";", Format: 6, Origin: Microsoft.Office.Interop.Excel.XlPlatform.xlWindows);
foreach (Microsoft.Office.Interop.Excel.Worksheet currentSheet in excelDoc.Worksheets)
{
currentSheet.Columns.AutoFit();
}
excelDoc.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault);
excel.DisplayAlerts = true;
excelDoc.Close();
excel.Quit();
}
catch (Exception ex)
{
try
{
excel.DisplayAlerts = true;
excelDoc?.Close();
excel?.Quit();
}
catch { }
throw;
}
}
static void Main(string[] args)
{
List <statusSQL> Records = new List <statusSQL>();
string TableName = (@"Placeholder");
var directory = new DirectoryInfo(@"Placeholder");
var myFile = (from f in directory.GetFiles()
orderby f.LastWriteTime descending
select f).First();
Console.WriteLine(myFile.Extension);
using (SqlConnection Connection = new SqlConnection())
{
string commandText = "DELETE FROM ";
SqlCommand command = new SqlCommand(commandText, Connection);
try
{
Connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
if (myFile.Extension == ".xlsx")
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(myFile.FullName);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
//var x = xlWorksheet.Columns.Value;
int rowCount = xlRange.Rows.Count;
for (int i = 1; i <= rowCount; i++)
{
if (i == 1)
{
continue;
}
statusSQL Record = new statusSQL();
Record.NodeName = xlRange.Cells[i, 1].Value2.ToString();
Record.Instance = xlRange.Cells[i, 2].Value2.ToString();
Record.DBname = xlRange.Cells[i, 3].Value2.ToString();
Record.LogicalFileName = xlRange.Cells[i, 4].Value2.ToString();
Record.PathFromSQL = xlRange.Cells[i, 5].Value2.ToString();
Record.FileType = xlRange.Cells[i, 6].Value2.ToString();
Record.NodeNameVormetric = xlRange.Cells[i, 7].Value2.ToString();
Record.GuardPointPath = xlRange.Cells[i, 8].Value2.ToString();
Record.EncryptionStatus = xlRange.Cells[i, 9].Value2.ToString();
Record.Application = xlRange.Cells[i, 10].Value2.ToString();
Record.State = xlRange.Cells[i, 11].Value2.ToString();
Record.Environment = xlRange.Cells[i, 12].Value2.ToString();
Record.LastUpdate = xlRange.Cells[i, 13].Value.ToString("dd/MM/yyyy");
Records.Add(Record);
Console.WriteLine(i);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
else
{
var fileStream = new FileStream(myFile.FullName, FileMode.Open, FileAccess.Read);
Console.WriteLine(fileStream);
using (var streamReader = new StreamReader(fileStream, Encoding.UTF8))
{
streamReader.ReadLine();
while (streamReader.Peek() >= 0)
{
String line = streamReader.ReadLine();
Regex regex = new Regex(",(?=.*\\\")");
string match = regex.Replace(line, "_");
var Rows = line.Split(new Char[] { ',' }).ToList();
if (Rows.Count() < 13 || Rows.Count() > 13)
{
Console.Write("Error :"); Console.Write("CSV contains " + Rows.Count().ToString() + " Columns"); continue;
}
Rows = Rows.Select(x => x.ToLower().Trim()).ToList();
statusSQL Record = new statusSQL();
Record.NodeName = Rows[0].Trim().ToLower();
Record.Instance = Rows[1];
Record.DBname = Rows[2];
Record.LogicalFileName = Rows[3];
Record.PathFromSQL = Rows[4];
Record.FileType = Rows[5];
Record.NodeNameVormetric = Rows[6];
Record.GuardPointPath = Rows[7];
Record.EncryptionStatus = Rows[8];
Record.Application = Rows[9];
Record.State = Rows[10];
Record.Environment = Rows[11];
Record.LastUpdate = Rows[12];
Records.Add(Record);
}
}
}
using (var db = new DataEntities1())
{
int count = 0;
foreach (statusSQL record in Records)
{
Console.WriteLine(count); count++; db.statusSQLs.Add(record);
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
List <DatabaseEncryptionLog> DatabaseEncryptionLogs = new List <DatabaseEncryptionLog>();
foreach (statusSQL record in Records)
{
DatabaseEncryptionLog Log = new DatabaseEncryptionLog();
Log.Application = record.Application;
Log.EncryptionStatus = record.EncryptionStatus;
Log.LastUpdate = record.LastUpdate;
Log.Tally = 1;
DatabaseEncryptionLogs.Add(Log);
}
EqualityComparer EqualityComparer = new EqualityComparer();
var UniqueLogs = DatabaseEncryptionLogs.Distinct(EqualityComparer).ToList();
for (var i = 0; i < UniqueLogs.Count(); i++)
{
int LogCount = DatabaseEncryptionLogs.Where(x => x.Application == UniqueLogs[i].Application && x.EncryptionStatus == UniqueLogs[i].EncryptionStatus && x.LastUpdate == UniqueLogs[i].LastUpdate).Count();
UniqueLogs[i].Tally = LogCount;
}
using (var db = new DataEntities1())
{
foreach (DatabaseEncryptionLog record in UniqueLogs)
{
db.DatabaseEncryptionLogs.Add(record);
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
public void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
Data data = (Data) e.Argument; // Cast e argument to a local Data instance
// Start Excel/Multiframe calls - Either 11 or 20 stages, depending on debugMode (11 is debugMode, skips creating frame. Does debug info only)
bool debugMode = Properties.Settings.Default.DebugMode;
int progressTotal = debugMode == true ? 11 : 20;
int progressCount = 0;
bool unableToAddQ2Loads = false;
try
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Opening Excel"); progressCount++;
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = Properties.Settings.Default.DisplayExcel;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Open(fileName);
ws = wb.Sheets.get_Item(1); // Expects "Input" sheet to be the first worksheet
// Insert values into Excel Inputs Sheet
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Inserting values into Input Sheet"); progressCount++;
ws.Range["windSpeedVu"].Value = data.ultimateWindSpeed;
ws.Range["windSpeedVs"].Value = data.serviceWindSpeed;
ws.Range["internalPressure"].Value = data.internalPressure;
ws.Range["shedType"].Value = data.roofType;
ws.Range["shedWallType"].Value = data.wallType;
ws.Range["shedEaveHeight"].Value = data.eaveHeight;
ws.Range["shedRoofPitch"].Value = data.roofPitch;
ws.Range["shedSpan"].Value = data.span;
ws.Range["shedBaySpacing"].Value = data.baySpacing;
ws.Range["shedNumberOfBays"].Value = data.numberOfBays;
ws.Range["shedSupports"].Value = data.supports;
ws.Range["shedEndColumnType"].Value = data.endColumnType;
ws.Range["shedEndColumnSection"].Value = data.endColumnSection;
ws.Range["shedEndRafterType"].Value = data.endRafterType;
ws.Range["shedEndRafterSection"].Value = data.endRafterSection;
ws.Range["shedMidColumnType"].Value = data.midColumnType;
ws.Range["shedMidColumnSection"].Value = data.midColumnSection;
ws.Range["shedMidRafterType"].Value = data.midRafterType;
ws.Range["shedMidRafterSection"].Value = data.midRafterSection;
ws.Range["shedMullions"].Value = data.numberOfMullions;
ws.Range["shedMullionsType"].Value = data.mullionType;
ws.Range["shedMullionsSection"].Value = data.mullionSection;
ws.Range["shedCompressionStrutType"].Value = data.strutType;
ws.Range["shedCompressionStrutSection"].Value = data.strutSection;
ws.Range["shedKneeBraceEnd"].Value = data.endKneeBraceType == "None" ? "no" : "yes";
if (data.endKneeBraceType != "None")
{
ws.Range["shedKneeBraceEndType"].Value = data.endKneeBraceType;
ws.Range["shedKneeBraceEndSection"].Value = data.endKneeBraceSection;
}
ws.Range["shedKneeBraceMid"].Value = data.midKneeBraceType == "None" ? "no" : "yes";
if (data.midKneeBraceType != "None")
{
ws.Range["shedKneeBraceMidType"].Value = data.midKneeBraceType;
ws.Range["shedKneeBraceMidSection"].Value = data.midKneeBraceSection;
}
ws.Range["shedKneeBracePercentEave"].Value = data.kneeBracePercentEave;
ws.Range["shedKneeBracePercentSpan"].Value = data.kneeBracePercentSpan;
ws.Range["shedApexBraceEnd"].Value = data.endApexBraceType == "None" ? "no" : "yes";
if (data.endApexBraceType != "None")
{
ws.Range["shedApexBraceEndType"].Value = data.endApexBraceType;
ws.Range["shedApexBraceEndSection"].Value = data.endApexBraceSection;
}
ws.Range["shedApexBraceMid"].Value = data.midApexBraceType == "None" ? "no" : "yes";
if (data.midApexBraceType != "None")
{
ws.Range["shedApexBraceMidType"].Value = data.midApexBraceType;
ws.Range["shedApexBraceMidSection"].Value = data.midApexBraceSection;
}
ws.Range["shedEavePurlinType"].Value = data.eavePurlinType;
ws.Range["shedEavePurlinSection"].Value = data.eavePurlinSection;
ws.Range["shedRoofPurlinType"].Value = data.strutType;
ws.Range["shedRoofPurlinSection"].Value = data.strutSection;
// Start calling macros from Excel Workbook
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting initial excel values"); progressCount++;
excelApp.Run("CsharpSetExcelInputValues");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting units in Multiframe"); progressCount++;
excelApp.Run("CsharpSetUnits");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Frame"); progressCount++;
excelApp.Run("CsharpCreateFrame");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Knee Braces"); progressCount++;
excelApp.Run("CsharpAddKneeBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Apex Braces"); progressCount++;
excelApp.Run("CsharpAddApexBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Mullions"); progressCount++;
excelApp.Run("CsharpAddMullions");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Eave Purlins"); progressCount++;
excelApp.Run("CsharpAddEavePurlins");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Restraints and Sections"); progressCount++;
excelApp.Run("CsharpAddRestraintsAndSections");
}
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Wind Pressures"); progressCount++;
excelApp.Run("CsharpGetWindPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Longwind Reductions"); progressCount++;
excelApp.Run("CsharpGetLWPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Copying Pressures to Loads Sheet"); progressCount++;
excelApp.Run("CsharpCopyPressures");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating New Load Cases"); progressCount++;
excelApp.Run("CsharpCreateNewLoadCases");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting up Loads"); progressCount++;
excelApp.Run("CsharpAddLoadsSetup");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Q2 Loads"); progressCount++;
unableToAddQ2Loads = (bool)excelApp.Run("CsharpAddQ2Loads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Portal Loads"); progressCount++;
excelApp.Run("CsharpAddEndPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Mid Portal Loads"); progressCount++;
excelApp.Run("CsharpAddMidPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Wall Loads"); progressCount++;
excelApp.Run("CsharpAddEndWallLoads");
}
if (debugMode == true)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Geometry Ratios"); progressCount++;
data.excelGeometryRatios = (double[]) excelApp.Run("CsharpGetGeometryRatiosDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Pressure Coefficients"); progressCount++;
data.excelPressureCoefficients = (double[]) excelApp.Run("CsharpGetPressureCoefficientsDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Load Cases"); progressCount++;
data.excelLoadCases = (string[])excelApp.Run("CsharpGetLoadCasesDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Loads"); progressCount++;
data.excelLoads = (string[,]) excelApp.Run("CsharpGetLoadsDebug");
}
// Alert if Q2 loads were unable to be added
if (unableToAddQ2Loads == true)
MessageBox.Show("Q2 loads were unable to be added. You will need to add them manually", "Unable to add Q2 Loads");
}
catch (Exception ex)
{
if (ex is ArgumentException || ex is ArgumentNullException)
MessageBox.Show("Error closing Excel (Marshal.ReleaseFinalComObject)\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
else
MessageBox.Show("Error closing Excel\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
}
finally
{
// Close Excel
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Closing Excel"); progressCount++;
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
wb.Close();
Marshal.FinalReleaseComObject(wb);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
private void btnExportExcel_Click(object sender, RoutedEventArgs e)
{
//Set Filter being displayed
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Export";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
//If user presses OK
if (saveDialog.ShowDialog() == true)
{
//get excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Add workbook to excel
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
//Store worksheet
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
try
{
//Get active worksheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)(workbook.ActiveSheet);
worksheet.Cells[1, 1] = "STT";
worksheet.Cells[1, 2] = "Mã món ăn";
worksheet.Cells[1, 3] = "Tên món ăn";
worksheet.Cells[1, 4] = "Số lượng";
worksheet.Cells[1, 5] = "Đơn giá";
worksheet.Cells[1, 6] = "Thành tiền";
//Set the value for remaining row of worksheet by the value being presented in GridView
for (int iRow = 0; iRow < dataGridReport.Items.Count; iRow++)
{
//get current row
ReportDetailInfo reportDetailInfo = (ReportDetailInfo)dataGridReport.Items[iRow];
worksheet.Cells[iRow + 2, 1] = reportDetailInfo.STT;
worksheet.Cells[iRow + 2, 2] = reportDetailInfo.DishID;
worksheet.Cells[iRow + 2, 3] = reportDetailInfo.DishName;
worksheet.Cells[iRow + 2, 4] = reportDetailInfo.Quantity;
worksheet.Cells[iRow + 2, 5] = reportDetailInfo.UnitPrice;
worksheet.Cells[iRow + 2, 6] = reportDetailInfo.TotalPrice;
}
//Don't allow displaying alerts
//this is just a setup to prevent some kind of disturb things
excel.DisplayAlerts = false;
//Save Workbook
workbook.SaveAs(saveDialog.FileName);
excel.DisplayAlerts = true;
//Display successful dialog
MessageBox.Show("Xuất excel thành công", "Thành công", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Lỗi!!!", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
workbook.Close();
excel.Quit();
workbook = null;
excel = null;
GC.Collect();
}
}
}
private void GenerateExcel(bool isPDF, SaveFileDialog sfd)
{
CopyAlltoClipboard();
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int colStart = 1;
int addCol = 0;
int logoCellLeft = 0;
if (cboReports.SelectedValue.ToString() == "Absentees Report" || cboReports.SelectedValue.ToString() == "No Time Out Report")
{
addCol = 2;
colStart += addCol;
logoCellLeft = 100;
}
else if (cboReports.SelectedValue.ToString() == "Attendance Report")
{
logoCellLeft = 12;
}
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, colStart];
xlWorkSheet.Shapes.AddPicture(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @LOGO_PATH, MsoTriState.msoFalse, MsoTriState.msoCTrue, logoCellLeft, 0, 90, 90);
xlWorkSheet.get_Range("A1", "A1").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.get_Range("A1", "A1").Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
CR.Rows.AutoFit();
CR.get_Range("A1").Columns.EntireColumn.AutoFit();
CR.get_Range("C1", "K1").Columns.EntireColumn.AutoFit();
CR.get_Range("B1").Columns.ColumnWidth = 35;
if (cboReports.SelectedValue.ToString().Trim() == "Attendance Report")
{
CR.get_Range("D1", "K1").Columns.EntireColumn.AutoFit();
CR.get_Range("C1").Columns.ColumnWidth = 20;
}
CR.get_Range("B1").Cells.Style.WrapText = true;
xlWorkSheet.Cells[3, 3 + addCol - 1] = "' " + cboReports.SelectedValue.ToString();
xlWorkSheet.Cells[3, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[4, 3 + addCol - 1] = "' " + dtFrom.SelectedDate.Value.ToString("dd MMMMM yyyy") + " to " + dtTo.SelectedDate.Value.ToString("dd MMMM yyyy");
xlWorkSheet.Cells[4, 3 + addCol - 1].Cells.Style.WrapText = false;
xlWorkSheet.Cells[4, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.Cells[5, 3 + addCol - 1] = "' " + GetFilterUsed().Replace("_", "");
xlWorkSheet.Cells[5, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
int ctr = 1;
while (ctr <= dgResults.Columns.Count)
{
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, ctr + addCol]).Interior.Color = ColorTranslator.ToOle(Color.AliceBlue);
ctr++;
}
for (int ctrC = 1; ctrC <= dgResults.Columns.Count; ctrC++)
{
for (int ctrR = 8; ctrR <= dgResults.Items.Count + 8; ctrR++)
{
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[ctrR, ctrC + addCol]).BorderAround(LineStyle.Thin, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.AliceBlue));
}
}
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, colStart];
rg.EntireColumn.NumberFormat = "yyyy/MM/dd";
if (cboReports.SelectedValue.ToString() == "Consolidated Report")
{
rg.EntireColumn.NumberFormat = "@";
}
xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlexcel);
dgResults.UnselectAllCells();
Clipboard.Clear();
if (File.Exists(sfd.FileName) && !isPDF)
{
CheckIfFileIsOpen(sfd);
//System.Diagnostics.Process.Start(sfd.FileName);
System.Diagnostics.Process.Start(Path.GetDirectoryName(sfd.FileName));
}
}
public override void Exporter(string separateur = "")
{
#region Preparation des Objets
object misValue = System.Reflection.Missing.Value;
//Creer une application excel
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//Creer un workbook
Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(misValue);
//Ajouter un nouveau worksheet to workbook avec le nom d'evenement
Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = nomEvent;
#endregion
#region Header
int startRow = 1;
//La date du jours
if (date)
{
excelWorkSheet.Cells[startRow, 1] = "Date Géneration";
excelWorkSheet.Cells[startRow, 1].Font.Bold = true;
excelWorkSheet.Cells[startRow++, 2] = DateTime.Now.ToLongDateString();
}
excelWorkSheet.Cells[startRow, 1] = "Evenement";
excelWorkSheet.Cells[startRow, 1].Font.Bold = true;
excelWorkSheet.Cells[startRow, 1].Font.Size = 14;
excelWorkSheet.Cells[startRow++, 2] = nomEvent;
if (colsName)
{
//Ecrire Les Noms Des Colonnes
for (int i = 1; i < tbl.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[startRow, i] = tbl.Columns[i - 1].ColumnName;
}
}
#endregion
//Ecrire Les Données
for (int j = 0; j < tbl.Rows.Count; j++)
{
startRow++;
for (int k = 0; k < tbl.Columns.Count; k++)
{
excelWorkSheet.Cells[startRow, k + 1] = tbl.Rows[j][k].ToString();
}
}
#region Footer
startRow++;
//Nombre des enregistrements
if (count)
{
excelWorkSheet.Cells[startRow, 1] = "Nombre Des Enregistrement";
excelWorkSheet.Cells[startRow, 1].Font.Bold = true;
excelWorkSheet.Cells[startRow, 1].Font.Size = 14;
excelWorkSheet.Cells[startRow++, 2] = tbl.Rows.Count.ToString();
}
#region Les Sommes
if (sum != null && sum.Count > 0)
{
excelWorkSheet.Cells[startRow, 1].Font.Bold = true;
excelWorkSheet.Cells[startRow, 1].Font.Size = 14;
excelWorkSheet.Cells[startRow++, 1] = "Sommes Des Champs";//changer le font
//parcourir la liste des indices
foreach (var indx in sum)
{
List <double> valeurs = new List <double>();
//parcourir la table
for (int i = 0; i < tbl.Rows.Count; i++)
{
valeurs.Add(Convert.ToDouble(tbl.Rows[i][indx].ToString()));
}
excelWorkSheet.Cells[startRow, 1] = tbl.Columns[indx].ColumnName;
excelWorkSheet.Cells[startRow, 1].Font.Italic = true;
excelWorkSheet.Cells[startRow++, 2] = valeurs.Sum().ToString();
}
}
#endregion
#region Les Moyennes
if (avg != null && avg.Count > 0)
{
excelWorkSheet.Cells[startRow++, 1] = "Moyennes Des Champs";
//parcourir la liste des indices
foreach (var indx in avg)
{
List <double> valeurs = new List <double>();
//parcourir la table
for (int i = 0; i < tbl.Rows.Count; i++)
{
valeurs.Add(Convert.ToDouble(tbl.Rows[i][indx].ToString()));
}
excelWorkSheet.Cells[startRow, 1] = tbl.Columns[indx].ColumnName;
excelWorkSheet.Cells[startRow, 1].Font.Italic = true;
excelWorkSheet.Cells[startRow++, 2] = valeurs.Average().ToString();
}
}
#endregion
#endregion
//Sauveguarder le fichier
excelWorkBook.SaveAs(filename + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
excelWorkBook.Close();
excelApp.Quit();
//Liberer les COM
Marshal.ReleaseComObject(excelWorkSheet);
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApp);
}
/// <summary>
/// Schreibt ein aktuelles Backup in einem seperaten Ordner auf Höhe der .exe-Datei
/// </summary>
private void BackupLists(bool auto = false)
{
string filepath = "";
if (products.Count > 0) //Backup wird nur ausgeführt wenn Produktliste gefüllt
{
//------------------Excel-Backup------------------//
if (this.excel_checkbox.Checked)
{
this.backupprogress.Visible = true; //Zeige Ladebalken
this.selectionBox.Enabled = false; // Deaktiviere Bedienelemente
this.coorBox.Enabled = false;
//Neue Excel Mappe anlegen
Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
excelapp.ScreenUpdating = false; //Neuzeichnen deaktivieren
Microsoft.Office.Interop.Excel.Workbook map = excelapp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet page = map.Worksheets[1];
page.Name = "Backupdaten";
int index = 0;
for (int i = 0; i < products.Count; i++)
{
index = i * 33;
page.Cells[(index) + 1, 1].Value = productlist.Items[i].ToString(); // Produktbezeichner
SPSController.ReadDBEntry(i);
for (int j = 0; j < xylist_size; j++) // Koordianten
{
page.Cells[index + j + 2, 1].Value = SPSController.GetXVal(j);
page.Cells[index + j + 2, 2].Value = SPSController.GetYVal(j);
}
this.backupprogress.PerformStep(); //Step Progressbar
}
SPSController.ReadDBEntry(productlist.SelectedIndex); //Zurücksetzen auf ausgewählten Eintrag
filepath = GetFilepathWithDate();
map.SaveAs(filepath + ".xlsx");
map.Close();
excelapp.ScreenUpdating = true; //Neuzeichnen aktivieren
excelapp.Quit();
FileInfo exfi = new FileInfo(filepath + ".xlsx");
exfi.IsReadOnly = true; //Setze Schreibschutz für Datei
lastExcelBackupPath = filepath;
this.backupprogress.Value = 0;
SendToConsole("Excel-Backupdatei erstellt");
}
//------------------XML-Backup------------------//
this.backupprogress.Visible = true; //Zeige Ladebalken
this.selectionBox.Enabled = false; // Deaktiviere Bedienelemente
this.coorBox.Enabled = false;
XmlDocument xmlDoc = new XmlDocument();
XmlNode produktliste = xmlDoc.CreateElement("Produkte"); // Root-Node
xmlDoc.AppendChild(produktliste);
int k = 0;
foreach (string entry in products)
{
SPSController.ReadDBEntry(k);
XmlNode produkt = xmlDoc.CreateElement(entry);
produktliste.AppendChild(produkt);
for (int l = 0; l < xylist_size; l++) //X-Koordianten schreiben
{
XmlNode coor = xmlDoc.CreateElement("X" + l.ToString());
coor.InnerText = SPSController.GetXVal(l).ToString();
produkt.AppendChild(coor);
}
for (int l = 0; l < xylist_size; l++) //Y-Koordianten schreiben
{
XmlNode coor = xmlDoc.CreateElement("Y" + l.ToString());
coor.InnerText = SPSController.GetYVal(l).ToString();
produkt.AppendChild(coor);
}
k++;
this.backupprogress.PerformStep(); //Step Progressbar
}
filepath = GetFilepathWithDate(auto);
xmlDoc.Save(filepath + ".xml");
FileInfo xmlfi = new FileInfo(filepath + ".xml");
xmlfi.IsReadOnly = true; //Setze Schreibschutz für Datei
this.backupprogress.Visible = false; //Verstecke Ladebalken
this.backupprogress.Value = 0;
this.selectionBox.Enabled = true; // Aktiviere Bedienelemente
this.coorBox.Enabled = true;
SPSController.ReadDBEntry(productlist.SelectedIndex); //Zurücksetzen auf ausgewählten Eintrag
SendToConsole(auto ? "Automatisches XML-Backup erstellt\n" : "XML-Backup erstellt\n");
LastBackupDate(); //Aktuelisiert die Anzeige des letzten Backup Datums
}
else
{
SendToConsole("Backup kann nicht erstellt werden:\n Keine Einträge in Produktliste!\n");
}
}
/// <summary>
/// 通用导出Excle
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public ActionResult Excle(DataTable dt)
{
string path = "~/DownLoadTemplate/" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Millisecond + ".xls";
if (dt != null)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception ex)
{
throw ex;
}
if (xlApp != null)
{
try
{
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
xlSheet = xlBook.Worksheets[1];
xlSheet.Name = dt.TableName;
int rowIndex = 1;
int colIndex = 1;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
xlSheet.Cells[rowIndex, colIndex] = dt.Columns[i].ColumnName;
colIndex++;
}
//列名加粗显示
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true;
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10";
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 1;
for (int j = 0; j < colCount; j++)
{
xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
colIndex++;
}
rowIndex++;
}
xlSheet.Cells.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
path = Path.GetFullPath(path);
xlBook.SaveCopyAs(path);
xlBook.Close(false, null, null);
xlApp.Workbooks.Close();
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlBook);
xlBook = null;
}
catch (Exception ex)
{
return(Json(new ReturnResult
{
Data = "",
Message = ex.Message,
Result = 1
}));
}
finally
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
int generation = System.GC.GetGeneration(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
}
}
return(Json(new ReturnResult
{
Data = "",
Message = "导出成功",
Result = 0
}));
}
public void GenerarLayout(DoWorkEventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
string sAutoFitRange = string.Empty;
Microsoft.Office.Interop.Excel.Workbook _Workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet _Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_Workbook.ActiveSheet;
bgwProgreso.ReportProgress(0, string.Format("CREANDO DOCUMENTO"));
// Genera el layout bancario para Banorte
// Headers.
_Worksheet.Cells[1, 0 + 1] = "Oper";
_Worksheet.Cells[1, 1 + 1] = "Clave ID";
_Worksheet.Cells[1, 2 + 1] = "Cuenta Origen";
_Worksheet.Cells[1, 3 + 1] = bModo ? "Cuenta" : "Cuenta/CLABE destino";
_Worksheet.Cells[1, 4 + 1] = "Importe";
_Worksheet.Cells[1, 5 + 1] = "Referencia";
_Worksheet.Cells[1, 6 + 1] = "Descripción";
_Worksheet.Cells[1, 7 + 1] = "RFC Ordenante";
_Worksheet.Cells[1, 8 + 1] = "IVA";
_Worksheet.Cells[1, 9 + 1] = "Fecha aplicación";
_Worksheet.Cells[1, 10 + 1] = "Nombre beneficiario";
bgwProgreso.ReportProgress(0, string.Format("DANDO FORMATO"));
_Worksheet.Range["A2:A" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["B2:B" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["C2:C" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["D2:D" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["E2:E" + _DataTableMain.Rows.Count + 1].NumberFormat = "0.00";
_Worksheet.Range["F2:F" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["G2:G" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
_Worksheet.Range["H2:H" + _DataTableMain.Rows.Count + 1].NumberFormat = "@";
sAutoFitRange = "A:K";
for (int i = 0; i < _DataTableMain.Columns.Count; i++)
{
_Worksheet.Cells[1, i + 1].Interior.Color = ColorTranslator.ToOle(Color.FromArgb(102, 255, 51));
}
int ii = 0;
int iTotal = _DataTableMain.Rows.Count;
// Content.
for (int i = 0; i < _DataTableMain.Rows.Count; i++)
{
ii++;
Thread.Sleep(100);
int percents = (ii * 100) / iTotal;
bgwProgreso.ReportProgress(percents, string.Format("{0}/{1}", ii, iTotal));
for (int j = 0; j < _DataTableMain.Columns.Count; j++)
{
_Worksheet.Cells[i + 2, j + 1] = _DataTableMain.Rows[i][j].ToString();
}
}
_Worksheet.Columns[sAutoFitRange].AutoFit();
// Lots of options here. See the documentation.
_Workbook.SaveAs(sfdRuta.FileName);
_Workbook.Close();
app.Quit();
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
int idx = dbFile.LastIndexOf(@"\");
Microsoft.Win32.SaveFileDialog dlgSaveDiagram = new Microsoft.Win32.SaveFileDialog();
dlgSaveDiagram.Filter = "Excel xlsx |*.xlsx;";
dlgSaveDiagram.Title = "Export Report";
if (dlgSaveDiagram.ShowDialog() == true)
{
string filePath = dlgSaveDiagram.FileName;
string vsd = AppDomain.CurrentDomain.BaseDirectory.ToString() + "SimTech-PRV_DataSheet_Model.xlsx";
System.IO.File.Copy(vsd, filePath);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, Type.Missing, false, 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 xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet.Cells[5][15] = "test";
int count = 0;
int row1 = 14;
int note1 = 42;
int row2 = 74;
int note2 = 102;
int row3 = 134;
int note3 = 162;
for (int i = 1; i < mainTab.Items.Count; i++)
{
TabItem ti = (TabItem)mainTab.Items[i];
if (ti.Visibility == Visibility.Visible)
{
string num = ti.Name.Remove(0, 2);
UC_CaseStudy uc = (UC_CaseStudy)ti.FindName("uc" + num);
int col = count % 5;
if (count <= 4)
{
xlWorkSheet.Cells[5 + col * 2][row1] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row1 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row1 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else if (count >= 5 && count <= 9)
{
xlWorkSheet.Cells[5 + col * 2][row2] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row2 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row2 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else
{
xlWorkSheet.Cells[5 + col * 2][row3] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row3 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row3 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
count++;
}
}
if (count <= 5)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[3][121]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[14][121]];
r.Clear();
pic = xlWorkSheet.Shapes.Item(2) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
else if (count <= 10)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
xlWorkBook.Save();
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
private void output_Click(object sender, EventArgs e)
{
string path = outputpath.Text.Trim();
string tableName = outputtables.Text;
if (path == "")
{
MessageBox.Show("请选择导出地址！");
}
else if (tableName == null || tableName == "")
{
MessageBox.Show("请选择表名！");
}
else
{
//导出
string filename = path + "/" + tableName + ".xls";
string sql = "select * from " + tableName;
DataTable dt = GetDataTable(sql);
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
if (File.Exists(filename))
{
File.Delete(filename);
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
try
{
workbook.Saved = true;
workbook.SaveCopyAs(filename);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
finally {
workbook.Close(true);
excel.Quit();
GC.Collect();
}
DialogResult result = MessageBox.Show("数据导出成功！是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
System.Diagnostics.Process.Start(filename);
}
}
}
void CreateExcelDocument()
{
System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
xlWorkSheet.Name = (rm as ResourceManager).GetString("Menu_Contact");
Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1:C1");
formatRange.Font.Bold = true;
formatRange.WrapText = true;
string rang = "A1:" + "C" + (contList.Count + 1);
xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS";
xlWorkSheet.Range[rang].Font.Size = 16;
xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[1, 1] = LB_Name.Content;
xlWorkSheet.Cells[1, 2] = LB_Phone.Content;
xlWorkSheet.Cells[1, 3] = LB_Email.Content;
for (int i = 0; i < contList.Count; i++)
{
xlWorkSheet.Cells[2 + i, 1] = contList[i].Con_Name.Trim();
xlWorkSheet.Cells[2 + i, 2] = contList[i].Con_Phone.Trim();
xlWorkSheet.Cells[2 + i, 3] = contList[i].Con_Email.Trim();
}
xlWorkSheet.get_Range(rang).Columns.AutoFit();
xlApp.DisplayAlerts = false;
Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog();
saveFileDialog.FileName = (rm as ResourceManager).GetString("Menu_Contact");
saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter");
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle");
if (saveFileDialog.ShowDialog() == true)
{
System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName);
if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false)
{
xlWorkBook.SaveAs(saveFileDialog.FileName);
ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle);
Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false);
wmsg.Show();
}
}
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
catch (Exception ex)
{
System.Windows.Input.Mouse.OverrideCursor = null;
ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error);
Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false);
msb.Show();
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
System.Windows.Input.Mouse.OverrideCursor = null;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
saveFileDialog1.Filter = "Libro de Excel (*.xlsx)|*.xlsx | Libro de Excel 97-2003 (*.xls)|*.xls | Archivo csv (*.csv)|*.csv";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (saveFileDialog1.FilterIndex == 1)
{
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = ((Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet);
int i = 1;
int j = 1;
int jj = lvlcheckin.Columns.Count;
for (int rr = 0; rr < jj; rr++)
{
ws.Cells[i, j] = lvlcheckin.Columns[rr].Text;
j = j + 1;
}
i = 2;
j = 1;
foreach (ListViewItem lista in lvlcheckin.Items)
{
ws.Cells[i, j] = lista.Text.ToString();
foreach (ListViewItem.ListViewSubItem drv in lista.SubItems)
{
ws.Cells[i, j] = drv.Text.ToString();
j += 1;
}
j = 1;
i += 1;
}
wb.SaveAs(saveFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
wb.Close(false, Type.Missing, Type.Missing);
xla.Quit();
}
else if (saveFileDialog1.FilterIndex == 2)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (ColumnHeader ch in lvlcheckin.Columns)
{
sb.Append(ch.Text + ",");
}
sb.AppendLine();
foreach (ListViewItem lvi in lvlcheckin.Items)
{
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
if (lvs.Text.Trim() == string.Empty)
{
sb.Append(" ,");
}
else
{
sb.Append(lvs.Text + ",");
}
}
sb.AppendLine();
}
System.IO.StreamWriter sw = new System.IO.StreamWriter(saveFileDialog1.FileName);
sw.Write(sb.ToString());
sw.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public CreateDefaultExcel()
{
try
{
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
Microsoft.Office.Interop.Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet oSheet = oWB.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
oSheet.Cells[1, 1] = "Item code";
oSheet.Cells[1, 2] = "Item description";
oSheet.Cells[1, 3] = "Bar code";
oSheet.Cells[1, 4] = "Category";
oSheet.Cells[1, 5] = "Group";
oSheet.Cells[1, 6] = "Item type";
oSheet.Cells[1, 7] = "Supplier";
//oSheet.Cells[1, 9] = "Supplier code";
oSheet.Cells[1, 08] = "Quantity";
oSheet.Cells[1, 09] = "Access level";
oSheet.Cells[1, 10] = "MOQ";
oSheet.Name = "ExcelItemDetails";
//if we want to add For Application Root directry we can add
//Microsoft.Office.Interop.Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
// as Microsoft.Office.Interop.Excel.Worksheet;
//oSheet2.Name = "Sheet2";
//oSheet2.Cells[1, 1] = "Something completely different";
//string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
// + "\\Sample.xlsx";
string fileName = null;
SaveFileDialog savefile = new SaveFileDialog();
savefile.FileName = "Default.xlsx";
if (savefile.ShowDialog() == DialogResult.OK)
{
// Bind Access Level
var accesLevelList = new System.Collections.Generic.List <string>();
accesLevelList.Add("1");
accesLevelList.Add("2");
accesLevelList.Add("3");
var flatAccesLevelList = string.Join(",", accesLevelList.ToArray());
var oMissing = Type.Missing;
columnIndex = 09;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flatAccesLevelList);
// Bind Supplier
DataTable dtSupplier = objBal.BindSupplier();
var supplierlList = new System.Collections.Generic.List <string>();
if (dtSupplier.Rows.Count > 0)
{
for (int i = 0; i < dtSupplier.Rows.Count; i++)
{
supplierlList.Add(dtSupplier.Rows[i]["Company"].ToString());
}
}
var flaSupplierlList = string.Join(",", supplierlList.ToArray());
var oMissingsupplierlList = Type.Missing;
columnIndex = 07;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flaSupplierlList);
fileName = savefile.FileName;
oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
MessageBox.Show("Data saved in Excel format at location " + fileName, "Successfully Saved", MessageBoxButtons.OK, MessageBoxIcon.Question);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void btnBackUP_Click(object sender, EventArgs e)
{
if (this.cbxSelectKQuan.SelectedItem == null)
{
MessageBox.Show("请选择矿权！");
return;
}
if (this.cbxUpdateKQuan.SelectedItem == null || this.cbxKQu.SelectedItem == null || this.dateTimeInput1 == null ||
this.txtUpdateReason == null || this.txtOperator == null || this.txtManager == null || this.txtComment == null)
{
MessageBox.Show("请完善记录信息！");
return;
}
string[] TableName = { "JGAB301_核查矿区", "JGAB302_原上表矿区", "JGAB303_勘查工作区", "JGAB304_采矿权", "JGAB305_探矿权",
"JGAB306_矿体", "JGAB307_采空区", "JGAB308_核查块段", "JGAB309_核查块段储量", "JGAB310_原块段", "JGAB311_原块段储量",
"JGAB312_块段对照表", "JGAB313_资料目录", "JGAB314_附件目录", "JGAB315_专题图件", "JGAB316_专题图件图层",
"JGAB317_煤质特征", "JGAB318_储量利用", "JGAB319_大块段对照表", "JGAB320_合并原块段", "JGAB321_采矿权三率" };
string[] tableField = { "TZYSBH", "TYBH", "HCKQBH", "CKZBH", "CKQR", "CKQFW", "DZ", "KSBH", "KSMC", "FZJG", "YXQQ", "YXQZ", "XKCSS", "XKCSX", "KCZKZ", "ZKZMC", "ZYJSL", "ZYKSL", "BYJSL", "BYKSL", "DKSNL", "DJSNL", "SKSNL", "SJSNL", "NDKSL", "NDJSL", "KCFSM", "KCFS", "XKFSM", "XKFS", "RXKSL", "KQBH", "JJLXM", "JJLX", "CYRY", "NCZ", "JSSCCB", "KCPLX", "CXSBSL" };
// 文件保存路径及名称
// 创建Excel文档
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
string sheetName = "";
//删除自己生成的两个sheet
for (int i = 2; i < 4; i++)
{
sheetName = "Sheet" + i;
sheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Sheets.get_Item(sheetName);
sheet.Delete();
}
for (int i = 0; i < 1; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Name = "更新日志表";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 1] = "更新矿权";//也可以这样赋值
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 2] = "所属矿区";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 3] = "更新时间";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 4] = "更新原因";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 5] = "负责人";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 6] = "操作员";
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 7] = "备注";
//合并 单元格 设置表头
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").MergeCells);
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").MergeCells);
//得到 Range 范围 域对象
Microsoft.Office.Interop.Excel.Range range = ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G69");
//设置 该range内的 样式 颜色 边框
////设置Excel表格的 列宽
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C69").ColumnWidth = 30;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E69").ColumnWidth = 20;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F69").ColumnWidth = 30;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G69").ColumnWidth = 30;
//设置 域 Range 的颜色 从 A1到W1
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G1").Interior.ColorIndex = 15;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A2", "G2").Interior.ColorIndex = 15;
//设置某个域range被选中
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G3").Select();
//左右 设置 选中域内的 Excel单元格从C 到W 是活动的 前面的A B 为固定的
//但是 上下 方向 表头（这里表头合并两行 ）没有固定 选C3 到W3（表示从C的第三行开始 为 活动 的 上面两行为固定的）
ExcelApp.ActiveWindow.FreezePanes = true;
//设置 某个域range内 单元格里的字体颜色
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G2").Font.Color = -16744448;//（搜索Excel颜色对照表）
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G24").Font.Color = -16776961;
//文字 居中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Font.Size = 10;
range.Borders.LineStyle = 1;
//设置边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
range.WrapText = true;
//赋值 就
for (int j = 0; j < 1; j++)
{
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 1] = this.cbxSelectKQuan.SelectedItem.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 2] = this.cbxKQu.SelectedItem.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 3] = this.dateTimeInput1.Text.ToString();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 4] = this.txtUpdateReason.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 5] = this.txtManager.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 6] = this.txtOperator.Text;
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 7] = this.txtComment.Text;
}
}
ExcelApp.DisplayAlerts = true;
object missing = System.Reflection.Missing.Value;
// 文件保存
string excelPath = historyPath + "\\矿权-" + this.cbxSelectKQuan.SelectedItem.ToString() + "-" + DateTime.Today.Year.ToString() + "年" + DateTime.Today.Month.ToString() + "月.xls";
ExcelBook.SaveAs(excelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
ExcelBook.Close(Type.Missing, excelPath, Type.Missing);
ExcelApp.Quit();
string P_str_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + access_Path + ";Persist Security Info=False";
OleDbConnection oledbcon = new OleDbConnection(P_str_Con);//实例化OLEDB连接对象
//使用事务保持数据的一致性与完整性
string P_str_Sql_04 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB304_采矿权] from JGAB304_采矿权 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_21 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB321_采矿权三率] from JGAB321_采矿权三率 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_08 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB308_核查块段] from JGAB308_核查块段 where CKZBH = '"
+ this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句
string P_str_Sql_06 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB306_矿体] from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_09 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB309_核查块段储量] from JGAB309_核查块段储量 where (TYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_12 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB312_块段对照表] from JGAB312_块段对照表 where (HCTYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_10 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB310_原块段] from JGAB310_原块段 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_11 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB311_原块段储量] from JGAB311_原块段储量 where (KDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_18 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB318_储量利用] from JGAB318_储量利用 where (TYBH IN (select DISTINCT CLLYTYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_17 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB317_煤质特征] from JGAB317_煤质特征 where (TYBH IN (select DISTINCT MCBH from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))";
string P_str_Sql_19 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB319_大块段对照表] from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_20 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB320_合并原块段] from JGAB320_合并原块段 where (HBTYBH IN (select DISTINCT YTYBH from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))";
string P_str_Sql_01 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB301_核查矿区] from JGAB301_核查矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_02 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB302_原上表矿区] from JGAB302_原上表矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_03 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB303_勘查工作区] from JGAB303_勘查工作区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_05 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB305_探矿权] from JGAB305_探矿权 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_07 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB307_采空区] from JGAB307_采空区 where CKQBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'";
string P_str_Sql_13 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB313_资料目录] from JGAB313_资料目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_14 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB314_附件目录] from JGAB314_附件目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_15 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB315_专题图件] from JGAB315_专题图件 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string P_str_Sql_16 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB316_专题图件图层] from JGAB316_专题图件图层 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))";
string[] SQLList = { P_str_Sql_04, P_str_Sql_21, P_str_Sql_08, P_str_Sql_06, P_str_Sql_09, P_str_Sql_12, P_str_Sql_10, P_str_Sql_11
, P_str_Sql_18, P_str_Sql_17, P_str_Sql_19, P_str_Sql_20, P_str_Sql_01, P_str_Sql_02, P_str_Sql_03,P_str_Sql_05, P_str_Sql_07, P_str_Sql_13, P_str_Sql_14
, P_str_Sql_15, P_str_Sql_16 };
oledbcon.Open();//打开数据库连接
OleDbCommand oledbcom = new OleDbCommand();
oledbcom.Connection = oledbcon;
oledbcom.Transaction = oledbcon.BeginTransaction();//开始事务
try
{
for (int i = 0; i < SQLList.Length; i++)
{
string strsql = SQLList[i].ToString();
if (strsql.Trim().Length > 1)
{
oledbcom.CommandText = strsql;
oledbcom.ExecuteNonQuery();
}
//SetTextMessage(i * 100 / SQLList.Length);
}
oledbcom.Transaction.Commit();
MessageBox.Show("操作成功！");
}
catch (Exception ex)
{
MessageBox.Show("操作遇到问题，已撤销所做操作！");
oledbcom.Transaction.Rollback();//回滚数据，保证数据的完整性
}
finally
{
oledbcon.Close(); //关闭数据库连接
oledbcon.Dispose(); //释放资源
}
}
AutomateOneWorkbookIndirect
(
String nodeXLWorkbookFilePath,
String workbookSettings
)
{
Debug.Assert(!String.IsNullOrEmpty(nodeXLWorkbookFilePath));
Debug.Assert(!String.IsNullOrEmpty(workbookSettings));
// Ideally, the Excel API would be used here to open the workbook
// and run the AutomateOneWorkbook() method on it. Two things
// make that impossible:
//
// 1. When you open a workbook using
// Application.Workbooks.Open(), you get only a native Excel
// workbook, not an "extended" ThisWorkbook object.
//
// Although a GetVstoObject() extension method is available to
// convert a native Excel workbook to an extended workbook,
// that method doesn't work on a native workbook opened via
// the Excel API -- it always returns null.
//
// It might be possible to refactor AutomateOneWorkbook() to
// require only a native workbook. However, problem 2 would
// still make things impossible...
//
// 2. If this method is being run from a modal dialog, which it
// is (see AutomateTasksDialog), then code in the workbook
// that needs to be automated doesn't run until the modal
// dialog closes.
//
// The following code works around these problems.
Microsoft.Office.Interop.Excel.Application oExcelApplication =
null;
ExcelApplicationKiller oExcelApplicationKiller = null;
try
{
// Use a new Application object for each workbook. If the same
// Application object is reused, the memory used by each
// workbook is never released and the machine will eventually
// run out of memory.
oExcelApplication =
new Microsoft.Office.Interop.Excel.Application();
if (oExcelApplication == null)
{
throw new Exception("Excel couldn't be opened.");
}
// ExcelApplicationKiller requires that the application be
// visible.
oExcelApplication.Visible = true;
oExcelApplicationKiller = new ExcelApplicationKiller(
oExcelApplication);
// Store an "automate tasks on open" flag in the workbook,
// indicating that task automation should be run on it the next
// time it's opened. This can be done via the Excel API.
Microsoft.Office.Interop.Excel.Workbook oWorkbookToAutomate =
ExcelUtil.OpenWorkbook(nodeXLWorkbookFilePath,
oExcelApplication);
PerWorkbookSettings oPerWorkbookSettings =
new PerWorkbookSettings(oWorkbookToAutomate);
oPerWorkbookSettings.WorkbookSettings = workbookSettings;
oPerWorkbookSettings.AutomateTasksOnOpen = true;
oWorkbookToAutomate.Save();
oWorkbookToAutomate.Close(false, Missing.Value, Missing.Value);
oExcelApplication.Quit();
}
catch (Exception oException)
{
ErrorUtil.OnException(oException);
return;
}
finally
{
// Quitting the Excel application does not remove it from
// memory. Kill its process.
oExcelApplicationKiller.KillExcelApplication();
oExcelApplication = null;
oExcelApplicationKiller = null;
}
try
{
// Now open the workbook in another instance of Excel, which
// bypasses problem 2. Code in the workbook's Ribbon will
// detect the flag's presence, run task automation on it, close
// the workbook, and close the other instance of Excel.
OpenWorkbookToAutomate(nodeXLWorkbookFilePath, 60 * 60);
}
catch (Exception oException)
{
ErrorUtil.OnException(oException);
return;
}
}
static void Main(string[] args)
{
var optionsChrome = new ChromeOptions();
optionsChrome.AddArgument("--headless");
var driver = new ChromeDriver(optionsChrome);
driver.Navigate().GoToUrl("https://fulltime.thefa.com/ff/DivisionDetails?divisionid=7044651&leagueid=3956158&seasonid=659468196");
Thread.Sleep(1000);
var titleSection = driver.FindElementById("ff-division-table-obj");
var tableValues = titleSection.Text;
var footballLeagueData = new List <FootballLeagueRow>();
driver.FindElement(By.XPath("//*[@id=\"coachmark-modal-block\"]/div/div/div[2]/div/div[2]")).Click();
for (int i = 1; i <= 22; i++)
{
var dataRow = new FootballLeagueRow();
for (int j = 1; j <= 11; j++)
{
if (j == 10)
{
continue;
}
var xPathExpression = string.Format("//*[@id=\"ff-division-table-obj\"]/tbody/tr[{0}]/td[{1}]", i, j);
var dataItem = driver.FindElement(By.XPath(xPathExpression)).Text;
switch (j)
{
case 1:
dataRow.POS = dataItem;
break;
case 2:
dataRow.Team = dataItem;
break;
case 3:
dataRow.PLD = dataItem;
break;
case 4:
dataRow.W = dataItem;
break;
case 5:
dataRow.D = dataItem;
break;
case 6:
dataRow.L = dataItem;
break;
case 7:
dataRow.GF = dataItem;
break;
case 8:
dataRow.GA = dataItem;
break;
case 9:
dataRow.GD = dataItem;
break;
case 11:
dataRow.PTS = dataItem;
break;
}
}
footballLeagueData.Add(dataRow);
}
driver.Quit();
driver.Dispose();
string path = @"C:\Users\jackw\Documents\WebScraper\mybook.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 allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
//Add data to excel sheet
int rowCount = 2;
foreach (var item in footballLeagueData)
{
mWSheet1.Cells[rowCount, 1] = item.POS;
mWSheet1.Cells[rowCount, 2] = item.Team;
mWSheet1.Cells[rowCount, 3] = item.PLD;
mWSheet1.Cells[rowCount, 4] = item.W;
mWSheet1.Cells[rowCount, 5] = item.D;
mWSheet1.Cells[rowCount, 6] = item.L;
mWSheet1.Cells[rowCount, 7] = item.GF;
mWSheet1.Cells[rowCount, 8] = item.GA;
mWSheet1.Cells[rowCount, 9] = item.GD;
mWSheet1.Cells[rowCount, 10] = item.PTS;
rowCount++;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
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();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string filename = "";
try
{
this.error.Visible = false;
string str2 = "";
string str3 = "";
string month = "";
double num = 0.0;
int num2 = 0;
OleDbConnection connection = new OleDbConnection();
OleDbCommand selectCommand = new OleDbCommand();
OleDbDataAdapter adapter = new OleDbDataAdapter();
DataSet dataSet = new DataSet();
Microsoft.Office.Interop.Excel.Application o = new Microsoft.Office.Interop.Excel.Application();
object obj2 = Missing.Value;
string name = "";
if (this.xlsUpload.HasFile)
{
string extension = Path.GetExtension(this.xlsUpload.FileName.ToString());
string cmdText = null;
string connectionString = "";
if ((extension.Trim().ToLower() == ".xls") || (extension.Trim().ToLower() == ".xlsx"))
{
if (File.Exists(base.Server.MapPath("ExcelUpload/" + this.xlsUpload.FileName.ToString())))
{
this.error.Visible = true;
this.error.InnerText = "File already exist on the server.kindly upload another file";
}
else
{
this.xlsUpload.SaveAs(base.Server.MapPath("ExcelUpload/" + this.xlsUpload.FileName.ToString()));
filename = base.Server.MapPath("ExcelUpload/" + this.xlsUpload.FileName.ToString());
Microsoft.Office.Interop.Excel.Workbook workbook = o.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets)
{
name = worksheet.Name;
break;
}
workbook.Close(false, obj2, obj2);
while (Marshal.ReleaseComObject(workbook) > 0)
{
}
workbook = null;
o.Quit();
Marshal.ReleaseComObject(o);
o = null;
GC.Collect();
GC.WaitForPendingFinalizers();
if (extension.Trim().ToLower() == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (extension.Trim().ToLower() == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
else
{
File.Delete(filename);
return;
}
cmdText = "SELECT * FROM [" + name + "$]";
connection = new OleDbConnection(connectionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
selectCommand = new OleDbCommand(cmdText, connection);
adapter = new OleDbDataAdapter(selectCommand);
dataSet = new DataSet();
adapter.Fill(dataSet);
bool flag = false;
DataTable table = new DataTable();
DataColumn column = new DataColumn("AgrNo", Type.GetType("System.String"));
DataColumn column2 = new DataColumn("Name", Type.GetType("System.String"));
DataColumn column3 = new DataColumn("Amt", Type.GetType("System.Double"));
DataColumn column4 = new DataColumn("Month", Type.GetType("System.String"));
table.Columns.Add(column);
table.Columns.Add(column2);
table.Columns.Add(column3);
table.Columns.Add(column4);
foreach (DataRow row in dataSet.Tables[0].Rows)
{
str2 = "";
str3 = "";
num = 0.0;
month = "";
flag = false;
str2 = row[0].ToString();
str3 = row[1].ToString();
if (str2.ToUpper().Contains("END OF FILE"))
{
flag = true;
break;
}
if ((!string.IsNullOrEmpty(str3) && (str3.Length > 7)) && str3.Substring(1, 7).Contains("9999999"))
{
flag = true;
break;
}
if (!string.IsNullOrEmpty(row[2].ToString()))
{
double result = 0.0;
if (!double.TryParse(row[2].ToString().Trim(), out result))
{
this.error.Visible = true;
this.error.InnerHtml = "<button type='button' class='close' data-dismiss='alert'>×</button>Suspected wrong input file format. Amount must not be numeric";
connection.Close();
return;
}
num = double.Parse(row[2].ToString().Trim());
}
month = row[3].ToString().Trim();
if (this.getMonthNo(month) == 0)
{
this.error.Visible = true;
this.error.InnerHtml = "<button type='button' class='close' data-dismiss='alert'>×</button>The input file format is wrong. One of the month value is in wrong format.Check the input file and try again";
return;
}
num2 = this.getMonthNo(month);
if (((str2.Trim().Length < 1) || (month.Trim().Length < 1)) || (str3.Trim().Length < 1))
{
this.error.Visible = true;
this.error.InnerHtml = "<button type='button' class='close' data-dismiss='alert'>×</button>The input file format is wrong. One of the row is empty.Check the input file and try again";
return;
}
DataRow row2 = table.NewRow();
row2[column] = str2;
row2[column2] = str3;
row2[column3] = num;
row2[column4] = num2;
table.Rows.Add(row2);
}
if ((table != null) && (table.Rows.Count > 0))
{
bool flag3 = false;
foreach (DataRow row in table.Rows)
{
RestructureProjection recpro = new RestructureProjection
{
ObligorName = row[1].ToString(),
AgreementNo = row[0].ToString(),
DepartmentId = usr.DepartmentID,
AddedBy = base.User.Identity.Name,
DateAdded = new DateTime?(DateTime.Now),
Amount = new decimal?(decimal.Parse(row[2].ToString())),
BudgetYrID = new int?(budYr.ID),
Month = new int?(int.Parse(row[3].ToString())),
Status = 1,
// LoanType=ddlLoanType.SelectedValue
};
flag3 = CommonBLL.AddRestructure(recpro);
}
if (flag3)
{
this.BindGrid();
this.success.Visible = true;
this.success.InnerHtml = " <button type='button' class='close' data-dismiss='alert'>×</button> Record updated successfully!!.";
}
else
{
this.error.Visible = true;
this.error.InnerHtml = " <button type='button' class='close' data-dismiss='alert'>×</button>Record could Not updated. Kindly try again. If error persist contact Administrator!!.";
}
}
}
}
else
{
this.error.Visible = true;
this.error.InnerHtml = " <button type='button' class='close' data-dismiss='alert'>×</button> An error occured. File not valid";
}
}
else
{
this.error.Visible = true;
this.error.InnerHtml = " <button type='button' class='close' data-dismiss='alert'>×</button> An error occured. File not valid";
}
}
catch (Exception exception)
{
if (File.Exists(filename))
{
this.error.Visible = true;
}
this.error.InnerHtml = " <button type='button' class='close' data-dismiss='alert'>×</button> An error occured. Kindly try again. If error persist contact Administrator!!.";
Utility.WriteError("Error: " + exception.Message);
}
}
public void Print(object sender, EventArgs e)
{
if (((FrmMAIN)this.MdiParent).ActiveMdiChild == this)
{
if (dgvSales.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 = "SaveMaterialCost";
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 < dgvSales.ColumnCount; k++)
{
xlWorkSheet.Cells[1, k] = dgvSales.Columns[k].HeaderText.ToString();
}
for (i = 0; i < dgvSales.RowCount; i++)
{
for (j = 0; j < dgvSales.ColumnCount - 1; j++)
{
//if (j == 3)
// continue;
if (dgvSales[j, i].Value != null)
{
xlWorkSheet.Cells[i + 2, j + 1] = dgvSales[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);
}
}
}
}
}
static void Main(string[] args)
{
string door_number = "67097"; //params
string quote_number = "50887-16-1"; //"60870"; //same
string rev_number = quote_number + "- Rev " + quote_number.Substring(quote_number.Length - 1);
//door_number = args[0];
//quote_number = args[1];
string startFile = @"\\designsvr1\SOLIDWORKS\DWDevelopment\Specifications\" + quote_number + @"\documents\" + "DataOutput " + quote_number + "- Door Designer.DO";; //location
string newFile = @"\\designsvr1\apps\Door Master\" + door_number + ".DO";
string packingFile = @"\\designsvr1\SOLIDWORKS\DWDevelopment\Specifications\" + quote_number + @"\documents\Packing List " + rev_number + ".xlsx"; //should be the default file path for the session for everyone
string engineerFile = @"\\designsvr1\SOLIDWORKS\DWDevelopment\Specifications\" + quote_number + @"\documents\Engineers Notes " + rev_number + ".xlsx";
string newPackingLocation = @"\\designsvr1\apps\bridge_jobcard\" + door_number + @"\Packing List " + door_number + ".xlsx";
string newEngineerLocation = @"\\designsvr1\apps\bridge_jobcard\" + door_number + @"\Engineer Notes " + door_number + ".xlsx";
System.IO.Directory.CreateDirectory(@"\\designsvr1\apps\bridge_jobcard\" + door_number);
//string fileName = "DataOutput " + quote_number + "- Door Designer.DO";
//^^ we need to copy and move this file before editing
System.IO.File.Copy(startFile, newFile, true); //true = overwrite
string test = File.ReadAllText(newFile);
//repplace the the - with ""
quote_number = quote_number.Replace("-", "");
test = test.Replace(quote_number, door_number);
File.WriteAllText(newFile, test);
int line_number = 224; //this is ALWAYS the beginning
//vv will change
for (int i = 0; i < door_number.Length; i++)
{
string singleDigit = door_number.Substring(i, 1);
lineChanger(singleDigit, newFile, line_number);
line_number = line_number + 1;
}
//also edit the packing list
//at some point we are going to move this excel sheet to another directory too
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(packingFile);
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; // get the entire used range
if (File.Exists(newPackingLocation))
{
File.Delete(newPackingLocation);
}
xlWorksheet.Cells[5][7].Value2 = door_number.ToString();
xlWorksheet.SaveAs(newPackingLocation);
xlWorkbook.Close(true); //close the excel sheet
xlApp.Quit(); //close everything excel related so that theres no errors when the door program tries to connect
if (File.Exists(newEngineerLocation))
{
File.Delete(newEngineerLocation);
}
File.Copy(engineerFile, newEngineerLocation, true); // also move this one over with a new name
//check if there is a entry in dbo.door_program
string sql = "select door_id FROM dbo.door_program WHERE door_id = " + door_number;
using (SqlConnection conn = new SqlConnection(CONNECT.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
var getdata = cmd.ExecuteScalar();
if (getdata == null)
{
//This door does not exist in the program table so we need to insert it
sql = "INSERT INTO dbo.door_program (door_id,programed_by_id,program_note,program_date) VALUES(" + door_number + ",314,'Programmed by bridge system',getdate())";
}
else
{
//update the door as programmed by 314
sql = "UPDATE dbo.door_program set programed_by_id = 314,program_note = 'Programmed by bridge system',program_date = getdate() WHERE door_id = " + door_number;
}
}
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
Console.WriteLine(sql);
// Console.ReadLine();
// Console.ReadLine(); //remove this otherwise when firing from the commandline it will hang a little
}
public void WriteExcell(DataSet ds, string Requisition, string FileName, string strSavePath, string FilePath)
{
//Pick up the RFQ format file
// string strPath= Server.MapPath(".") + "\Technical\ExcelFile\RFQ_FormatFile.xls";
// string path = System.AppDomain.CurrentDomain.BaseDirectory + @"RFQ_FormatFile.xls";
string path = FilePath + @"/RFQ_FormatFile.xls";
CheckExcellProcesses();
ExlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
ExlWrkBook = ExlApp.Workbooks.Open(path, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[1, 3] = ds.Tables[2].Rows[0]["Vessel_name"].ToString();
ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0]["QUOTATION_CODE"].ToString();
ExlWrkSheet.Cells[3, 3] = ds.Tables[0].Rows[0]["Quotation_Due_Date"].ToString();
ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[11, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
ExlWrkSheet.Cells[6, 12] = ds.Tables[0].Rows[0]["BUYER_COMMENTS"].ToString();
ExlWrkSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[1, 13] = ds.Tables[0].Rows[0]["Vessel_code"].ToString();
ExlWrkSheet.Cells[2, 13] = ds.Tables[0].Rows[0]["DOCUMENT_CODE"].ToString();
ExlWrkSheet.Cells[3, 13] = ds.Tables[0].Rows[0]["ITEM_SYSTEM_CODE"].ToString();
ExlWrkSheet.Cells[5, 13] = DateTime.Now.ToString("yyyy/MM/dd");
ExlWrkSheet.Cells[6, 13] = ds.Tables[0].Rows[0]["Quotation_CODE"].ToString();
ExlWrkSheet.Cells[7, 13] = ds.Tables[0].Rows[0]["QUOTATION_SUPPLIER"].ToString();
ExlWrkSheet.Cells[12, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
if (ds.Tables[4].Rows.Count > 0)
{
ExlWrkSheet.Cells[10, 7] = ds.Tables[4].Rows[0]["MechInfo"].ToString();
ExlWrkSheet.Cells[11, 7] = ds.Tables[4].Rows[0]["Model_Type"].ToString();
ExlWrkSheet.Cells[12, 7] = ds.Tables[4].Rows[0]["MakerName"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerAddress"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCity"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerEmail"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCONTACT"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerPhone"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerFax"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerTELEX"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["System_Serial_Number"].ToString();
}
Microsoft.Office.Interop.Excel.Range xlsRange;
int i = 15;
foreach (DataRow dr in ds.Tables[1].Rows)
{
//S.nO.
ExlWrkSheet.Cells[i, 1] = dr["ID"].ToString();
//DRAWING NO.
ExlWrkSheet.Cells[i, 2] = dr["Drawing_Number"].ToString();
//PART NO
ExlWrkSheet.Cells[i, 3] = dr["Part_Number"].ToString();
//Item Ref Code
ExlWrkSheet.Cells[i, 4] = dr["ITEM_REF_CODE"].ToString();
//Item
ExlWrkSheet.Cells[i, 5] = dr["Short_Description"].ToString();
ExlWrkSheet.Cells[i + 1, 5] = dr["Long_Description"].ToString();
ExlWrkSheet.Cells[i + 2, 5] = dr["ITEM_COMMENT"].ToString();
ExlWrkSheet.Cells[i, 6] = dr["Unit_and_Packings"].ToString();
ExlWrkSheet.Cells[i, 7] = dr["REQUESTED_QTY"].ToString();
i = i + 3;
}
ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 15).ToString(), "N1639").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
ExlWrkSheet.Cells[ds.Tables[1].Rows.Count * 3 + 15, 1] = ds.Tables[3].Rows[0]["LegalTerm"].ToString();
//ExlWrkSheet.get_Range("F15", "H" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("J15", "J" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("I7", "I9").Locked = false;
//ExlWrkSheet.get_Range("I10", "K11").Locked = false;
ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00";
//ExlWrkSheet.get_Range("I5", "I6").Locked = false;
//ExlWrkSheet.get_Range("L1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.get_Range("M1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing);
ExlWrkBook.SaveAs(strSavePath + FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
string s = Server.MapPath("~");
string destFile = Server.MapPath("Uploads/Purchase") + "\\" + FileName;;
File.Copy(strSavePath + FileName, destFile, true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExlWrkBook.Close(null, null, null);
//ExlApp.Workbooks.Close();
ExlApp.Quit();
KillExcel();
}
}
public bool DataSetToExcel(DataSet ds, string FilePath)
{
//建立Excel对象
//progressBar.Value = 0;
//progressBar.Maximum = ds.Tables.Count;
foreach (DataTable dt in ds.Tables)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Rows.RowHeight = 20;
worksheet.Columns.ColumnWidth = 20;
worksheet.Name = dt.TableName;
excel.Visible = false;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
int rowNumber = dt.Rows.Count;//不包括字段名
int columnNumber = dt.Columns.Count;
int colIndex = 0;
//生成字段名称
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dt.Rows[r][c];
}
//Application.DoEvents();
}
// 写入Excel
range = excel.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
range.NumberFormat = "0";//设置单元格为文本格式
range.Value2 = objData;
string path = string.Format(FilePath + @"\{0}.xlsx", dt.TableName);
try
{
FileInfo fileInfo = new FileInfo(path);
if (!fileInfo.Exists)
{
workbook.Saved = true;
workbook.SaveAs(path);
excel.UserControl = false;
}
else
{
workbook.Saved = true;
workbook.SaveCopyAs(path);//保存
excel.UserControl = false;
}
}
catch (Exception ex)
{
//Common.RecordError("ToExcelSave--" + ex.Message);
}
finally
{
//, Missing.Value, Missing.Value
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges);
excel.Quit();
}
//progressBar.Value += 1;
}
catch (Exception ex)
{
//Common.RecordError("DataSetToExcel------" + ex.Message);
}
}
return(true);
}
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;
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-MX");
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));
}
int columns_count = excelRange.Columns.Count;
int rows_count = excelRange.Rows.Count;
for (rowCnt = 2; rowCnt <= rows_count; rowCnt++)
{
object[] strData = new object[columns_count];
for (int clm = 0; clm < columns_count; clm++)
{
strData[clm] = ((Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[rowCnt, clm + 1]).Value2;
}
dt.Rows.Add(strData);
}
gvData.ItemsSource = dt.DefaultView;
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
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
protected void DataTableToExcel2(System.Data.DataTable tbl, string fileName, bool flag)
{
if (tbl == null)
{
return;
}
int rowNum = tbl.Rows.Count;
int columnNum = tbl.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook newBook = excelApp.Workbooks.Add();
//newBook.SaveAs(fileName);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn col in tbl.Columns)
{
columnIndex++;
excelApp.Cells[rowIndex, columnIndex] = col.Caption;
}
//将DataTable中的数据导入Excel中
Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[1, 3], excelApp.Cells[rowNum + 1, columnIndex]);
r.NumberFormat = "@";
r.NumberFormatLocal = "@";
for (int i = 0; i < rowNum; i++)
{
rowIndex++;//数据从第二行开始
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//if (columnIndex == 3||columnIndex == 18)
//{
// Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[rowIndex, columnIndex], excelApp.Cells[rowIndex, columnIndex]);
// r.NumberFormat = "@";
// r.NumberFormatLocal = "@";
//}
excelApp.Cells[rowIndex, columnIndex] = tbl.Rows[i][j].ToString();
}
}
excelApp.Cells.Columns.AutoFit();
newBook.SaveCopyAs(fileName);
newBook.Close();
excelApp.Workbooks.Close();
excelApp.Quit();
//Kill打开的Excel进程
//Process[] excelApps;
//excelApps = Process.GetProcessesByName("EXCEL");
//foreach (Process p in excelApps)
//{
// p.Kill();
//}//End
if (flag)
{
AddAtt(fileName); //加入附件中
}
}
void CreateExcelDocument()
{
System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
try
{
xlWorkSheet.Name = (rm as ResourceManager).GetString("LB_Expenses");
xlWorkSheet.Cells[1, 1] = exitems[CB_Offer.SelectedIndex].Venue.Trim() + " - " + exitems[CB_Offer.SelectedIndex].Address.Trim();
Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1", "D1");
formatRange.Font.Bold = true;
formatRange.WrapText = true;
xlWorkSheet.Range["a1", "D1"].Merge();
string rang = "A1:" + "D" + (expList.Count + 3);
xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS";
xlWorkSheet.Range[rang].Font.Size = 16;
xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[2, 1] = LB_Expense.Content;
xlWorkSheet.Cells[2, 2] = LB_Cost.Content;
xlWorkSheet.Cells[2, 3] = LB_Count.Content;
xlWorkSheet.Cells[2, 4] = (rm as ResourceManager).GetString("LB_Amount");
int sum = 0;
for (int i = 0; i < expList.Count; i++)
{
xlWorkSheet.Cells[3 + i, 1] = expList[i].ExpenseName.Trim();;
xlWorkSheet.Cells[3 + i, 2] = f.StringCurrencyFormat(expList[i].Expense.ToString());
xlWorkSheet.Cells[3 + i, 3] = f.StringCurrencyFormat(expList[i].Count.ToString());
xlWorkSheet.Cells[3 + i, 4] = f.StringCurrencyFormat((expList[i].Expense * expList[i].Count).ToString());
xlWorkSheet.Cells[3 + i, 2].NumberFormat = "0";
xlWorkSheet.Cells[3 + i, 3].NumberFormat = "0";
xlWorkSheet.Cells[3 + i, 4].NumberFormat = "0";
sum += expList[i].Expense * expList[i].Count;
}
xlWorkSheet.Range["A" + (expList.Count + 3), "C" + (expList.Count + 3)].Merge();
xlWorkSheet.Cells[expList.Count + 3, 1] = (rm as ResourceManager).GetString("LB_Amount");
xlWorkSheet.Cells[expList.Count + 3, 4] = f.StringCurrencyFormat(sum.ToString());
xlWorkSheet.get_Range(rang).Columns.AutoFit();
rang = "A" + (expList.Count + 3) + ":" + "D" + (expList.Count + 3);
formatRange = xlWorkSheet.get_Range(rang);
formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xlApp.DisplayAlerts = false;
Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog();
int venlenght = exitems[CB_Offer.SelectedIndex].Venue.Length;
int addlenght = exitems[CB_Offer.SelectedIndex].Address.Length;
saveFileDialog.FileName = (rm as ResourceManager).GetString("LB_Expenses") + "_" + exitems[CB_Offer.SelectedIndex].Venue.Substring(0, venlenght < 15 ? venlenght:15) + "_" + exitems[CB_Offer.SelectedIndex].Address.Substring(0, addlenght < 15 ? addlenght:15);
saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter");
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle");
if (saveFileDialog.ShowDialog() == true)
{
System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName);
if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false)
{
xlWorkBook.SaveAs(saveFileDialog.FileName);
ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle);
Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false);
wmsg.Show();
}
}
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
catch (Exception ex)
{
System.Windows.Input.Mouse.OverrideCursor = null;
ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error);
Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false);
msb.Show();
xlWorkBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}
System.Windows.Input.Mouse.OverrideCursor = null;
}
public static void generateExcel()
{
packages = DBConnector.getInstance().getPackages();
buckets = DBConnector.getInstance().getBuckets();
generatePackageList();
checkProcess();
generateBucketList();
/******************** create a workbook *************************/
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
excelworkBook = excel.Workbooks.Add(Type.Missing);
/********************* create new sheet (Activity List) ***************************/
excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheetAll.Name = "Activity List";
int row = 1;
int tb1_start_x = row;
int tb1_start_y = 1;
excelSheetAll.Cells[row, 1] = "Process Name";
excelSheetAll.Cells[row, 2] = "Duration";
excelSheetAll.Cells[row, 3] = "Main Window Title";
row++;
foreach (KeyValuePair<string, Activity> pair in activityList)
{
excelSheetAll.Cells[row, 1] = pair.Value.processName;
excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g");
excelSheetAll.Cells[row, 3] = pair.Key;
row++;
}
int tb1_end_x = row - 1;
int tb1_end_y = 3;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
/*************************** create new sheet (Packaged Activity List) ****************************/
excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetPackaged.Name = "Packaged Activity List";
row = 1;
int tb2_start_x = row;
int tb2_start_y = 1;
excelSheetPackaged.Cells[row, 1] = "Package Name";
excelSheetPackaged.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in packagedList)
{
excelSheetPackaged.Cells[row, 1] = pair.Key;
excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb2_end_x = row - 1;
int tb2_end_y = 2;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Packaged Activity List";
chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/************************* create new sheet (Bucketed Activity List) ******************************/
excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add();
excelSheetBucketed.Name = "Bucketed Activity List";
row = 1;
int tb3_start_x = row;
int tb3_start_y = 1;
excelSheetBucketed.Cells[row, 1] = "Bucket Name";
excelSheetBucketed.Cells[row, 2] = "Duration";
row++;
foreach (KeyValuePair<string, TimeSpan> pair in bucketedList)
{
excelSheetBucketed.Cells[row, 1] = pair.Key;
excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g");
row++;
}
int tb3_end_x = row - 1;
int tb3_end_y = 2;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]];
excelCellrange.NumberFormat = "hh:mm:ss.000";
excelCellrange.EntireColumn.AutoFit();
border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing);
chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300);
chart = chartObject.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "Buckted Activity List";
chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x);
chart.SetSourceData(chartRange, System.Reflection.Missing.Value);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
/*************** save excel *******************/
//UserPrincipal.Current.DisplayName
String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//excelworkBook.SaveAs(filePath);
excelworkBook.Close();
excel.Quit();
Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------");
Console.WriteLine("Export to Excel");
Thread.Sleep(1000);
System.Diagnostics.Process.Start(filePath);
}
public static DataTable GetDataFromExcelByCom(bool hasTitle = false)
{
OpenFileDialog openFile = new OpenFileDialog();
//openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
var excelFilePath = openFile.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
DataTable dt = new DataTable();
try
{
WaitFormService.Show();
if (app == null)
{
return(null);
}
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null)
{
return(null);
}
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt))
{
name = txt;
}
}
while (dt.Columns.Contains(name))
{
name = name + "_1"; //重复行名称会报错。
}
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
WaitFormService.SetText("正在录入第" + iRow.ToString() + "条的数据/共" + iRowCount.ToString() + "条记录！");
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
WaitFormService.Close();
return(dt);
}
catch { return(null); }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
private List <MessageMediaStructure> readExcel(string filePath)
{
List <MessageMediaStructure> ListOfexcelRows = new List <MessageMediaStructure>();
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
//xlWorkBook = xlApp.Workbooks.Open(Environment.CurrentDirectory + "\\" + filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkBook = xlApp.Workbooks.Open(filePath);//, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
catch
{
MessageBox.Show("فایل ورودی وجود ندارد", "خطا");
return(ListOfexcelRows);
}
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in xlWorkBook.Worksheets)
{
int i = 1;
foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.UsedRange.Rows)
{
MessageMediaStructure ex = new MessageMediaStructure();
try
{
//ex.lable = int.Parse(worksheet.Cells[row.Row, 2].Value.ToString());
ex.tagged = int.Parse(worksheet.Cells[row.Row, 2].Value.ToString());
}
catch
{
//NuOfEmptyTags++;
//continue;
}
try
{
//ex.text = worksheet.Cells[row.Row, 1].Value.ToString().Trim();
ex.message = worksheet.Cells[row.Row, 1].Value.ToString().Trim();
}
catch
{
//NuOfWrongRows++;
}
if (ex.message != null)
{
ex.Id = i;
ListOfexcelRows.Add(ex);
i++;
}
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
xlWorkBook.Close();
xlApp.Quit();
return(ListOfexcelRows);
}
private void SaveTimesheet()
{
DateTime sunday = DateTime.Now.AddDays(7 - (int)DateTime.Now.DayOfWeek - (DateTime.Now.DayOfWeek < DayOfWeek.Tuesday ? 7 : 0));
string xlFilename = TimesheetsDir + "\\Time Entry " + Name[0] + Name.Substring(Name.LastIndexOf(' ') + 1) + "_" + sunday.Year + sunday.Month.ToString("D2") + sunday.Day.ToString("D2") + ".xlsx";
try
{
File.WriteAllBytes(xlFilename, Properties.Resources.Template);
}
catch
{
return;
}
//excel COM object data
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(xlFilename, ReadOnly: false, Editable: true);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int currRow = 6;
int projCol = 5;
int monCol = 10;
int tueCol = 11;
int wedCol = 12;
int thuCol = 13;
int friCol = 14;
try
{
xlWorksheet.Cells[2, 16].Value = sunday.Date.ToShortDateString();
xlWorksheet.Cells[4, 5].Value = Name;
xlWorksheet.Cells[4, 10].Value = ID;
foreach (ProjectTimeDataViewModel p in CollectionView)
{
if (p.TotalTime == 0)
{
continue;
}
xlWorksheet.Cells[currRow, projCol].Value = p.ChargeNumber;
xlWorksheet.Cells[currRow, monCol].Value = p.MondayTime;
xlWorksheet.Cells[currRow, tueCol].Value = p.TuesdayTime;
xlWorksheet.Cells[currRow, wedCol].Value = p.WednesdayTime;
xlWorksheet.Cells[currRow, thuCol].Value = p.ThursdayTime;
xlWorksheet.Cells[currRow, friCol].Value = p.FridayTime;
currRow++;
}
}
catch { }
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Save();
xlWorkbook.Close(false);
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
private void buttonX_ok_Click(object sender, EventArgs e)
{
//add all point
List <IPoint> pointList = new List <IPoint>();
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
myExcel.Application.Workbooks.Open(textBoxX1.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //this.txtFile.Text为Excel文件的全路径
Microsoft.Office.Interop.Excel.Workbook myBook = myExcel.Workbooks[1];
//获取第一个Sheet
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)myBook.Sheets[1];
string sheetName = sheet.Name; //Sheet名
myBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
//read excel file to creat Field
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + this.textBoxX1.Text + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strCon);
string sql1 = string.Format("select * from [{0}$]", sheetName);
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql1, strCon);
DataSet ds = new DataSet();
myCommand.Fill(ds);
conn.Close();
int xIndex = 0;
int yIndex = 0;
int zIndex = 0;
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (ds.Tables[0].Columns[i].ColumnName == "X_经度")
{
xIndex = i;
}
if (ds.Tables[0].Columns[i].ColumnName == "Y_纬度")
{
yIndex = i;
}
if (ds.Tables[0].Columns[i].ColumnName.Contains("Z_高程"))
{
zIndex = i;
}
}
ISpatialReference pSpaReference = new UnknownCoordinateSystemClass();
pSpaReference.SetDomain(-8000000, 8000000, -800000, 8000000);
IFeatureClass pFt = CreateShapeFile(ds, this.textBoxX2.Text, pSpaReference);
if (pFt == null)
{
return;
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//根据XY坐标添加点,edit attribute
IsNumberic isNum = new IsNumberic();
ESRI.ArcGIS.Geometry.IPoint pPoint = new ESRI.ArcGIS.Geometry.PointClass();
if (ds.Tables[0].Rows[i][xIndex].ToString() == "" || ds.Tables[0].Rows[i][xIndex].ToString() == " ")
{
break;
}
if (isNum.IsNumber(ds.Tables[0].Rows[i][xIndex].ToString()) && isNum.IsNumber(ds.Tables[0].Rows[i][yIndex].ToString()))
{
pPoint.X = System.Convert.ToSingle(ds.Tables[0].Rows[i][xIndex].ToString());
pPoint.Y = System.Convert.ToSingle(ds.Tables[0].Rows[i][yIndex].ToString());
pPoint.Z = System.Convert.ToSingle(ds.Tables[0].Rows[i][zIndex].ToString());
IFeature pFeature = pFt.CreateFeature();
pFeature.Shape = pPoint;
pFeature.Store();
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (ds.Tables[0].Columns[j].ColumnName.Contains("里程"))
{
continue;
}
//pFeature.set_Value(pFeature.Fields.FindField(ds.Tables[0].Columns[j].ColumnName), ds.Tables[0].Rows[i][j]);
pFeature.set_Value(j + 2, ds.Tables[0].Rows[i][j].ToString());
}
pFeature.Store();
pointList.Add(pPoint);
}
else
{
MessageBox.Show("the" + i + "rows x and y value is unvalid!");
}
}
}
ClsGDBDataCommon processDataCommon = new ClsGDBDataCommon();
string strInputPath = System.IO.Path.GetDirectoryName(textBoxX2.Text);
string strInputName = System.IO.Path.GetFileName(textBoxX2.Text);
IFeatureLayer pFeatureLayer = new FeatureLayerClass();
pFeatureLayer.FeatureClass = pFt;
pFeatureLayer.Name = System.IO.Path.GetFileNameWithoutExtension(System.IO.Path.GetFileNameWithoutExtension(strInputName));
//create line shape file
IPointCollection PointCollection = ReadPoint(pFeatureLayer);
string lineName = strInputPath + "\\" + System.IO.Path.GetFileNameWithoutExtension(strInputName) + "_line.shp";
CreateLineShpFile(lineName, pSpaReference);
//将所有的点连接成线
List <IPolyline> Polyline = CreatePolyline(PointCollection);
List <double> lineLengthList = new List <double>();
//将连接成的线添加到线图层中
string pLineFile = lineName;
string pFilePath = System.IO.Path.GetDirectoryName(pLineFile);
string pFileName = System.IO.Path.GetFileName(pLineFile);
//打开工作空间
IWorkspaceFactory pWSF = new ShapefileWorkspaceFactoryClass();
IFeatureWorkspace pWS = (IFeatureWorkspace)pWSF.OpenFromFile(pFilePath, 0);
//写入实体对象
IFeatureLayer plineLayer = new FeatureLayerClass();
plineLayer.FeatureClass = pWS.OpenFeatureClass(pFileName);
AddFeature(plineLayer, Polyline, pointList, lineLengthList);
plineLayer.Name = pFeatureLayer.Name + "_line";
m_pMapCtl.AddLayer(plineLayer as ILayer, 0);
m_pMapCtl.AddLayer(pFeatureLayer as ILayer, 0);
m_pMapCtl.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeography, null, null);
ImpSymbolFromFile(textBoxX3.Text, pFeatureLayer, plineLayer);
this.Close();
}
private void CreaExcel(DataSet ds)
{
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oLibros = default(Microsoft.Office.Interop.Excel.Workbooks);
Microsoft.Office.Interop.Excel.Workbook oLibro = default(Microsoft.Office.Interop.Excel.Workbook);
Microsoft.Office.Interop.Excel.Sheets oHojas = default(Microsoft.Office.Interop.Excel.Sheets);
Microsoft.Office.Interop.Excel.Worksheet oHoja = default(Microsoft.Office.Interop.Excel.Worksheet);
Microsoft.Office.Interop.Excel.Range oCeldas = default(Microsoft.Office.Interop.Excel.Range);
try
{
string sFile = null;
string sTemplate = null;
// Usamos una plantilla para crear el nuevo excel
sFile = Server.MapPath("PDV_Planning") + "\\" + "Datos_Rutas.xls";
sTemplate = Server.MapPath("PDV_Planning") + "\\" + "Datos_Panel_ptoVenta1.xls";
oExcel.Visible = false;
oExcel.DisplayAlerts = false;
// Abrimos un nuevo libro
oLibros = oExcel.Workbooks;
oLibros.Open(sTemplate);
oLibro = oLibros.Item[1];
oHojas = oLibro.Worksheets;
for (int i = 0; i < ds.Tables.Count; i++)
{
oHoja = (Microsoft.Office.Interop.Excel.Worksheet)oHojas.Item[i + 1];
oHoja.Name = "Hoja" + (i + 1);
oCeldas = oHoja.Cells;
oHoja.Range["B2"].Interior.Color = 0;
oHoja.Range["B2"].Font.Color = 16777215;
oHoja.Range["A2"].Interior.Color = 0;
oHoja.Range["A2"].Font.Color = 16777215;
oHoja.Range["B2"].Font.Bold = true;
oHoja.Range["A2"].Font.Bold = true;
oHoja.Range["A2", "B" + (ds.Tables[i].Rows.Count + 2).ToString()].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDash;
oHoja.Range["A2", "B" + (ds.Tables[i].Rows.Count + 2).ToString()].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline;
VuelcaDatos(ds.Tables[i], oCeldas);
}
oHoja.SaveAs(sFile);
oLibro.Close();
// Eliminamos lo que hemos creado
oExcel.Quit();
oExcel = null;
oLibros = null;
oLibro = null;
oHojas = null;
oHoja = null;
oCeldas = null;
System.GC.Collect();
}
catch
{
oLibro.Close();
oExcel.Quit();
Pmensaje.CssClass = "MensajesSupervisor";
lblencabezado.Text = "Sr. Usuario";
lblmensajegeneral.Text = "Es indispensable que cierre sesión he inicie nuevamente. su sesión expiró.";
Mensajes_Usuario();
}
}