public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath)
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Your file is saved" + SaveFilePath);
}
/// <exception cref="FileNotFoundException"><c>FileNotFoundException</c>.</exception>
/// <exception cref="Exception"><c>Exception</c>.</exception>
public IList<IDataRecord> GetDataFromExcel(string filePath)
{
try
{
Log.Info("Начало импорта");
if (!File.Exists(filePath))
throw new FileNotFoundException(string.Format("Файл не найден. [{0}]", filePath));
Excelapp = new Application { Visible = false };
Workbook = Excelapp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
ReadDoc();
}
catch( Exception e)
{
Log.Error("Ошибка импорта", e);
throw;
}
finally
{
Workbook.Close();
Excelapp.Quit();
Excelapp = null;
}
return DataRecords;
}
public void Compare(ExcelWork firstObj, int firstRowIndex, ExcelWork secondObj, int secondRowIndex)
{
try
{
resultBook = excelApp.Workbooks.Open(SavePath);
resultSheet = resultBook.Worksheets[1];
WorkBookManager(firstObj, firstRowIndex);
WorkBookManager(secondObj, secondRowIndex);
startColumnIndex++;
resultBook.Save();
}
catch (Exception ex)
{
/// Возврат Error Message во View
///
throw new Exception(ex.Message);
}
finally
{
resultBook?.Close();
}
}
public bool Copy(string where, string from, int indexSheet = 1)
{
Excel.Workbook paternBook = null;
try
{
resultBook = excelApp.Workbooks.Add();
resultBook.SaveAs(SavePath);
paternBook = excelApp.Workbooks.Open(where);
(paternBook.Worksheets[1] as Excel.Worksheet).Copy(Before: resultBook.Worksheets[indexSheet]);
resultBook.Save();
return(true);
}
catch (Exception)
{
/// Возврат Error Message во View
///
return(false);
}
finally
{
resultBook?.Close();
paternBook?.Close();
}
}
public (Maybe <Schedule> item, IEnumerable <Message> messages) GetSchedule(string name)
{
var(timetable, timetableMessages) = GetTimetable(name);
if (timetableMessages.HasStoppingErrors())
{
return(Maybe <Schedule> .None(string.Format(CultureInfo.CurrentCulture, Resources.Strings.CannotReadScheduleDueToErrorsInTimetable)), timetableMessages);
}
var messages = new List <Message>(timetableMessages);
var result = new Schedule(name, timetable.Value);
var app = new Excel.Application {
Visible = false
};
Excel.Workbook book = null;
try
{
book = app.Workbooks.Open(GetFullFilename(name));
messages.AddRange(GetSchedules(result, book));
}
finally
{
book?.Close(false, GetFullFilename(name));
app.Quit();
}
return(Maybe <Schedule> .NoneIfError(result, messages.HasStoppingErrors()), messages);
}
public void ReadProcesses()
{
count = 0;
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
//Читаем данные по проектам
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes
List<string> row = new List<string>();
int n = 6;
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
row = new List<string>();
for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j
Mas.Add(row); //строка добавляется в массив
count++;
}
//test = WorkSheetExcel.Cells[2, 1];
WorkBookExcel.Close(false, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
}
public getData readData()
{
getData.dateT.Clear();
getData.prj.Clear();
getData.descr.Clear();
getData.taskTime.Clear();
notes.Clear();
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
//excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1
// WorkSheetExcel.Cells[i, 1].Text.ToString() != ""
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString());
getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString());
getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString());
getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString());
}
WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя
ExcelApp.Quit();
GC.Collect();
getData.dateT.Reverse();
getData.prj.Reverse();
getData.descr.Reverse();
getData.taskTime.Reverse();
return getData;
}
public (Maybe <Timetable> item, IEnumerable <Message> messages) GetTimetable(string name)
{
var(layout, layoutMessages) = GetLayout(name);
if (layoutMessages.HasStoppingErrors())
{
return(Maybe <Timetable> .None(string.Format(CultureInfo.CurrentCulture, Resources.Strings.CannotReadTimetableDueToErrorsInLayout)), layoutMessages);
}
var result = new Timetable(name, layout.Value);
var app = Excel;
var messages = new List <Message>(layoutMessages);
Excel.Workbook book = null;
try
{
book = app.Workbooks.Open(GetFullFilename(name));
messages.AddRange(GetTrains(result, book));
}
catch (Exception ex)
{
messages.Add(Message.System(ex.Message));
}
finally
{
book?.Close(false, GetFullFilename(name));
}
return(Maybe <Timetable> .NoneIfError(result, messages.HasStoppingErrors()), messages);
}
public static void ConvertExcelDocumentToPdf(string srcFile, string dstFile)
{
Excel.Application excelApp = null;
Excel.Workbook excelWorkBook = null;
try
{
excelApp = new Excel.Application();
excelWorkBook = excelApp.Workbooks.Open(
srcFile,
ReadOnly: true,
IgnoreReadOnlyRecommended: true,
Notify: false
);
excelWorkBook.ExportAsFixedFormat(
Type: Excel.XlFixedFormatType.xlTypePDF,
Filename: dstFile,
Quality: Excel.XlFixedFormatQuality.xlQualityStandard,
IncludeDocProperties: true,
OpenAfterPublish: false,
IgnorePrintAreas: true
);
}
finally
{
excelWorkBook?.Close(SaveChanges: false);
excelApp?.Quit();
}
}
protected virtual void Dispose(bool disposing)
{
if (IsDisposed)
{
return;
}
ExcelWorkbook?.Close(true);
ExcelApplication?.Quit();
if (ExcelWorksheet != null)
{
Marshal.ReleaseComObject(ExcelWorksheet);
}
if (ExcelWorkbook != null)
{
Marshal.ReleaseComObject(ExcelWorkbook);
}
if (ExcelApplication != null)
{
Marshal.ReleaseComObject(ExcelApplication);
}
ExcelWorksheet = null;
ExcelWorkbook = null;
ExcelApplication = null;
IsDisposed = true;
}
private void WorkBookManager(ExcelWork arg, int rowIndex)
{
Excel.Workbook comparedBook = null;
try
{
comparedBook = excelApp.Workbooks.Open(arg.FileName);
/// Получение нужной страницы
var workSheet = (Excel.Worksheet)comparedBook.Worksheets[arg.WorkSheet];
dynamic value = ReadSheet(workSheet, arg.Column);
WriteToNewSheet(value, rowIndex);
}
catch (Exception ex)
{
/// Возврат Error Message во View
///
throw new Exception(ex.Message);
}
finally
{
comparedBook?.Close();
}
}
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象
#endregion Fields
#region Methods
// Change the Mapping WorkFlow @2015-7-16 By CC
public void EditWorkFlow(string StrTestData)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce，if there is no name then Defult is "1"
objExcelWorkSheet.Select(Type.Missing);
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number
int rowNo = 0;
int ParameterRowNo = 0;
int columnNo = 0;
int FlagColumnNo = 0;
for (int j = 1; j <= columnsint; j++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString();
if (strColumnsName == "Flag")
{
FlagColumnNo = j;
}
if (strColumnsName == "BusinessFlow")
{
rowNo = j;
}
if (strColumnsName == "ParameterValue")
{
ParameterRowNo = j;
}
}
for (int i = 2; i <= rowsint; i++)
{
string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString();
if (strFlagValue =="Y")
{
int RowNO = i;
objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N";
}
string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString();
if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping")
{
columnNo = i;
string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString();
if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn")
{
objExcelWorkSheet.Cells[i, 1] = "Y";
}
}
}
objExcelWorkbook.Save();
objExcelWorkbook.Close(false, StrTestData, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
}
public void Save <T>(IList <T> items) where T : class
{
Excel.Workbook workbook = null;
Excel.Worksheet worksheet;
if (typeof(T).FullName == typeof(OFD).FullName)
{
OfdDataSave(new FileInfo(@".\data\ofd.xlsx"), items.Cast <OFD>().ToList());
}
if (typeof(T).FullName == typeof(User).FullName)
{
UserDataSave(new FileInfo(@".\data\users.xlsx"), items.Cast <User>().ToList());
}
if (typeof(T).FullName == typeof(Store).FullName)
{
FileInfo addressDataFile = new FileInfo(@".\data\address.xlsx");
FileInfo companyDataFile = new FileInfo(@".\data\company.xlsx");
FileInfo storeDataFile = new FileInfo(@".\data\company.xlsx");
addressDataFile.Delete();
companyDataFile.Delete();
storeDataFile.Delete();
var addresses = new Dictionary <int, Address>();
var company = new Dictionary <int, Company>();
var stores = items.Cast <Store>().ToList();
int companyID = 1;
foreach (Store store in stores)
{
if (!addresses.ContainsKey(Convert.ToInt32(store.Number)))
{
addresses.Add(Convert.ToInt32(store.Number), store.Address);
}
if (!company.ContainsValue(store.Owner))
{
company.Add(companyID++, store.Owner);
}
}
workbook = excelApplication.Workbooks.Add();
worksheet = workbook.Sheets.Add();
AddressDataSave(worksheet, addresses);
workbook.SaveAs(addressDataFile.FullName);
workbook.Close();
workbook = excelApplication.Workbooks.Add();
worksheet = workbook.Sheets.Add();
CompanyDataSave(worksheet, company);
workbook.SaveAs(companyDataFile.FullName);
workbook.Close();
workbook = excelApplication.Workbooks.Add();
worksheet = workbook.Sheets.Add();
StoreDataSave(worksheet, stores, company);
workbook.SaveAs(storeDataFile.FullName);
}
workbook?.Close(false);
}
public static List <Comment> LoadComments(string file)
{
List <Comment> res = new List <Comment>();
Excel.Workbook book = null;
try
{
Excel.Application app = new Excel.Application();
book = app.Workbooks.Open(file);
Excel.Worksheet worksheet = (Excel.Worksheet)book.Worksheets.Item[1];
var range = worksheet.UsedRange;
for (int i = 4;; i++)
{
var id = range.Range["A" + i]?.Value2;
if (id == null)
{
break;
}
int j = 1;
var val = range.Range["D2"]?.Value2;
var ok = range.Range["D" + i]?.Value2;
while (val != null)
{
if (ok != null)
{
Comment c = new Comment();
c.StudentId = id.ToString();
c.Descr = val.ToString();
res.Add(c);
}
char letter = (char)('D' + j);
val = range.Range[letter + "2"]?.Value2;
ok = range.Range[letter + "" + i]?.Value2;
j++;
}
}
}
catch (Exception e)
{
MessageBox.Show("Произошла ошибка импорта. " + e.Message, "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return(null);
}
finally
{
book?.Close();
}
return(res);
}
public void Dispose()
{
_wb?.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_statistic);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_dictionary);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_wb);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_app);
}
public List <T> Read <T>(List <ExcelRowClassMapper> mapper) where T : class, new()
{
var l = new List <T>();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp?.Workbooks.Open(FileName);
Excel._Worksheet xlWorksheet = (Excel._Worksheet)(xlWorkbook?.Sheets[1]);
Excel.Range xlRange = xlWorksheet?.UsedRange;
int rowCount = xlRange?.Rows.Count ?? 0;
int colCount = xlRange?.Columns.Count ?? 0;
T item = null;
for (int i = 1; i <= rowCount; i++)
{
if (i == 1 && ColumnNamesInFirstRow)
{
readColumnNames(xlRange, colCount);
}
else
{
for (int j = 1; j <= colCount; j++)
{
if (j == 1)
{
item = new T();
setCommonValues(item, mapper);
}
var cell = xlRange.Cells[i, j] as Excel.Range;
var value = cell?.Value;
var propertyName = getPropertyName(mapper, j);
var property = typeof(T).GetProperty(propertyName);
if (value != null)
{
setValue(item, property, value);
}
}
}
if (!(item is null))
{
l.Add(item);
}
}
xlWorkbook?.Close();
xlApp?.Quit();
return(l);
}
/// <summary>
/// Creates objects needed for the writing process and starts it.
/// </summary>
/// <param name="fileCreationType">
/// The strategy for creating the excel file.
/// It is assumed that the function is only called with <see cref="FileCreationType.UpdateExistingFile" />,
/// <see cref="FileCreationType.CreateEmptyFile" /> or <see cref="FileCreationType.CreateNewFile" />
/// as possible values.
/// </param>
/// <param name="translationsDictionary">
/// The translations that should be written into the dictionary.
/// </param>
/// <param name="path">The path of the excel sheet.</param>
private void CreateExelFileBasedOnCreationType(FileCreationType fileCreationType,
Dictionary<CultureInfo, Dictionary<string, string>> translationsDictionary, string path)
{
var excel = new ExcelInterop.Application();
ExcelInterop.Workbook workbook = null;
try
{
//FileCreationType.UpdateExistingFile
if (fileCreationType == FileCreationType.UpdateExistingFile)
{
workbook = excel.Workbooks.Open(System.IO.Path.GetFullPath(path));
}
//FileCreationType.CreateEmptyFile or FileCreationType.CreateNewFile
else
{
workbook = excel.Workbooks.Add();
}
//FileCreationType.UpdateExistingFile or FileCreationType.CreateNewFile
if (fileCreationType != FileCreationType.CreateEmptyFile)
{
//get parameters.
var worksheet = (ExcelInterop.Worksheet) workbook.Worksheets[1];
var textLocalizations =
TextLocalizationsUtils.FlipLocalizationsDictionary(translationsDictionary);
//write to sheet.
WriteTranslationsToWorksheet(worksheet, textLocalizations);
}
//saving.
excel.DisplayAlerts = false;
//FileCreationType.UpdateExistingFile.
if (fileCreationType == FileCreationType.UpdateExistingFile)
{
workbook.Save();
}
//FileCreationType.CreateEmptyFile or FileCreationType.CreateNewFile.
else
{
workbook.SaveAs(System.IO.Path.GetFullPath(path));
}
}
finally
{
workbook?.Close();
excel.Quit();
}
}
public void Close()
{
workbook?.Close(true, null, null);
application?.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(application);
worksheet = null;
workbook = null;
application = null;
range = null;
log?.Close();
log = null;
}
private void button1_Click(object sender, EventArgs e)
{
var filepath = @"d:\dupa.xlsx";
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(filepath);
MySheet = (Excel.Worksheet)MyBook.Sheets["Arkusz3"]; // Explicit cast is not required here
Excel.Range cell = MySheet.Range[MySheet.Cells[1, 1], MySheet.Cells[4, 4]];
foreach (Excel.Range item in cell)
{
item.Value = string.Format("row:{0:D2} col:{1:D2}", item.Row, item.Column);
}
MyBook.SaveAs(Filename: filepath);
MyBook.Close();
}
//public Write2Exl()
//{
//}
public void WriteInExl(List<string> workList)
{
xlApp = new Excel.Application(); // open Excel App
xlWorkBookTar = xlApp.Application.Workbooks.Open(wrtFilePath); // open Workbook
//wrtBySht("InstrumentClassData");
foreach (string item in workList)
{
wrtBySht(item);
}
xlWorkBookTar.SaveAs(wrtFilePath, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBookTar.Close();
xlApp.Quit();
finProg();
}
public void Parse(object src, object dest)
{
if (src == null || string.IsNullOrEmpty(src.ToString()))
{
throw new ArgumentNullException("源文件不能为空");
}
if (dest == null || string.IsNullOrEmpty(dest.ToString()))
{
throw new ArgumentNullException("目标路径不能为空");
}
try
{
xlsx = excelApp.Workbooks.Open(src.ToString(), missing, missing, missing, missing, missing
, missing, missing, missing, missing, missing, missing, missing, missing, missing);
int startPage = 0;
int endPage = 10;
if (xlsx != null)
{
xlsx.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, dest, missing, missing, missing, missing, missing, missing,
missing);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (xlsx != null)
{
xlsx.Close(false, missing, missing);
xlsx = null;
}
if (excelApp!= null)
{
excelApp.Quit();
excelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void LoadSheetName_RobPlan()
{
_xlApp = new Excel.Application {DisplayAlerts = false};
_xlWorkBook = _xlApp.Workbooks.Open(_pathRobPlan);
int countGroup = 0;
for (int i = 1; i <= _xlWorkBook.Sheets.Count; i++)
{
string name = _xlWorkBook.Worksheets.get_Item(i).Name;
if (name.Length != 8 || name.IndexOf('-') != 2) continue;
SheetNamesRobPlan[countGroup] = _xlWorkBook.Worksheets.get_Item(i).Name;
countGroup++;
}
_xlWorkBook.Close();
_xlApp.Quit();
}
public string ExportToExcel(DocumentModel document,string savePath)
{
savePath = savePath + Path.GetRandomFileName().Replace('.', 'a') + ".xlsx";
myApp = new Excel.Application();
myApp.Visible = false;
myBook = myApp.Workbooks.Add();
mySheet = (Excel.Worksheet)myBook.Sheets[1];
var lastRow = mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
WriteHeadTable(document.HeadTable, ref lastRow);
WriteActs(document.Acts, ref lastRow);
WriteTables(document.Tables, ref lastRow);
mySheet.Columns.AutoFit();
myBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
myBook.Close(false);
myApp.Quit();
return savePath;
}
public void Esegui(string codiceCondominio, decimal importo, string siglaUtente)
{
object oMissing = System.Reflection.Missing.Value;
_thisApplication = new Excel.Application();
_thisApplication.Visible = _verbose;
_spesaturaWorkbook = _thisApplication.Workbooks.Open(_excelFullPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
_spesaturaSheet = (Excel.Worksheet)_spesaturaWorkbook.Worksheets[1];
// Trovo la prima cella libera
int emptyRow = -1;
for (int i = 15; i < 250; i++)
{
string[] columnsIndex = Settings.Default.ColumnIndex.Split(';');
bool found = true;
for (int j = 0; j < columnsIndex.Length; j++)
{
if (((Excel.Range)_spesaturaSheet.Cells[i, int.Parse(columnsIndex[j])]).Value2 != null)
{
found = false;
break;
}
}
if (found)
{
emptyRow = i;
break;
}
}
if (emptyRow > -1)
{
string[] columnsIndex = Settings.Default.UpdatableColumnIndex.Split(';');
valorizzaCelle(emptyRow, int.Parse(columnsIndex[0]), _descrizione);
valorizzaCelle(emptyRow, int.Parse(columnsIndex[1]), DateTime.Today.ToShortDateString());
valorizzaCelle(emptyRow, int.Parse(columnsIndex[2]), importo);
valorizzaCelle(emptyRow, int.Parse(columnsIndex[3]), siglaUtente);
}
_spesaturaWorkbook.Close(true, Type.Missing, Type.Missing);
_thisApplication.Quit();
}
public List<string> readData()
{
prjList.Clear();
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Списки"]; //Получаем ссылку на лист 1
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
prjList.Add(WorkSheetExcel.Cells[i, 1].Text.ToString());
}
WorkBookExcel.Close(false, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
return prjList;
}
internal static void SaveAs(string path, string output, string delimiter)
{
Excel.Application app = null;
Excel.Workbook wb = null;
try
{
app = new Excel.Application();
wb = app.Workbooks.Open(
path,
Format: Excel.XlFileFormat.xlCSV,
Delimiter: delimiter
);
wb.SaveAs(output, Excel.XlFileFormat.xlExcel8);
app.Visible = false;
}
finally
{
wb?.Close();
app?.Quit();
}
}
public void excelWrite()
{
//if (File.Exists(_filePath))
//{
// File.Delete(_filePath);
// FileStream fs = File.Create(_filePath);
// fs.Close();
//}
int i=0;
ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
foreach (string n in _tNames)
{
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 1];
RangeExcel.Value = _tNames[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 2];
RangeExcel.Value = _tTels[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 3];
RangeExcel.Value = _tTels2[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 4];
RangeExcel.Value = _tTels3[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 5];
RangeExcel.Value = _tEmail[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 6];
RangeExcel.Value = _tDiv[i];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 1, 7];
RangeExcel.Value = _tPos[i];
i++;
}
WorkBookExcel.Close(true, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
}
public (Maybe <TrackLayout> item, IEnumerable <Message> messages) GetLayout(string name)
{
var messages = new List <Message>();
var result = new TrackLayout(name);
var app = Excel;
Excel.Workbook book = null;
try
{
var fileName = GetFullFilename(name);
book = app.Workbooks.Open(fileName);
messages.AddRange(GetStations(result, book));
if (messages.CanContinue())
{
messages.AddRange(GetStretches(result, book));
}
}
finally
{
book?.Close(false, GetFullFilename(name));
}
return(Maybe <TrackLayout> .NoneIfError(result, messages.HasStoppingErrors()), messages);
}
public bool Read(string filename)
{
bool bDone = false;
Excel.Application a = new Excel.Application();
Excel.Workbook wb = null;
Excel.Worksheet currentSheet = null;
Process appProcess = GetExcelProcess(a);
try
{
wb = a.Workbooks.Open(filename);
int k = wb.Worksheets.Count < 20 ? wb.Worksheets.Count : 20;
//k = 1;
for (int i = 0; i < k; i++)
{
currentSheet = wb.Worksheets[i + 1];
Sheet sheet = new Sheet
{
Name = currentSheet.Name
};
var lastCell = currentSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell); //последнюю ячейку
// размеры базы
int lastColumn = (int)lastCell.Column;
int lastRow = (int)lastCell.Row;
lastCell = null;
for (int row = 1; row <= lastRow + 1; row++)
{
List <string> cols = new List <string>();
for (int col = 1; col <= lastColumn; col++)
{
cols.Add(currentSheet.Cells[row, col].Text.ToString());
}
sheet.Cells.Add(cols);
}
Sheets[i] = sheet;
}
bDone = true;
}
catch (Exception ex)
{
ClientBase.DialogService.ShowMessage(ex.Message);
}
finally
{
currentSheet = null;
wb?.Close(false, false, false);
wb = null;
a?.Quit();
a = null;
appProcess.Kill();
GC.Collect();
}
return(bDone);
}
private void btnExportToXml_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)|*.xls";
dialog.OverwritePrompt = true;
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
//导出至xml文件
if (dialog.FileName.EndsWith(".xml"))
{
XmlDocument xmldoc = new XmlDocument();
XmlTextWriter writeXml = null;
try
{
writeXml = new XmlTextWriter(dialog.FileName, Encoding.GetEncoding("gb2312"));
}
catch
{
MessageBox.Show("创建或写入文件失败！");
return;
}
writeXml.Formatting = Formatting.Indented;
writeXml.Indentation = 5;
writeXml.WriteStartDocument();
writeXml.WriteStartElement("参数列表");
{
foreach (WeightParameter wp in curWaParas)
{
writeXml.WriteStartElement("参数");
{
writeXml.WriteStartElement("参数名称");
writeXml.WriteString(wp.ParaName);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数单位");
writeXml.WriteString(wp.ParaUnit);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数类型");
writeXml.WriteValue(wp.ParaType);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数数值");
writeXml.WriteValue(wp.ParaValue);
writeXml.WriteEndElement();
}
{
writeXml.WriteStartElement("参数备注");
writeXml.WriteString(wp.ParaRemark);
writeXml.WriteEndElement();
}
writeXml.WriteEndElement();
}
}
writeXml.WriteEndElement();
writeXml.Close();
}
//导出至xml文件
if (dialog.FileName.EndsWith(".xls"))
{
Excel.Application app = new Excel.ApplicationClass();
try
{
Object missing = System.Reflection.Missing.Value;
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Add(missing);
Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet;
Excel.Range DataCell = wSheet.get_Range("A1", "A1");
DataCell.Value2 = "参数名称";
DataCell.Next.Value2 = "参数单位";
DataCell.Next.Next.Value2 = "参数数值";
for (int i = 0; i < curWaParas.Count; ++i)
{
WeightParameter wp = curWaParas[i];
string cellid = "A" + (i + 2).ToString();
DataCell = wSheet.get_Range(cellid, cellid);
DataCell.Value2 = wp.ParaName;
DataCell.Next.Value2 = wp.ParaUnit;
DataCell.Next.Next.Value2 = wp.ParaValue;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.SaveAs(dialog.FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
wBook.Close(false, missing, missing);
app.Quit();
app = null;
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
XLog.Write("成功导出参数值到文件\"" + dialog.FileName + "\"！");
}
//загрузка заявок в таблицу
private async Task LoadTickets(string filePath)
{
await Task.Run(() =>
{
try
{
// создаем объект Excel, открываем указанный пользователем файл, при этом сам Excel не видим
excel_app = new Excel.Application();
excel_app.Visible = false;
excel_book = excel_app.Workbooks.Open(filePath);
// задаем первую страницу
excel_sheet = excel_book.Sheets[1];
// подсчитываем кол-во строк в документе, без учета первой строки
int rowCount = excel_sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row - 1;
this.Invoke((Action)delegate
{
//очищаем таблицу
dgvTable.Rows.Clear();
tsStatusLabel.Text = "Открытие документа";
tsProgressBar.Value = 0;
tsProgressBar.Maximum = rowCount;
// меняем в таблице тип данных в первом столбце на дата\время
Column1.ValueType = typeof(DateTime);
// добавляем строки по кол-во строк в документе Excel
dgvTable.Rows.Add(rowCount);
// устанавливаем для боковой строки ширину
dgvTable.RowHeadersWidth = 50;
//проход по строкам документа
for (int i = 0; i < rowCount; i++)
{
// проход по столбцам строки
for (int j = 0; j < 4; j++)
{
//добавляем значения из документа Excel в таблицу
dgvTable.Rows[i].Cells[j].Value = excel_sheet.Cells[i + 2, j + 1].Value;
}
// заменяем знак "#" на пробел во втором столбце
dgvTable.Rows[i].Cells[2].Value = dgvTable.Rows[i].Cells[2].Value.ToString().Replace('#', ' ');
// нумеруем кол-во строк в таблице
dgvTable.Rows[i].HeaderCell.Value = (i + 1).ToString();
tsProgressBar.Value++;
tsStatusLabel.Text = "Заполнение заявок";
}
});
}
finally
{
// закрываем книгу без сохранения
excel_book.Close(false, false);
// закрываем приложение
excel_app.Quit();
// убиваем процесс
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_app);
// удаляем ссылки на Excel
excel_sheet = null;
excel_book = null;
excel_app = null;
this.Invoke((Action)delegate
{
tsProgressBar.Value = tsProgressBar.Maximum;
tsStatusLabel.Text = "Укажите шаблон";
});
// запуск сборщика мусора
GC.Collect();
}
});
}
private string convert(string filePath)
{
m_path = filePath;
var missing = Type.Missing;
objApp = new Excel.Application();
objWorkbooks = objApp.Workbooks;
Cross_TopTag topTag;
Cross_Data[] data;
Cross_Paragraphs[][] paragraphs;
Cross_Qas[][][] qas;
Cross_Answers[][][][] answers;
object[,] sheet1ValueArray;
object[,] sheet2ValueArray;
int countParagraphs = 0;
int countQas = 0;
int currentRow = 0;
bool excelOpen = false;
try
{
if (m_currentConvertingMode == convertingMode.CJSONToCExcel)
{
#region JSON -> Excel 변환
// ** name1 영역 파싱
topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path));
// name2 영역 파싱
data = new Cross_Data[topTag.data.Count];
for (int i = 0; i < data.Length; i++)
{
data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString());
}
// ** name3 영역 파싱
paragraphs = new Cross_Paragraphs[data.Length][];
for (int i = 0; i < data.Length; i++)
{
paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count];
for (int j = 0; j < data[i].paragraphs.Count; j++)
{
paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString());
countParagraphs++;
}
}
// ** name4 영역 파싱
qas = new Cross_Qas[data.Length][][];
for (int i = 0; i < data.Length; i++)
{
qas[i] = new Cross_Qas[paragraphs[i].Length][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count];
for (int k = 0; k < paragraphs[i][j].qas.Count; k++)
{
qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString());
countQas++;
}
}
}
// ** name5 영역 파싱
answers = new Cross_Answers[data.Length][][][];
for (int i = 0; i < data.Length; i++)
{
answers[i] = new Cross_Answers[paragraphs[i].Length][][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
answers[i][j] = new Cross_Answers[qas[i][j].Length][];
for (int k = 0; k < qas[i][j].Length; k++)
{
answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count];
for (int m = 0; m < qas[i][j][k].answers.Count; m++)
{
answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString());
}
}
}
}
// ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정
sheet1RowCount = countQas;
sheet2RowCount = countParagraphs;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];
// ** sheet1ValueArray & sheet2ValueArray에 데이터 입력
// * paragraph 순번 & name1 영역
for (int row = 0; row < sheet2RowCount; row++)
{
sheet2ValueArray[row, 0] = row + 1;
sheet2ValueArray[row, 1] = topTag.version;
sheet2ValueArray[row, 2] = topTag.creator;
sheet2ValueArray[row, 3] = topTag.progress;
sheet2ValueArray[row, 4] = topTag.formatt;
sheet2ValueArray[row, 5] = topTag.time;
sheet2ValueArray[row, 6] = topTag.check;
sheet2ValueArray[row, 7] = topTag.firstfile;
sheet2ValueArray[row, 8] = topTag.secondfile;
}
// * name2 & name3 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
sheet2ValueArray[currentRow, 9] = data[d].title;
sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context;
sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged;
currentRow++;
}
}
// * name4 영역
currentRow = 0;
int currentParaNum = 1;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
sheet1ValueArray[currentRow, 0] = currentRow + 1;
sheet1ValueArray[currentRow, 1] = qas[d][p][q].id;
sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1;
sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1;
sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1;
sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1;
sheet1ValueArray[currentRow, 6] = qas[d][p][q].question;
sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en;
sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1;
sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1;
sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1;
sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1;
sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1;
sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2;
sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2;
sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2;
sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2;
sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; //
sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; //
sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; //
sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; //
sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2;
sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3;
sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3;
sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3;
sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3;
sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3;
sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3;
sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3;
sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3;
sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3;
sheet1ValueArray[currentRow, 37] = currentParaNum;
currentRow++;
}
currentParaNum++;
}
}
// * name5 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
if (qas[d][p][q].answers.Count > 3)
{
return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + filePath);
}
int answerStartColNum = 31;
for (int a = 0; a < answers[d][p][q].Length; a++)
{
sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text;
sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en;
sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged;
sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn;
sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start;
sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end;
answerStartColNum += 6;
}
currentRow++;
}
}
}
// ** 엑셀로 출력
excelOpen = true;
objWorkbook = objWorkbooks.Add(missing);
objWorksheets = objWorkbook.Worksheets;
// * sheet2 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1);
objWorksheet.Name = "Paragraphs";
range = objWorksheet.get_Range("A1", "M1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet2ColHeader;
Marshal.ReleaseComObject(range);
Excel.Range c1 = objWorksheet.Cells[2, 1];
Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet2ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
// * sheet1 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
objWorksheet.Name = "Qas";
range = objWorksheet.get_Range("A1", "AL1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet1ColHeader;
Marshal.ReleaseComObject(range);
c1 = objWorksheet.Cells[2, 1];
c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet1ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
m_savePath = Path.ChangeExtension(m_path, "xlsx");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists)
{
fi.Delete();
}
objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
#endregion
}
else
{
#region Excel -> JSON 변환
// ** Excel 파일 불러와서 object 이중배열에 데이터 입력
excelOpen = true;
objWorkbook = objWorkbooks.Open(m_path);
objWorksheets = objWorkbook.Worksheets;
objWorksheet = (Excel.Worksheet)objWorksheets[1];
range = objWorksheet.UsedRange;
sheet1ValueArray = (object[, ])range.get_Value(missing);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
objWorksheet = (Excel.Worksheet)objWorksheets[2];
range = objWorksheet.UsedRange;
sheet2ValueArray = (object[, ])range.get_Value(missing);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
// ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력
// * topTag 객체 데이터 입력
topTag = new Cross_TopTag();
topTag.version = sheet2ValueArray[2, 2] == null ? null : sheet2ValueArray[2, 2].ToString();
topTag.creator = sheet2ValueArray[2, 3] == null ? null : sheet2ValueArray[2, 3].ToString();
topTag.progress = Convert.ToInt32(sheet2ValueArray[2, 4]);
topTag.formatt = sheet2ValueArray[2, 5] == null ? null : sheet2ValueArray[2, 5].ToString();
topTag.time = Convert.ToDouble(sheet2ValueArray[2, 6]);
topTag.data = new List <object>();
// * topTag 객체 내의 Data 객체 리스트 입력
IList <object> titleList = new List <object>();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
object tempTitle = sheet2ValueArray[r, 7];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
Cross_Data tempData = new Cross_Data();
tempData.title = tempTitle == null ? "" : tempTitle.ToString();
tempData.paragraphs = new List <object>();
topTag.data.Add(tempData);
}
// * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
int dataCount = 0;
object currentTitle = sheet2ValueArray[2, 7];
List <Cross_Data> tempDataList = topTag.data.Cast <Cross_Data>().ToList();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
Cross_Paragraphs tempParagraphs = new Cross_Paragraphs();
tempParagraphs.context = sheet2ValueArray[r, 8] == null ? null : sheet2ValueArray[r, 8].ToString();
//tempParagraphs.context_original = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_en = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_tagged = sheet2ValueArray[r, 10] == null ? null : sheet2ValueArray[r, 10].ToString();
//if (sheet2ValueArray[r, 11] == null)
//{
// tempParagraphs.context_tagged = null;
//}
//else
//{
// //tempParagraphs.context_tagged = new List<string>();
// string[] tempTagged = sheet2ValueArray[r, 11].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempParagraphs.context_tagged.Add(item);
// }
//}
tempParagraphs.qas = new List <object>();
if (sheet2ValueArray[r, 7] == null || sheet2ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7] == null ? null : sheet2ValueArray[r, 7].ToString();
}
else if (sheet2ValueArray[r, 7] == currentTitle)
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7].ToString();
}
}
topTag.data = tempDataList.Cast <object>().ToList();
// * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
dataCount = 0;
int paragraphCount = 0;
int currentParagraph = 1;
tempDataList = topTag.data.Cast <Cross_Data>().ToList();
List <Cross_Qas> tempQasList = new List <Cross_Qas>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
Cross_Qas tempQas = new Cross_Qas();
tempQas.id = sheet1ValueArray[r, 2] == null ? null : sheet1ValueArray[r, 2].ToString();
tempQas.confuseQt1 = Convert.ToBoolean(sheet1ValueArray[r, 3] == null ? null : sheet1ValueArray[r, 3]);
tempQas.confuseQf1 = Convert.ToBoolean(sheet1ValueArray[r, 4] == null ? null : sheet1ValueArray[r, 4]);
tempQas.confuseSat1 = Convert.ToBoolean(sheet1ValueArray[r, 5] == null ? null : sheet1ValueArray[r, 5]);
tempQas.confuseLat1 = Convert.ToBoolean(sheet1ValueArray[r, 6] == null ? null : sheet1ValueArray[r, 6]);
tempQas.question = sheet1ValueArray[r, 7] == null ? null : sheet1ValueArray[r, 7].ToString();
tempQas.question_en = sheet1ValueArray[r, 8] == null ? null : sheet1ValueArray[r, 8].ToString();
tempQas.question_tagged1 = sheet1ValueArray[r, 9] == null ? null : sheet1ValueArray[r, 9].ToString();
tempQas.questionType1 = sheet1ValueArray[r, 10] == null ? null : sheet1ValueArray[r, 10].ToString();
tempQas.questionFocus1 = sheet1ValueArray[r, 11] == null ? null : sheet1ValueArray[r, 11].ToString();
tempQas.questionSAT1 = sheet1ValueArray[r, 12] == null ? null : sheet1ValueArray[r, 12].ToString();
tempQas.questionLAT1 = sheet1ValueArray[r, 13] == null ? null : sheet1ValueArray[r, 13].ToString();
tempQas.confuseQt2 = Convert.ToBoolean(sheet1ValueArray[r, 14] == null ? null : sheet1ValueArray[r, 14]);
tempQas.confuseQf2 = Convert.ToBoolean(sheet1ValueArray[r, 15] == null ? null : sheet1ValueArray[r, 15]);
tempQas.confuseSat2 = Convert.ToBoolean(sheet1ValueArray[r, 16] == null ? null : sheet1ValueArray[r, 16]);
tempQas.confuseLat2 = Convert.ToBoolean(sheet1ValueArray[r, 17] == null ? null : sheet1ValueArray[r, 17]);
tempQas.questionType2 = sheet1ValueArray[r, 18] == null ? null : sheet1ValueArray[r, 18].ToString();
tempQas.questionFocus2 = sheet1ValueArray[r, 19] == null ? null : sheet1ValueArray[r, 19].ToString();
tempQas.questionSAT2 = sheet1ValueArray[r, 20] == null ? null : sheet1ValueArray[r, 20].ToString();
tempQas.questionLAT2 = sheet1ValueArray[r, 21] == null ? null : sheet1ValueArray[r, 21].ToString();
tempQas.confuseQt3 = Convert.ToBoolean(sheet1ValueArray[r, 22] == null ? null : sheet1ValueArray[r, 22]);
tempQas.confuseQf3 = Convert.ToBoolean(sheet1ValueArray[r, 23] == null ? null : sheet1ValueArray[r, 23]);
tempQas.confuseSat3 = Convert.ToBoolean(sheet1ValueArray[r, 24] == null ? null : sheet1ValueArray[r, 24]);
tempQas.confuseLat3 = Convert.ToBoolean(sheet1ValueArray[r, 25] == null ? null : sheet1ValueArray[r, 25]);
tempQas.questionType3 = sheet1ValueArray[r, 26] == null ? null : sheet1ValueArray[r, 26].ToString();
tempQas.questionFocus3 = sheet1ValueArray[r, 27] == null ? null : sheet1ValueArray[r, 27].ToString();
tempQas.questionSAT3 = sheet1ValueArray[r, 28] == null ? null : sheet1ValueArray[r, 28].ToString();
tempQas.questionLAT3 = sheet1ValueArray[r, 29] == null ? null : sheet1ValueArray[r, 29].ToString();
List <Cross_Answers> tempAnswersList = new List <Cross_Answers>();
// * topTag->Data->Paragraphs->Qas 객체 리스트 내의 Answers 객체 리스트 입력
for (int i = 0; i < 3; i++)
{
int ansStartColNum = 22 + (i * 6);//18
if (sheet1ValueArray[r, ansStartColNum] == null)
{
break; // 정답의 text 공백이면 없음 처리
}
Cross_Answers tempAnswers = new Cross_Answers();
tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? null : sheet1ValueArray[r, ansStartColNum].ToString();
//tempAnswers.text_original = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? null : sheet1ValueArray[r, ansStartColNum + 2].ToString();
tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? null : sheet1ValueArray[r, ansStartColNum + 3].ToString();
//if (sheet1ValueArray[r, ansStartColNum + 3] == null)
//{
// tempAnswers.text_tagged = null;
//}
//else
//{
// tempAnswers.text_tagged = new List<string>();
// string[] tempTagged = sheet1ValueArray[r, ansStartColNum + 3].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempAnswers.text_tagged.Add(item);
// }
//}
//if (sheet1ValueArray[r, ansStartColNum + 4] == null)
//{
// tempAnswers.text_syn = null;
//}
//else
//{
// tempAnswers.text_syn = new List<string>();
// string[] tempSyn = sheet1ValueArray[r, ansStartColNum + 4].ToString().Split(':');
// foreach (var item in tempSyn)
// {
// tempAnswers.text_syn.Add(item);
// }
//}
tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);
tempAnswersList.Add(tempAnswers);
}
tempQas.answers = tempAnswersList.Cast <object>().ToList();
tempQasList.Add(tempQas);
currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 40]); //36
if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
{
if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 40])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
tempQasList = new List <Cross_Qas>();
if (paragraphCount < topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
{
paragraphCount++;
}
else // 도달하고 난 후에는 이렇게 처리
{
dataCount++;
paragraphCount = 0;
}
}
}
if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList();
}
}
// ** JSON 파일로 저장
m_savePath = Path.ChangeExtension(m_path, "json");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists) // 파일이 이미 존재하면 삭제
{
fi.Delete();
}
string saveJSONText;
bool m_EtoJNullRemoveCheck = false;
if (m_EtoJNullRemoveCheck)
{
saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거
}
);
}
else
{
saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Include // Null값 객체 포함
}
);
}
using (StreamWriter sw = new StreamWriter(m_savePath))
{
sw.Write(saveJSONText);
}
#endregion
}
return(STR_CONVERTING_SUCCESS);
}
catch (Exception e)
{
if (excelOpen)
{
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
}
return("예외처리 된 오류 발생.\r\n파일: " + filePath);
}
}
/// <summary>
///
/// </summary>
public void Dispose()
{
wk = axFramerControl1.ActiveDocument as Excel.Workbook;
try
{
wk.Close(Missing.Value, Missing.Value, Missing.Value);
excelapp.Quit();
if (wk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
wk = null;
}
if (wk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
wk = null;
}
GC.Collect();
}
catch
{
if (wk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
wk = null;
}
if (wk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
wk = null;
}
GC.Collect();
}
}
//读取数据 显示到 datagridviewEmo
void runDisplayEmotion()
{
BlogReader breader;
//if (Datas.sinaJSONList.Count > 0)
// Datas.sinaJSONList.Clear();
if (Datas.sinaJSONList.Count == 0)
{
breader = new BlogReader("temp5.dat");
Datas.sinaJSONList = breader.ReadFromFile();
}
Datas.ProgressBarState = "读取完成,正在分析...";
Datas.ProgresBarStyle = System.Windows.Forms.ProgressBarStyle.Marquee;
string emo = "";
//表情键
List<string> emoKey = new List<string>();
List<EmoValue> emoValue = new List<EmoValue>();
using (FileStream fs = new FileStream("datas/emo.txt", FileMode.Open))
{
StreamReader sr = new StreamReader(fs);
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.Length > 0)
{
emoKey.Add(line);
emoValue.Add(new EmoValue());
}
}
sr.Close();
fs.Close();
}
MatchCollection mCollection;
foreach (SinaJSON item in Datas.sinaJSONList)
{
mCollection = Regex.Matches(item.Text, @"\[\w+\]");
for (int i = 0; i < mCollection.Count; i++)
{
emo = mCollection[i].Value;
//emo = emo.Substring(1, emo.Length - 2);
int index = emoKey.IndexOf(emo);
int male = 0, female = 0;
if (item.SinaUser.Gender.Equals("m"))
male++;
else
female++;
if (index != -1)
emoValue[index] = new EmoValue(emoValue[index].male + male, emoValue[index].female + female);
}
}
app = new Excel.Application();
workbooks = app.Workbooks as Excel.Workbooks;
workbook = workbooks.Add(Type.Missing);
app.DisplayAlerts = false;
//删除 多余Sheet
foreach (Worksheet ws in workbook.Worksheets)
if (ws != app.ActiveSheet)
{
ws.Delete();
}
foreach (Chart cht in workbook.Charts)
cht.Delete();
//创建一个Sheet,存数据
//worksheet = (Worksheet)workbook.
// Worksheets.Add(Type.Missing, workbook.ActiveSheet,
// Type.Missing, Type.Missing);
worksheet = workbook.Worksheets[1];
worksheet.Name = "数据";
int worksheetIndex = 0;
for (int i = 0; i < emoKey.Count; i++)
{
if (emoValue[i].male > 0 || emoValue[i].female > 0)
{
worksheet.Cells[i + 1, 1] = emoKey[i];
worksheet.Cells[i + 1, 2] = emoValue[i].male;
worksheet.Cells[i + 1, 3] = emoValue[i].female;
worksheetIndex++;
}
}
// TODO: 生成一个统计图对象：
Chart xlChart = (Chart)workbook.Charts.
Add(Type.Missing, worksheet, Type.Missing, Type.Missing);
// TODO: 设定数据来源
Range cellRange = (Range)worksheet.Cells[1, 1];
// TODO: 通过向导生成Chart
xlChart.ChartWizard(cellRange.CurrentRegion,
XlChartType.xl3DColumn, Type.Missing,
XlRowCol.xlColumns, 1, 0, true,
"表情比较", "表情", "数量");
// TODO: 设置统计图Sheet的名称
xlChart.Name = "统计";
// TODO: 让12个Bar都显示不同的颜色
ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
// TODO: 让Chart的条目的显示形状变成圆柱形，并给它们显示加上数据标签
Series s1 = (Series)grp.SeriesCollection(1);
s1.Name = "男";
s1.BarShape = XlBarShape.xlCylinder;
s1.HasDataLabels = true;
Series s = (Series)grp.SeriesCollection(2);
s.BarShape = XlBarShape.xlCylinder;
s.HasDataLabels = true;
s.Name = "女";
// TODO: 设置统计图的标题和图例的显示
xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
xlChart.ChartTitle.Font.Size = 24;
xlChart.ChartTitle.Shadow = false;
xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;
// TODO: 设置两个轴的属性，Excel.XlAxisType.xlValue对应的是Y轴，Excel.XlAxisType.xlCategory对应的是X轴
Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
valueAxis.AxisTitle.Orientation = -90;
Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
categoryAxis.AxisTitle.Font.Name = "宋体";
//--------------------------------------------------
//workbook.SaveAs(sPath, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing);
xlChart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture);
IntPtr hwnd = (IntPtr)app.Hwnd;
Bitmap b = null;
try
{
if (OpenClipboard(hwnd))
{
IntPtr data = GetClipboardData(14); // CF_ENHMETAFILE 14
if (data != IntPtr.Zero)
{
using (Metafile mf = new Metafile(data, true))
{
b = new Bitmap(mf);
}
}
}
}
finally
{
CloseClipboard();
//clear
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workbook = null;
app = null;
xlChart = null;
GC.Collect();
}
this.Invoke(new updateDataGridViewEmo(doUpdateDataGridViewEmo), new object[] { emoKey, emoValue, b });
}
public static void ExportExcel(DataTable dtBia1, DataTable dtBia2, DataTable dtBia3)
{
try
{
SaveFileDialog sd = new SaveFileDialog();
sd.Filter = "xls files (*.xls)|*.xls";
sd.FilterIndex = 2;
sd.RestoreDirectory = true;
int[] tongDiem = new int[dtBia1.Rows.Count];
if (sd.ShowDialog() == DialogResult.OK)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
// diem bia 1
Excel.Worksheet worksheetBia1 = (Excel.Worksheet)workbook.Worksheets[1];
worksheetBia1.Name = "Điểm số bia 1";
//tao ten collumn
worksheetBia1.Cells[1, 1] = "Tên";
worksheetBia1.Cells[1, 2] = "Lượt 1";
worksheetBia1.Cells[1, 3] = "Lượt 2";
worksheetBia1.Cells[1, 4] = "Lượt 3";
worksheetBia1.Cells[1, 5] = "Tổng";
for (int i = 0; i <= dtBia1.Rows.Count - 1; i++)
{
// tru them 2 cot tong diem va xep loai nen la -3
for (int j = 0; j <= dtBia1.Columns.Count - 3; j++)
{
string data = dtBia1.Rows[i].ItemArray[j].ToString();
// table excel bat dau tu 1 nen phai +1 so vs table C#
worksheetBia1.Cells[i + 1 + 1, j + 1] = data;
//cong tong diem, j = 0 la ten
if (j > 0 && j < dtBia3.Columns.Count - 3)
{
try
{
tongDiem[i] += Int16.Parse(data);
}
catch (Exception exInt)
{ }
}
}
}
worksheetBia1.Columns.AutoFit();
// diem bia 2
Excel.Worksheet worksheetBia2 = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue); //(Excel.Worksheet)workbook.Worksheets[2];
worksheetBia2.Name = "Điểm số bia 2";
//tao ten collumn
worksheetBia2.Cells[1, 1] = "Tên";
worksheetBia2.Cells[1, 2] = "Lượt 1";
worksheetBia2.Cells[1, 3] = "Lượt 2";
worksheetBia2.Cells[1, 4] = "Lượt 3";
worksheetBia2.Cells[1, 5] = "Tổng";
for (int i = 0; i <= dtBia2.Rows.Count - 1; i++)
{
// tru them 2 cot tong diem va xep loai nen la -3
for (int j = 0; j <= dtBia2.Columns.Count - 3; j++)
{
string data = dtBia2.Rows[i].ItemArray[j].ToString();
worksheetBia2.Cells[i + 1 + 1, j + 1] = data;
//cong tong diem, j = 0 la ten
if (j > 0 && j < dtBia3.Columns.Count - 3)
{
try
{
tongDiem[i] += Int16.Parse(data);
}
catch (Exception exInt)
{ }
}
}
}
worksheetBia2.Columns.AutoFit();
// diem bia 3
Excel.Worksheet worksheetBia3 = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue);
worksheetBia3.Name = "Điểm số bia 3";
//tao ten collumn
worksheetBia3.Cells[1, 1] = "Tên";
worksheetBia3.Cells[1, 2] = "Lượt 1";
worksheetBia3.Cells[1, 3] = "Lượt 2";
worksheetBia3.Cells[1, 4] = "Lượt 3";
worksheetBia3.Cells[1, 5] = "Tổng";
for (int i = 0; i <= dtBia3.Rows.Count - 1; i++)
{
// tru them 2 cot tong diem va xep loai nen la -3
for (int j = 0; j <= dtBia3.Columns.Count - 3; j++)
{
string data = dtBia3.Rows[i].ItemArray[j].ToString();
worksheetBia3.Cells[i + 1 + 1, j + 1] = data;
//cong tong diem, j = 0 : la ten, j = dtBia3.Columns.Count - 3 : la tong diem
if (j > 0 && j < dtBia3.Columns.Count - 3)
{
try
{
tongDiem[i] += Int16.Parse(data);
}
catch (Exception exInt)
{ }
}
}
}
worksheetBia3.Columns.AutoFit();
// Worksheet tong diem va ti le phan tram
Excel.Worksheet worksheetTong = (Excel.Worksheet)workbook.Worksheets.Add(misValue, misValue, misValue, misValue);
worksheetTong.Name = "Điểm số tổng";
//tao ten collumn
worksheetTong.Cells[1, 1] = "Tên";
worksheetTong.Cells[1, 2] = "Tổng điểm 3 bia";
worksheetTong.Cells[1, 3] = "Xếp loại";
/*
+ Giỏi: bắn : 72 điểm trở lên.
+ Khá: 59 đến 71 điểm.
+ Đạt: 45 đến 58 điểm.
+ Không đạt: Dưới 45 điểm.
* */
int numGioi = 0;
int numKha = 0;
int numDat = 0;
int numKhongDat = 0;
for (int i = 0; i <= dtBia3.Rows.Count - 1; i++)
{
string data = dtBia3.Rows[i].ItemArray[0].ToString();
worksheetTong.Cells[i + 1 + 1, 1] = data;
worksheetTong.Cells[i + 1 + 1, 2] = tongDiem[i];
string xeploai = "Không đạt";// ngoai dieu kien ben duoi la khong dat thanh tich
if (tongDiem[i] >= 72)
{
xeploai = "Giỏi";
numGioi++;
}
else if (tongDiem[i] >= 59 && tongDiem[i] <= 71)
{
xeploai = "Khá";
numKha++;
}
else if (tongDiem[i] >= 45 && tongDiem[i] <= 58)
{
xeploai = "Đạt";
numDat++;
}
else
{
numKhongDat++;
}
worksheetTong.Cells[i + 1 + 1, 3] = xeploai;
}
worksheetTong.Cells[dtBia3.Rows.Count + 3, 1] = "Tỉ lệ % xếp loại giỏi";
float tlGioi = (float)numGioi / (float)dtBia3.Rows.Count * 100;
worksheetTong.Cells[dtBia3.Rows.Count + 3, 2] = tlGioi + "%";
worksheetTong.Cells[dtBia3.Rows.Count + 4, 1] = "Tỉ lệ % xếp loại khá";
float tlKha = (float)numKha / (float)dtBia3.Rows.Count * 100;
worksheetTong.Cells[dtBia3.Rows.Count + 4, 2] = tlKha + "%";
worksheetTong.Cells[dtBia3.Rows.Count + 5, 1] = "Tỉ lệ % xếp loại đạt";
float tlDat = (float)numDat / (float)dtBia3.Rows.Count * 100;
worksheetTong.Cells[dtBia3.Rows.Count + 5, 2] = tlDat + "%";
worksheetTong.Cells[dtBia3.Rows.Count + 6, 1] = "Tỉ lệ % xếp loại không đạt";
float tlKhongDat = (float)numKhongDat / (float)dtBia3.Rows.Count * 100;
worksheetTong.Cells[dtBia3.Rows.Count + 6, 2] = tlKhongDat + "%";
worksheetTong.Columns.AutoFit();
workbook.SaveAs(sd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(worksheetBia1);
releaseObject(worksheetBia2);
releaseObject(worksheetBia3);
releaseObject(workbook);
releaseObject(xlApp);
MessageBox.Show("Xuất file hoàn tất.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{ }
}
public string GetReport()
{
try
{
object misValue = System.Reflection.Missing.Value;
_excelApp = new Excel.Application();
_excelBook = _excelApp.Workbooks.Add(misValue);
_excelSheet = (Excel.Worksheet)_excelBook.Worksheets.get_Item(1);
_excelSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
_excelApp.Cells[1, 1] = "ЗАО «Специализированная Автошкола»";
_excelApp.Cells[2, 1] = "Юридический адрес:";
_excelApp.Cells[3, 1] = "г. Могилев, ул. Школьная 16-405";
_excelApp.Cells[4, 1] = "УНН: 600359652";
_excelApp.Cells[5, 1] = "Р/с 3012205280013 в Ф-л ОАО Бел";
_excelApp.Cells[6, 1] = "АПБ МОУ г. Могилев,";
_excelApp.Cells[7, 1] = "пр-т Мира 91 код 942";
_excelApp.Cells[8, 1] = "Телефон:(222) 290-45-65";
_excelApp.Cells[9, 1] = "Директор: Хацкевич Андрей Евгеньевич";
_excelApp.Cells[11, 1] = "Расписание теоритических занятий";
_excelApp.Cells[12, 1] = string.Format("на {0} {1} г.", GetMonthName(Month), Year);
_excelApp.Cells[14, 1] = "Дата/Время";
_excelApp.Cells.get_Range("A14", "B14").Merge(Type.Missing);
_excelApp.Columns[1].ColumnWidth = 15;
_excelApp.Cells[14, 1].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[1].VerticalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[2].ColumnWidth = 11;
_excelApp.Columns[2].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[2].VerticalAlignment = Excel.Constants.xlCenter;
//вывод времени
var times = Lessons.Select(l => l.Lessons.Select(t => new
{
StartTime = t.StartTime,
EndTime = t.EndTime,
})).SelectMany(t => t.Select(d => d)).Distinct().OrderBy(d => d.StartTime);
int col = 3;
foreach (var t in times)
{
_excelApp.Cells[14, col] = t.StartTime.ToString() + "-" + t.EndTime.ToString();
_excelApp.Columns[col].ColumnWidth = 19;
_excelApp.Columns[col].HorizontalAlignment = Excel.Constants.xlCenter;
_excelApp.Columns[col].VerticalAlignment = Excel.Constants.xlCenter;
col++;
}
int colCount = col - 1;
var cellHeader1 = (Excel.Range)_excelApp.Cells[11, 1];
var cellHeader2 = (Excel.Range)_excelApp.Cells[11, colCount];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
cellHeader1 = (Excel.Range)_excelApp.Cells[12, 1];
cellHeader2 = (Excel.Range)_excelApp.Cells[12, colCount];
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).Merge(Type.Missing);
_excelApp.Cells.get_Range(cellHeader1, cellHeader2).HorizontalAlignment = Excel.Constants.xlCenter;
//вывод дат
int row = 15;
foreach (var l in Lessons.OrderBy(l => l.Dates.OrderBy(d => d.Day).FirstOrDefault().DayOfWeek))
{
_excelApp.Cells[row, 1] = l.DayOfWeek;
int rowStart = row;
foreach (var d in l.Dates.OrderBy(d => d.Date))
{
_excelApp.Cells[row, 2] = d.ToShortDateString();
row++;
}
int rowEnd = row - 1;
_excelApp.Cells.get_Range(string.Format("A{0}", rowStart), string.Format("A{0}", rowEnd)).Merge(Type.Missing);
for (int i = 3; i < col; i++)
{
Excel.Range cellStart = (Excel.Range)_excelApp.Cells[rowStart, i];
Excel.Range cellEnd = (Excel.Range)_excelApp.Cells[rowEnd, i];
_excelApp.Cells.get_Range(cellStart, cellEnd).Merge(Type.Missing);
}
}
//вывод занятий
for (int i = 3; i < col; i++)
{
for (int j = 15; j < row; j++)
{
string dayOfWeek = _excelApp.Cells[j, 1].Text;
string str1 = ((string)_excelApp.Cells[14, i].Text).Substring(0, 8);
TimeSpan startTime = TimeSpan.Parse(str1);
string str2 = ((string)_excelApp.Cells[14, i].Text).Substring(9, 8);
TimeSpan endTime = TimeSpan.Parse(str2);
var less = Lessons.Where(l => l.DayOfWeek == dayOfWeek).FirstOrDefault();
if (less != null)
{
var item = less.Lessons.Where(l => l.StartTime == startTime && l.EndTime == endTime).FirstOrDefault();
if (item != null)
{
_excelApp.Cells[j, i] = item.Group + "\n" + item.Instructor;
}
}
}
}
Excel.Range cell1 = (Excel.Range)_excelApp.Cells[14, 1];
Excel.Range cell2 = (Excel.Range)_excelApp.Cells[row - 1, col - 1];
var cells = _excelSheet.get_Range(cell1, cell2); // выделяем
cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
cells.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // правая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // левая внешняя
cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // нижняя внешняя
cells.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние вертикальные
cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; // внутренние горизонтальные
//_excelBook.SaveAs(_saveAsPath);
_excelBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Filename: _saveAsPath);
}
catch (Exception)
{
}
finally
{
_excelBook.Close(false);
_excelApp.Quit();
}
return _saveAsPath;
}
public async void btnRunExtraction_Click(object sender, RoutedEventArgs e)
{
GlobalDataBindingValues.Instance.extractionStartTime = DateTime.Now;
var date = datePicker.SelectedDate;
if (date is null)
{
_window.Logger.ErrorText = "Please select a date for extraction.";
return;
}
if (!CredentialStore.Instance.StoreUserCredentials())
{
return;
}
if (CredentialStore.Instance.CancelRun)
{
_window.Logger.WarningText = "Run cancelled by user!";
return;
}
// TODO: Use binding here.
var username = _window.TxtUserName.Text;
var password = _window.TxtPasswordBox.Password;
const int maxFailureCount = 5;
for (var failureCount = 0; failureCount < maxFailureCount; failureCount++)
{
try
{
_window.Logger.NewExtraction("Customer Limit Utilisation Report Extraction Started");
var currentAdaptivEnvironment
= _window.CmbBxAdaptivEnvironments.SelectedValue.ToString();
//CredentialStore.Instance.StoreUserCredentials();
var successfulLogin = await Task.Run(() =>
_window.OpenAdaptivAndLogin(username, password,
currentAdaptivEnvironment));
if (!successfulLogin)
{
_window.Logger.ErrorText = "Failed to run customer limit utilisation extraction!";
return;
}
#region wait for browser
_window.completedLoading = false;
while (!_window.completedLoading)
{
await Task.Run(() => Thread.Sleep(100));
}
await Task.Run(() => Thread.Sleep(1000));
_window.completedLoading = false;
#endregion wait for browser
Action methodName = JavaScriptUtils.JavaScriptErrorDialogFound;
IAsyncResult result = methodName.BeginInvoke(null, null);
_window.InjectJavascript(
nameof(JsScripts.OpenCustomerLimitUtilisationReport),
JsScripts.OpenCustomerLimitUtilisationReport);
_window.InjectJavascript(
nameof(JsScripts.OpenCustomerLimitUtilisationReport),
JsScripts.OpenCustomerLimitUtilisationReport);
_window.WebBrowser.Document?.InvokeScript(nameof(JsScripts.OpenCustomerLimitUtilisationReport));
#region wait for browser
while (!_window.completedLoading)
{
await Task.Run(() => Thread.Sleep(100));
}
await Task.Run(() => Thread.Sleep(1000));
_window.completedLoading = false;
#endregion wait for browser
#region wait for browser
while (!_window.completedLoading)
{
await Task.Run(() => Thread.Sleep(100));
}
await Task.Run(() => Thread.Sleep(1000));
_window.completedLoading = false;
#endregion wait for browser
methodName = JavaScriptErrorDialogFound;
result = methodName.BeginInvoke(null, null);
// TODO: Get filter details for customer limit utilisation report.
var fields = new List <string>();
var conditions = new List <string>();
var criteria = new List <string>();
var conjunctions = new List <string>();
if (cmbBxFilterField1.SelectedIndex != 0)
{
fields.Add(cmbBxFilterField1.SelectedValue.ToString());
}
if (cmbBxFilterField2.SelectedIndex != 0)
{
fields.Add(cmbBxFilterField2.SelectedValue.ToString());
}
if (cmbBxFilterField3.SelectedIndex != 0)
{
fields.Add(cmbBxFilterField3.SelectedValue.ToString());
}
if (cmbBxFilterCondition1.SelectedIndex != 0)
{
conditions.Add(cmbBxFilterCondition1.SelectedValue.ToString());
}
if (cmbBxFilterCondition2.SelectedIndex != 0)
{
conditions.Add(cmbBxFilterCondition2.SelectedValue.ToString());
}
if (cmbBxFilterCondition3.SelectedIndex != 0)
{
conditions.Add(cmbBxFilterCondition3.SelectedValue.ToString());
}
_window.Logger.OkayText = "Filtering customer limit utilisation report...";
_window.InjectJavascript(
nameof(JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis),
JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis);
//_window.InjectJavascript(
// nameof(JsScripts.FilterCustomerLimitUtilisationReport),
// JsScripts.FilterCustomerLimitUtilisationReport(fields, conditions, criteria, conjunctions));
_window.WebBrowser.Document?.InvokeScript(nameof(JsScripts.FilterCustomerLimitUtilisationReportForPortfolioAnalysis));
#region wait for browser
//_window.completedLoading = false;
//while (!_window.completedLoading)
//{
// await Task.Run(() => Thread.Sleep(100));
//}
//await Task.Run(() => Thread.Sleep(1000));
//_window.completedLoading = false;
#endregion wait for browser
methodName.EndInvoke(result);
methodName = JavaScriptErrorDialogFound;
result = methodName.BeginInvoke(null, null);
_window.Logger.OkayText =
$"Opening customer limit utilisation report for {(DateTime)date:dd-MMM-yyyy}...";
_window.InjectJavascript(
nameof(JsScripts.ChooseCustomerLimitUtilisationReport),
JsScripts.ChooseCustomerLimitUtilisationReport);
await Task.Run(() => Thread.Sleep(1000));
_window.WebBrowser.Document?.InvokeScript(
nameof(JsScripts.ChooseCustomerLimitUtilisationReport));
_window.InjectedScripts.Clear();
#region wait for browser
_window.completedLoading = false;
while (!_window.completedLoading)
{
await Task.Run(() => Thread.Sleep(100));
}
await Task.Run(() => Thread.Sleep(1000));
_window.completedLoading = false;
#endregion wait for browser
methodName.EndInvoke(result);
await Task.Run(() => Thread.Sleep(1000));
methodName = JavaScriptErrorDialogFound;
result = methodName.BeginInvoke(null, null);
_window.InjectJavascript(
nameof(JsScripts.SelectCustomerLimitUtilisationReportDate),
JsScripts.SelectCustomerLimitUtilisationReportDate);
_window.WebBrowser.Document?.InvokeScript(
nameof(JsScripts.SelectCustomerLimitUtilisationReportDate),
new object[] { ((DateTime)date).ToString("dd/MM/yyyy") });
#region wait for browser
_window.completedLoading = false;
while (!_window.completedLoading)
{
await Task.Run(() => Thread.Sleep(100));
}
await Task.Run(() => Thread.Sleep(1000));
_window.completedLoading = false;
#endregion wait for browser
methodName.EndInvoke(result);
methodName = JavaScriptErrorDialogFound;
result = methodName.BeginInvoke(null, null);
_window.InjectJavascript(
nameof(JsScripts.GenerateCustomerLimitUtilisationReport),
JsScripts.GenerateCustomerLimitUtilisationReport);
_window.WebBrowser.Document?.InvokeScript(
nameof(JsScripts.GenerateCustomerLimitUtilisationReport));
methodName.EndInvoke(result);
while (_window.WebBrowser.Document?.GetElementsByTagName("img").Count < 5)
{
await Task.Run(() => Thread.Sleep(1000));
}
await Task.Run(() => Thread.Sleep(3000));
methodName = JavaScriptErrorDialogFound;
result = methodName.BeginInvoke(null, null);
_window.InjectJavascript(
nameof(JsScripts.ExportCustomerLimitUtilisationReportToCsv),
JsScripts.ExportCustomerLimitUtilisationReportToCsv);
_window.WebBrowser.Document?.InvokeScript(
nameof(JsScripts.ExportCustomerLimitUtilisationReportToCsv));
var overrideExistingFile = (bool)chkBxOverrideExistingFiles.IsChecked;
await Task.Run(() => Thread.Sleep(1000));
methodName.EndInvoke(result);
await Task.Run(() =>
SaveCustomerLimitUtilisationReport((DateTime)date,
overrideExistingFile));
var csvFile = $"\\\\pcibtighnas1\\CBSData\\Portfolio Analysis\\Data\\Cust Util\\SBG\\CustomerLimitUtil {date:dd.MM.yyyy}.csv";
Dispatcher.Invoke((System.Action)(() =>
{
_window.Logger.OkayText =
"Converting csv extraction to xlsx...";
}));
var xlsxFile = csvFile.Replace(".csv", ".xlsx");
if (overrideExistingFile && File.Exists(xlsxFile))
{
File.Delete(xlsxFile);
Thread.Sleep(1000);
}
MainWindow.ConvertWorkbookFormats(csvFile, ".xlsx");
while (!File.Exists(xlsxFile))
{
await Task.Run(() => Thread.Sleep(100));
}
var fileSize = FileUtils.FileSize(xlsxFile);
Dispatcher.Invoke((System.Action)(() =>
{
_window.Logger.OkayText =
"Performing minor formatting on xlsx file...";
}));
var xlApp = new Excel.Application();
Excel.Workbook wb = xlApp.Workbooks.Open(xlsxFile);
Excel.Worksheet ws = wb.Worksheets[1];
xlApp.DisplayAlerts = false;
ws.Name = "Customer Limit Utilisation";
Excel.Range topLeftCell = ws.Cells[1, 1];
Excel.Range bottomRightCell = ws.Cells[3, 1000];
Excel.Range rangeToDelete = ws.Range[topLeftCell, bottomRightCell];
rangeToDelete.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
wb.Save();
xlApp.DisplayAlerts = true;
wb.Close();
xlApp.Quit();
Dispatcher.Invoke((System.Action)(() =>
{
_window.Logger.OkayText = "Deleting csv file...";
}));
File.Delete(csvFile);
Dispatcher.Invoke((System.Action)(() =>
{
if (_window.extractedFiles.Any(x => x.FilePath == xlsxFile))
{
_window.extractedFiles.Remove(_window.extractedFiles.First(x => x.FilePath == xlsxFile));
}
_window.extractedFiles.Add(new ExtractedFile
{
FilePath = xlsxFile,
FileName = Path.GetFileName(xlsxFile),
FileType = "Customer Limit Utilisation",
FileSize = fileSize
});
}));
_window.Logger.ExtractionComplete("Customer Limit Utilisation");
GlobalDataBindingValues.Instance.extractionEndTime = DateTime.Now;
var timeSpan = GlobalDataBindingValues.Instance.extractionEndTime
- GlobalDataBindingValues.Instance.extractionStartTime;
_window.Logger.OkayTextWithoutTime =
$"Extraction took: {timeSpan.Minutes} minutes {timeSpan.Seconds % 60} seconds";
_window.WebBrowser.Url = new Uri("C:\\GitLab\\AdaptivBot\\ExtractionComplete.html");
break;
}
catch (Exception exception)
{
if (failureCount < maxFailureCount)
{
_window.Logger.ErrorText =
$"Something failed for Customer Limit Utilisation extraction. {exception.Message}";
_window.Logger.ErrorText = $"Trying again. Attempt number: {failureCount + 2}";
}
else
{
_window.Logger.ErrorText = $"Customer Limit Utilisation " +
$"extraction extraction failed {maxFailureCount} times. " +
$"This may be due to an Adaptiv error. Please try again later.";
}
}
}
}
/// <summary>
/// 另存为
/// </summary>
/// <param name="fileName"></param>
public void SaveCopyAs(string fileName)
{
m_objBook.SaveCopyAs(fileName);
m_objBook.Close(false, miss, miss);
}
private void button3_Click(object sender, EventArgs e)
{
timer1.Enabled = true;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtFile.Text);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
rowCount = xlRange.Rows.Count;
colCount = xlRange.Columns.Count;
int number;
int c = 0;
int cc = 0;
bool isParsable = Int32.TryParse(txtRows.Text, out number);
if (isParsable)
{
c = number;
}
object misValue = System.Reflection.Missing.Value;
Excel.Workbook xlWorkBook2;
Excel.Worksheet xlWorkSheet2;
xlWorkBook2 = xlApp.Workbooks.Add(misValue);
xlWorkSheet2 = (Excel.Worksheet)xlWorkBook2.Worksheets.get_Item(1);
int nfile = 0;
progressBar1.Maximum = rowCount;
progressBar1.Step = 1;
int r = 0;
r = c;
for (int x = 1; x <= colCount; x++)
{
xlWorkSheet2.Cells[1, x] = xlWorksheet.Cells[1, x];
}
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if (cc <= c && i <= c)
{
xlWorkSheet2.Cells[i - cc, j] = xlWorksheet.Cells[i, j];
if (i - cc == 1 && checkBox1.Checked)
{
xlWorkSheet2.Cells[1, j] = xlWorksheet.Cells[1, j];
}
}
}
if (i == c && c <= rowCount)
{
cc = c;
c = c + r;
nfile += 1;
xlWorkBook2.SaveAs(txtSave.Text + "\\SplitedFile" + nfile + ".xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook2.Close(true, misValue, misValue);
xlWorkBook2 = xlApp.Workbooks.Add(misValue);
xlWorkSheet2 = (Excel.Worksheet)xlWorkBook2.Worksheets.get_Item(1);
}
progressBar1.PerformStep();
}
nfile += 1;
xlWorkBook2.SaveAs(txtSave.Text + "\\SplitedFile" + nfile + ".xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook2.Close(true, misValue, misValue);
Marshal.ReleaseComObject(xlWorkSheet2);
Marshal.ReleaseComObject(xlWorkBook2);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//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);
MessageBox.Show("Finished!", "Process information", MessageBoxButtons.OK, MessageBoxIcon.Information);
progressBar1.Value = 0;
}
public ActionResult imPortExcel(HttpPostedFileBase excelfile, string organID)
{
// Kiểm tra file đó có tồn tại hay không
if (excelfile == null || excelfile.ContentLength == 0)
{
ViewBag.ListSchool = new Share().listOrgan(2).ToList();
ViewBag.Error = "Thêm File mới<br /> ";
return(View("imPortExcel"));
}
else
{
// kiểm tra đuôi file có phải là file Excel hay không
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
// Khai báo đường dẫn
string path = Path.Combine("D:/", excelfile.FileName);
// Tạo đối tượng COM. Tạo một đối tượng COM cho mọi thứ được tham chiếu
Excel.Application application = new Excel.Application();
// Tạo application cái này là mở ms Excel
Excel.Workbook workbook = application.Workbooks.Open(path);
// Mở WorkBook Mở file Excel mình truyền vào
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
// Mở worksheet Mở sheet đầu tiên
Excel.Range range = worksheet.UsedRange;
//Lặp lại qua các hàng và cột và in ra bàn điều khiển khi nó xuất hiện trong tệp
//excel is not zero based!!
var role = Convert.ToInt32(Session["Role"].ToString());
if (role == 3)
{
var schoolID = Session["SchoolID"].ToString();
for (int i = 2; i < range.Rows.Count; i++)
{
Person person = new Person();
string personID;
do
{
personID = new Share().RandomText();
} while (new CompanyAndSchool().FindPerson(personID) == false);
person.PersonID = personID;
person.LastName = ((Excel.Range)range.Cells[i, 3]).Text;
person.FirstName = ((Excel.Range)range.Cells[i, 4]).Text;
DateTime dateValue = DateTime.FromOADate(Convert.ToDouble(((Excel.Range)range.Cells[i, 5]).Value));
// Dòng code này có ý nghĩa là nó sẽ chuyển đối kiểu số thành ngày lại
person.Birthday = dateValue;
int gender = int.Parse(((Excel.Range)range.Cells[i, 6]).Text);
person.Gender = Convert.ToBoolean(gender);
person.Address = ((Excel.Range)range.Cells[i, 7]).Text;
person.Phone = ((Excel.Range)range.Cells[i, 8]).Text;
person.Email = ((Excel.Range)range.Cells[i, 9]).Text;
person.SchoolID = schoolID;
person.CompanyID = organID;
person.RoleID = 5;
//listproducts.Add(product);
InsertPer(person);
if (SendMailTK(personID))
{
Intern intern = new Intern();
intern.PersonID = personID;
intern.StudentCode = ((Excel.Range)range.Cells[i, 2]).Text;
intern.Result = 0;
InsertInt(intern);
}
}
}
else
{
var companyID = Session["CompanyID"].ToString();
for (int i = 2; i < range.Rows.Count; i++)
{
Person person = new Person();
string personID;
do
{
personID = new Share().RandomText();
} while (new CompanyAndSchool().FindPerson(personID) == false);
person.PersonID = personID;
person.LastName = ((Excel.Range)range.Cells[i, 3]).Text;
person.FirstName = ((Excel.Range)range.Cells[i, 4]).Text;
//person.Birthday = DateTime.ParseExact(((Excel.Range)range.Cells[i, 4]).Text,"yyyy/MM/dd",null);
DateTime dateValue = DateTime.FromOADate(Convert.ToDouble(((Excel.Range)range.Cells[i, 5]).Value));
person.Birthday = dateValue;
int gender = int.Parse(((Excel.Range)range.Cells[i, 6]).Text);
//person.Gender = bool.Parse(Convert.ToUInt32(((Excel.Range)range.Cells[i, 5]).Value));
person.Gender = Convert.ToBoolean(gender);
person.Address = ((Excel.Range)range.Cells[i, 7]).Text;
person.Phone = ((Excel.Range)range.Cells[i, 8]).Text;
person.Email = ((Excel.Range)range.Cells[i, 9]).Text;
person.CompanyID = companyID;
person.RoleID = 5;
//listproducts.Add(product);
InsertPer(person);
if (SendMailTK(personID))
{
Intern intern = new Intern();
intern.PersonID = personID;
intern.StudentCode = ((Excel.Range)range.Cells[i, 2]).Text;
intern.Result = 0;
InsertInt(intern);
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//xuất các đối tượng com để dừng hoàn toàn quá trình excel chạy trong nền
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(worksheet);
//đóng lại và xuất thông tin
workbook.Close();
Marshal.ReleaseComObject(workbook);
//thoát và xuất thông tin
application.Quit();
Marshal.ReleaseComObject(application);
//ViewBag.ListProduct = listproducts;
//dem = listproducts.Count();
//return View("List");
ViewBag.ListSchool = new Share().listOrgan(2).ToList();
ViewBag.Error = "Thêm thành công<br /> ";
return(View("imPortExcel"));
}
else
{
ViewBag.ListSchool = new Share().listOrgan(2).ToList();
ViewBag.Error = "File không hợp lệ<br /> ";
return(View("imPortExcel"));
}
}
}
private void button2_Click_1(object sender, EventArgs e)
{
int CONT = 1;
var excel = new Excel.Application();
Excel.Workbook librodetrabajo = excel.Workbooks.Open(Label1);
//string conString = @"Data Source=" + Server.Text + ";Initial Catalog=" + DataBase.Text + ";User Id=" + User.Text + ";password="******"";
//string POS = "";
//for (int i = 1; i <= colCount; i++)
//{
// POS += "`" + xlRange.Cells[1, i].Value2.ToString() + "`,";
//}
//okf = POS.Substring(0, POS.Length - 1);
for (int b = 2; b <= rowCount; b++)
{
string value = "";
string resultExcel = "";
for (int c = 1; c <= colCount; c++)
{
value += xlRange.Cells[b, c].Value2.ToString() + ",";
resultExcel = value.Substring(0, value.Length - 1);
}
string[] valores = resultExcel.Split(',');
string contratoarea = valores[0];
string verticePoligono = valores[1];
latitud = Convert.ToInt32(valores[3]);
longitud = Convert.ToInt32(valores[2]);
m1 = latitud / 10000; //Primeros 2 números
int m4 = latitud % Convert.ToInt32(digitos);
string result = m4.ToString();
int ZeroConditional = int.Parse(result);
string sub = "";
if (ZeroConditional == 0)
{
try
{
divMin = 0;
}
catch (Exception ex)
{
throw ex;
}
}
else
{
try
{
sub = result.Substring(0, result.Length - 2); // Segundos dos números de en medio
if (sub == "")
{
sub = "0";
}
}
catch (Exception ex)
{
throw ex;
}
}
m2 = (latitud % (10000));
m3 = latitud % 100;
//--------------------------------------------
//Console.WriteLine(m1+" "+sub+" "+m3);
if (ZeroConditional == 0)
{
divMin = 0;
}
else
{
divMin = Convert.ToDouble(sub) / minutos; //Números de en medio entre minutos
}
divSeg = m3 / segundos;
//--------------Asignar valor a LATITUD Y LONGITUD
//Console.WriteLine(m1 + " " + divMin + " " + divSeg);
double suma = m1 + divMin + divSeg;
if (contratoarea == "CNH-R01-L03-A18/2015" && verticePoligono == "10")
{
}
m11 = longitud / 10000;
int m44 = longitud % Convert.ToInt32(digitos);
string result2 = m44.ToString();
string sub1 = "";
if (result2 == "0")
{
sub1 = "0";
}
else
{
sub1 = result2.Substring(0, result2.Length - 2);
if (sub1 == "")
{
sub1 = "0";
}
}
m22 = (longitud % (10000));
m33 = longitud % 100;
//--------------------------------------------
//Console.WriteLine(m1+" "+sub+" "+m3);
divMin1 = Convert.ToDouble(sub1) / minutos;
divSeg1 = m33 / segundos;
//--------------Asignar valor a LATITUD Y LONGITUD
//Console.WriteLine(m1 + " " + divMin + " " + divSeg);
double suma2 = m11 + divMin1 + divSeg1;
query = $"insert into CO_Coordenadas (IdAreaContractual,lat,lng, poligono) values ({contratoarea},'{suma}','-{suma2}',{verticePoligono})";
str.WriteLine(query);
}
}
str.Close();
librodetrabajo.Close();
MessageBox.Show("final");
}
public static List <Question> Read(string excelFile, IEnumerable <string> tabs)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
int hWnd = 0;
try
{
List <Question> listOfQUestions = new List <Question>();
xlApp = new Excel.Application();
hWnd = xlApp.Hwnd;
xlWorkbook = xlApp.Workbooks.Open(excelFile);
//Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[6];
Dictionary <string, Excel._Worksheet> dict = new Dictionary <string, Excel._Worksheet>();
foreach (Excel._Worksheet worksheet in xlWorkbook.Worksheets)
{
dict.Add(worksheet.Name, worksheet);
}
tabs.ToList().ForEach(s =>
LoadWorksheet(listOfQUestions, GetWorksheet(dict, s)));
List <Question> allQuestions = new List <Question>();
allQuestions.AddRange(listOfQUestions);
allQuestions.AddRange(listOfQUestions.SelectMany(s => s.Children));
foreach (var question in allQuestions)
{
question.Expressions = GetExpressionsForrPresentation(allQuestions, question.DisplayRule);
question.Expressions.Questions.ToList().ForEach(s => allQuestions.First(q => q.Id == s).LogicalChildren.Add(question.Id));
}
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
return(listOfQUestions);
}
finally
{
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
if (xlApp.Workbooks != null)
{
Marshal.ReleaseComObject(xlApp.Workbooks);
}
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
SendMessage((IntPtr)hWnd, 0x10, IntPtr.Zero, IntPtr.Zero);
}
/* https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/
* if (sheets != null) Marshal.ReleaseComObject(sheets);
* if (book != null) Marshal.ReleaseComObject(book);
* if (books != null) Marshal.ReleaseComObject(books);
* if (app != null) Marshal.ReleaseComObject(app);
*/
}
private void ExportDataSetToExcel(DataSet ds, string strPath)
{
int inHeaderLength = 3, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
//strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
_excel.Application excelApp = new _excel.Application();
_excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (System.Data.DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
_excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName;//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1; //
inRow = inHeaderLength + 2 + m; //2
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "I" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
_excel.Range RowL = excelWorkSheet.get_Range("A5", "I6");
RowL.EntireRow.RowHeight = 80;
}
//merge E4 - I4
_excel.Range M = excelWorkSheet.get_Range("E4", "I4");
M.Merge(true);
//Excel Header
_excel.Range cellRang = excelWorkSheet.get_Range("A1", "I3");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = _excel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = _excel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
excelWorkSheet.Cells[1, 1] = "GRIPPER DESIGH";
excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Flat.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 60, 200, 80);
cellRang.EntireRow.RowHeight = 20;
// add Vacuum Ejector pic
excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Vacuum Ejector.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 150, 200, 80);
// add Draft design
_excel.Range Df = excelWorkSheet.get_Range("A8", "I10");
Df.Merge(false);
Df.Interior.Color = System.Drawing.Color.White;
Df.Font.Color = System.Drawing.Color.Gray;
Df.HorizontalAlignment = _excel.XlHAlign.xlHAlignCenter;
Df.VerticalAlignment = _excel.XlVAlign.xlVAlignCenter;
Df.Font.Size = 26;
Df.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
Df.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.Cells[8, 1] = "DRAFT DESIGH";
//add df pic
_excel.Range Dfp = excelWorkSheet.get_Range("A11", "I24");
Dfp.Merge(false);
excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Gripper.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 300, 200, 80);
excelWorkSheet.Shapes.AddPicture(@"C:\Users\palmdotax\source\repos\GRipperDesign\Picture\Draft\Vacuum pad Distance.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 200, 300, 200, 80);
//Style table column names
cellRang = excelWorkSheet.get_Range("A4", "G4");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = _excel.XlHAlign.xlHAlignRight;
//Formate price column
excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as _excel._Worksheet).Activate();
excelWorkBook.SaveAs(@"C:\Users\palmdotax\source\repos\GRipperDesign\BOM.xlxs");
excelWorkBook.Close();
excelApp.Quit();
MessageBox.Show("Excel generated successfully \n As " + strPath);
}
private void ExportExcel(string path)
{
bool success = false;
object misValue = System.Reflection.Missing.Value;
excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null) {
MessageBox.Show("Excel is not properly installed!!");
return;
}
worker = new BackgroundWorker();
worker.WorkerSupportsCancellation = true;
worker.WorkerReportsProgress = true;
PawnGuardDBDataContext pawnguard = new PawnGuardDBDataContext();
ProgStackPanel.Visibility = System.Windows.Visibility.Visible;
ProgTextCancel.Visibility = System.Windows.Visibility.Collapsed;
ProgressGrid.Visibility = System.Windows.Visibility.Visible;
StoneListGrid.Visibility = System.Windows.Visibility.Hidden;
Import.IsEnabled = false;
Export.IsEnabled = false;
this.MinHeight = 500;
ProgText.Text = "Exporting Data...";
ProgressStone.Value = 0;
worker.DoWork += delegate(object sdr, DoWorkEventArgs dw) {
Excel.Application _excelApp = (Excel.Application)dw.Argument;
xlWorkBook = _excelApp.Workbooks.Open(path);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
List<Stone> stones = pawnguard.Stones.ToList<Stone>();
for (int row = 0; row < stones.Count; row++) {
for (int col = 0; col < 13; col++) {
if (col == 0) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].rarity;
if (col == 1) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].name;
if (col == 2) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].carat;
if (col == 3) xlWorkSheet.Cells[row + 2, col + 1] = stones[row].price;
}
worker.ReportProgress(Convert.ToInt32(((decimal)row / (decimal)stones.Count) * 100));
System.Threading.Thread.Sleep(100);
if (worker.CancellationPending) {
ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Canceling..."; }));
System.Threading.Thread.Sleep(2000);
dw.Cancel = true;
return;
}
}
};
worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) {
ProgressStone.Value = args.ProgressPercentage;
};
worker.RunWorkerCompleted += delegate(object sdr, RunWorkerCompletedEventArgs rwc) {
if (rwc.Error != null) {
MessageBox.Show(rwc.Error.Message);
return;
}
if (!rwc.Cancelled) {
ProgressStone.Value = 100;
SaveFileDialog dlg = new SaveFileDialog();
dlg.FileName = "Stone Template.xlsx";
dlg.DefaultExt = ".xlsx";
dlg.Filter = "Stone Template|*.xlsx";
Nullable<bool> result = dlg.ShowDialog();
if (result == true) {
string filename = dlg.FileName;
xlWorkBook.SaveAs(filename);
success = true;
path = filename;
}
} else {
ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Cancelled"; }));
}
xlWorkBook.Close(true, misValue, misValue);
excelApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(excelApp);
ProgressGrid.Visibility = System.Windows.Visibility.Hidden;
StoneListGrid.Visibility = System.Windows.Visibility.Visible;
Import.IsEnabled = true;
Export.IsEnabled = true;
this.MinHeight = 300;
if (success) {
System.Diagnostics.Process.Start(path);
}
};
worker.RunWorkerAsync(excelApp);
}
private void exportExcelv2()
{
if (dt_value.Rows.Count == 0) { MessageBox.Show("ไม่มีข้อมูล"); return; }
var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;
try
{
this.Cursor = Cursors.WaitCursor;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 15]].Font.Bold = true;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 1] = "รหัสสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].EntireColumn.ColumnWidth = 50;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 2] = "ชื่อสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 3] = "ม.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 4] = "ก.พ.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 5] = "มี.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 6] = "ม.ย.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 7] = "พ.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 8] = "มิ.ย.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 9] = "ก.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 10] = "ส.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 11] = "ก.ย.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 12] = "ต.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 13] = "พ.ย.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 14] = "ธ.ค.";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 15] = "รวม";
for (int i = 0; i < dt_value.Rows.Count; i++)
{
xlWorkSheet.Range[xlWorkSheet.Cells[i + 2, 3], xlWorkSheet.Cells[i + 2, 5]].NumberFormat = "#,##0.00";
}
//xlWorkSheet.Range[xlWorkSheet.Cells[1, (countSpan_shop * (j + 1)) + 2], xlWorkSheet.Cells[dt_shop.Rows.Count + 2, (countSpan_shop * (j + 1)) + 2]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);
for (int i = 0; i < dt_value.Rows.Count; i++)
{
for (int j = 0; j < dt_value.Columns.Count; j++)
{
xlWorkSheet.Cells[i + 2, j + 1] = dt_value.Rows[i][j].ToString();
}
}
string fileName = String.Empty;
SaveFileDialog saveFileExcel = new SaveFileDialog();
saveFileExcel.FileName = "" + DBConnString.sDb + " สรุปมูลค่าการขายแต่ละเดือน(แยกตามสินค้า) ปี " + CmbYear.Text + " ";
saveFileExcel.Filter = "Excel files (*.xls,*.xlsx)|*.xls*";
saveFileExcel.FilterIndex = 2;
saveFileExcel.RestoreDirectory = true;
if (saveFileExcel.ShowDialog() == DialogResult.OK)
{
fileName = saveFileExcel.FileName;
xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
this.Cursor = Cursors.Default;
}
else
{
return;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
this.Cursor = Cursors.Default;
}
}
private void ImportExcel(string path)
{
object rarity = null;
object name = null;
object carat = null;
object price = null;
object misValue = System.Reflection.Missing.Value;
excelApp = new Excel.Application();
Excel.Range range;
worker = new BackgroundWorker();
worker.WorkerSupportsCancellation = true;
worker.WorkerReportsProgress = true;
ProgStackPanel.Visibility = System.Windows.Visibility.Visible;
ProgTextCancel.Visibility = System.Windows.Visibility.Collapsed;
ProgressGrid.Visibility = System.Windows.Visibility.Visible;
StoneListGrid.Visibility = System.Windows.Visibility.Hidden;
Import.IsEnabled = false;
Export.IsEnabled = false;
this.MinHeight = 500;
ProgText.Text = "Importing Data...";
ProgressStone.Value = 0;
worker.DoWork += delegate(object sdr, DoWorkEventArgs dw) {
Excel.Application _excelApp = (Excel.Application)dw.Argument;
xlWorkBook = _excelApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
List<Stone> stoneItem = new List<Stone>();
for (int row = 0; row <= range.Rows.Count; row++) {
for (int col = 0; col <= range.Columns.Count; col++) {
if ((range.Cells[row + 2, col + 1] as Excel.Range).Value2 != null) {
if (col == 0) rarity = (range.Cells[row + 2, col + 1] as Excel.Range).Value2;
if (col == 1) name = (range.Cells[row + 2, col + 1] as Excel.Range).Value2;
if (col == 2) carat = (range.Cells[row + 2, col + 1] as Excel.Range).Value2;
if (col == 3) price = (range.Cells[row + 2, col + 1] as Excel.Range).Value2;
} else {
if (col == 0) rarity = string.Empty;
if (col == 1) name = string.Empty;
if (col == 2) carat = string.Empty;
if (col == 3) price = string.Empty;
}
}
if (worker.CancellationPending) {
ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Canceling..."; }));
System.Threading.Thread.Sleep(3000);
dw.Cancel = true;
return;
}
try {
if (!string.IsNullOrWhiteSpace(name.ToString())) {
stoneItem.Add(new Stone {
Id = row + 1,
name = name.ToString(),
rarity = rarity.ToString(),
carat = carat.ToString(),
price = price.ToString(),
});
}
} catch (Exception) {
ProgText.Dispatcher.Invoke(new Action(() => { ProgText.Text = "Invalid Template!"; }));
System.Threading.Thread.Sleep(1300);
worker.CancelAsync();
}
worker.ReportProgress(Convert.ToInt32(((decimal)row / (decimal)range.Rows.Count) * 100));
System.Threading.Thread.Sleep(5);
dw.Result = stoneItem;
}
};
worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) {
ProgressStone.Value = args.ProgressPercentage;
};
worker.RunWorkerCompleted += delegate(object sdr, RunWorkerCompletedEventArgs rwc) {
xlWorkBook.Close(true, misValue, misValue);
excelApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(excelApp);
ProgressGrid.Visibility = System.Windows.Visibility.Hidden;
StoneListGrid.Visibility = System.Windows.Visibility.Visible;
Import.IsEnabled = true;
Export.IsEnabled = true;
this.MinHeight = 300;
if (rwc.Error != null) {
MessageBox.Show(rwc.Error.Message);
return;
}
if (!rwc.Cancelled) {
List<Stone> stone = (List<Stone>)rwc.Result;
for (int i = 0; i < stone.Count; i++) {
stone[i].parentId = AppData.parentId;
stone[i].updatedAt = DateTime.Now;
stone[i].createdAt = DateTime.Now;
}
pawnguardDb = new PawnGuardDBDataContext();
LinqExt.Truncate(pawnguardDb.Stones);
pawnguardDb.Stones.InsertAllOnSubmit(stone);
pawnguardDb.SubmitChanges();
FillGrid();
page_Loaded(null, new RoutedEventArgs());
}
};
worker.RunWorkerAsync(excelApp);
}
public getData readData()
{
String name;
String position;
String tel;
String workTel;
String email;
String company;
getData.name.Clear();
getData.position.Clear();
getData.tel.Clear();
getData.workTel.Clear();
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1
//excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1
// WorkSheetExcel.Cells[i, 1].Text.ToString() != ""
for (int i = 3; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
name = CleanString(WorkSheetExcel.Cells[i, 1].Text.ToString());
position = CleanString(WorkSheetExcel.Cells[i, 2].Text.ToString());
tel = CleanString(WorkSheetExcel.Cells[i, 3].Text.ToString());
workTel = CleanString(WorkSheetExcel.Cells[i, 4].Text.ToString());
email = CleanString(WorkSheetExcel.Cells[i, 5].Text.ToString());
company = CleanString(WorkSheetExcel.Cells[i, 6].Text.ToString());
getData.name.Add(name);
getData.position.Add(position);
getData.tel.Add(tel);
getData.workTel.Add(workTel);
getData.email.Add(email);
getData.company.Add(company);
}
WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя
ExcelApp.Quit();
GC.Collect();
//Запись в БД
dbc.ClearTable("customer");
dbc.CustomerWrite(getData.name, getData.position, getData.tel, getData.workTel, getData.email, getData.company);
return getData;
}
private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka)
{
excelapp = new Excel.Application();
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka),
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelsheets = excelappworkbook.Worksheets;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
for (int i = 0; i < 350; i++)
{
if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
else
{
z3++;
if (z3 == 1) z1 = 0;
s = vsS[z2] + vsS[z1] + a1.ToString();
if (z1 == 25) { z2++; z1 = 0; }
}
if (s == "IW" + a1.ToString()) break;
excelcells = excelworksheet.get_Range(s, Type.Missing);
string excelnumber = excelcells.Value2;
if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
z1++;
}
z1 = 0;
z2 = 0;
z3 = 0;
excelappworkbook.Close();
excelappworkbooks.Close();
excelapp.Workbooks.Close();
}
/// <summary>
/// Сохраняем файлы в формате пдф и ексель в папки
/// </summary>
public void MakeExcelFile(decimal moneyAtStart, decimal moneyBalance)
{
string dateFile; //Повна назва файлу для зберігання
try
{
CreateFolderForSavingFile(excelFilePath, out dateFile);
oApp = new Excel.Application();
oBook = oApp.Workbooks.Add();
oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);
// Заповнюємо excel файл данними
CreateFullFile();
InputInformationFields(moneyAtStart, moneyBalance);
// Зберігаємо файл в форматі екселя
oBook.SaveAs(dateFile + ".xlsx");
CreateFolderForSavingFile(pdfFilePath, out dateFile);
// Додаткова перевірка при зберіганні pdf файла на встановлене розширення в Office
try
{
oBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, dateFile + ".pdf");
}
catch (ArgumentException)
{
MessageBox.Show("Помилка при збереженні PDF файла. Перевірте чи у Вас встановлене розширення в "
+ " Microsoft Office для збереження файлів в форматі PDF/XPS.", "Помилка при збереженні PDF",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
catch (Exception)
{
MessageBox.Show("Помилка при збереженні pdf файла.");
}
MessageBox.Show("Дані збережено!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
}
finally
{
oBook.Close();
oApp.Quit();
}
}
void bw_DoWork(object sender, DoWorkEventArgs e)
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
theBook = app.Workbooks.Open(_fileName, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка при открытии книги");
}
try
{
_sheets.Clear();
foreach (Excel.Worksheet w in theBook.Worksheets)
{
_sheets.Add(w.Name);
}
// Закрываем Excel
theBook.Close(false, Type.Missing, Type.Missing);
app.Quit();
theBook = null;
app = null;
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
private void BtnImportExcel_Click(object sender, EventArgs e)
{
// Open the file.
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Ms Excel 2007 files (*.xlsx)|*.xlsx|Ms Excel 2003 files (*.xls)|*.xls|Text Files (*.txt)|*.txt";
dialog.InitialDirectory = "C:";
dialog.Title = "Open file";
if (dialog.ShowDialog() == DialogResult.OK)
{
fileName = dialog.FileName;
// Get file's extension.
FileInfo fileInfo = new FileInfo(fileName);
fileExtension = fileInfo.Extension;
}
// Initialize the emails list.
emails = new List <string>();
if (fileExtension == ".txt")
{
string line;
StreamReader file = new StreamReader(fileName);
while ((line = file.ReadLine()) != null)
{
emails.Add(line);
}
}
else
{
Excel.Application excelApp = new Excel.Application();
// Read the excel file.
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
// Get rows and columns count.
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= cols; j++)
{
Excel.Range range = (excelWorksheet.Cells[i + 1, 1] as Excel.Range);
string cellValue = range.Value.ToString() ?? string.Empty;
// Add to list.
emails.Add(cellValue);
}
}
excelWorkbook.Close();
excelApp.Quit();
}
}
// Enable the other buttons.
btnFindDuplicates.Enabled = true;
btnExportEmailsWithoutDuplicates.Enabled = true;
btnExportPrivateBusinessEmails.Enabled = true;
}
private void ExportToExcel_gp()
{
if (dt_gp.Rows.Count == 0) { MessageBox.Show("ไม่มีข้อมูล"); return; }
var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;
try
{
this.Cursor = Cursors.WaitCursor;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0; int z = 0;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 12]].Font.Bold = true;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 1] = "วันที่";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].EntireColumn.ColumnWidth = 13;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 2] = "เลขที่ขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 3] = "รหัสสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].EntireColumn.ColumnWidth = 60;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 4] = "ชื่อสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 5], xlWorkSheet.Cells[1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 5] = "จำนวน";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 6], xlWorkSheet.Cells[1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 6] = "หน่วยนับ";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 7], xlWorkSheet.Cells[1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 7] = "ราคาขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 8], xlWorkSheet.Cells[1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 8] = "ราคาทุน";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 9], xlWorkSheet.Cells[1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 9] = "กำไรขั้นต้น";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].EntireColumn.ColumnWidth = 10;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 10], xlWorkSheet.Cells[1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 10] = "GP%";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 11], xlWorkSheet.Cells[1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 11] = "ราคาขายรวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 12], xlWorkSheet.Cells[1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 12] = "ราคาทุนรวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 13], xlWorkSheet.Cells[1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 13] = "กำไร(ขาดทุน) รวม";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 14], xlWorkSheet.Cells[1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 14] = "ชื่อผู้ขาย";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 15], xlWorkSheet.Cells[1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 15] = "ชื่อลูกค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 16], xlWorkSheet.Cells[1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 16] = gvGP.Columns[15].HeaderText;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].EntireColumn.ColumnWidth = 20;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 17], xlWorkSheet.Cells[1, 17]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 17] = gvGP.Columns[16].HeaderText;
object[,] oo = new object[dt_gp.Rows.Count, dt_gp.Columns.Count];
for (i = 0; i < dt_gp.Rows.Count; i++)
{
for (int j = 0; j < dt_gp.Columns.Count; j++)
{
string value = "";
try
{
decimal num = 0;
DateTime dateTime = new DateTime();
value = dt_gp.Rows[i][j].ToString();
if (DateTime.TryParse(value, out dateTime) && !Decimal.TryParse(value, out num))
{
oo[i, j] = (dateTime).ToOADate();
//xlWorkSheet.Cells[i + 2, j + 1] = (dateTime).ToOADate();
//xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).NumberFormat = "dd-mm-yyyy";
//xlWorkSheet.get_Range("A" + (i + 2), "A" + (j + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
else
{
oo[i, j] = dt_gp.Rows[i][j].ToString();
//xlWorkSheet.Cells[i + 2, j + 1] = dt_gp.Rows[i][j].ToString();
}
}
catch (Exception ex)
{
}
}
}
string excelRange = string.Format("A2:{0}{1}",
findColumnLetter(dt_gp.Columns.Count), dt_gp.Rows.Count + 1);
xlWorkSheet.get_Range(excelRange, Type.Missing).Value2 = oo;
xlWorkSheet.get_Range("A2", "A" + dt_gp.Rows.Count + 1).NumberFormat = "dd-mm-yyyy";
xlWorkSheet.get_Range("G2", "G" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
xlWorkSheet.get_Range("H2", "H" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
xlWorkSheet.get_Range("I2", "I" + dt_gp.Rows.Count + 1).NumberFormat = "#,##0.00";
string fileName = String.Empty;
SaveFileDialog saveFileExcel = new SaveFileDialog();
saveFileExcel.FileName = "" + DBConnString.sDb + " GP ตั้งแต่วันที่ " + dateTimePicker1.Text + " ถึง " + dateTimePicker2.Text + " ";
saveFileExcel.Filter = "Excel files (*.xls,*.xlsx)|*.xls*";
saveFileExcel.FilterIndex = 2;
saveFileExcel.RestoreDirectory = true;
if (saveFileExcel.ShowDialog() == DialogResult.OK)
{
fileName = saveFileExcel.FileName;
xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
this.Cursor = Cursors.Default;
MessageBox.Show("Export " + saveFileExcel.FileName + " Complete.");
}
else
{
return;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
this.Cursor = Cursors.Default;
}
}
private void SaveProj_Click(object sender, RoutedEventArgs e)
{
ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false);
WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"];
List<string> row = new List<string>();
int n = 6;
int count = 0;
row = new List<string>();
row.Add(Convert.ToString(prjSelect2.SelectedValue));
row.Add(prjHour.Text);
int statSpendHour1=0;
int statOstHour1=0;
float statOstDay1=0;
getData = rwList.readData();
for (int i = 0; i < getData.prj.Count; i++)
{
notes.Add(new Note()
{
dateT = getData.dateT[i],
project = getData.prj[i],
description = getData.descr[i],
taskTime = getData.taskTime[i]
});
}
//notes = rwList.readData();
for (int i = 0; i < notes.Count; i++)
{
if (Convert.ToString(prjSelect2.SelectedValue) == notes[i].project)
{ statSpendHour1 = statSpendHour1 + Convert.ToInt16(notes[i].taskTime); }
//statSpendHour1 = statSpendHour1 + Convert.ToInt16(getData.taskTime[i]);
}
statOstHour1 = Convert.ToInt32(prjHour.Text) - statSpendHour1;
statOstDay1 = statOstHour1 / 8;
row.Add(Convert.ToString(statSpendHour1));
row.Add(Convert.ToString(statOstHour1));
row.Add(Convert.ToString(statOstDay1));
Mas2.Add(row);
count++;
for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
{
row = new List<string>();
for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j
Mas2.Add(row); //строка добавляется в массив
count++;
}
for (int i = 0; i<count; i++)
{
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 1];
RangeExcel.Value = Mas2[i][0];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 2];
RangeExcel.Value = Mas2[i][1];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 3];
RangeExcel.Value = Mas2[i][2];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 4];
RangeExcel.Value = Mas2[i][3];
RangeExcel = (Excel.Range)WorkSheetExcel.Cells[i + 2, 5];
RangeExcel.Value = Mas2[i][4];
}
WorkBookExcel.Close(true, Type.Missing, Type.Missing);
ExcelApp.Quit();
GC.Collect();
this.Close();
}
private static void SynctoExcel(string excel, string json)
{
List<Sheets> myMessage = JsonConvert.DeserializeObject<List<Sheets>>(File.ReadAllText(@FileLocation));
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(@FileLocation);
MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"];
//
foreach (var file in myMessage)
{
foreach (Rows r in file.Rows)
{
var snumber = r.SheetLine;
MySheet.Cells[snumber, 8] = r.RMuri;
MySheet.Cells[snumber, 9] = r.OriginalFileCount;
MySheet.Cells[snumber, 10] = r.RMFileCount;
}
}
MyBook.Save();
MyBook.Close(null, null, null);
MyApp.Quit();
}
private void LoadExcelFile()
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
opisLabel.Text += "\nOtwieranie pliku excel...";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
if (rowCount > 5 && colCount > 2)
{
opisLabel.Text += "\nOdczytywanie informacji nagłówkowych...";
object[,] values = (object[, ])xlRange.Value2;
opisLabel.Text += "\nPrzetwarzanie danych nagłówkowych...";
DBRepository db = new DBRepository();
progressBar.Maximum = rowCount;
progressBar.Value = 0;
opisLabel.Text += "\nOdczytywanie " + (rowCount - 5) + " wierszy danych...";
for (int i = 6; i <= rowCount; i++)
{
String nazwaSklepu = values[i, 1].ToString();
String nazwaEmail = values[i, 2].ToString();
String email = values[i, 3].ToString();
String idSklepu = "";
String result = "";
if (db.SklepImport_AddSklep(nazwaSklepu, ref idSklepu, ref result))
{
if (db.SklepImport_AddMail(idSklepu, nazwaEmail, email, ref result))
{
czyDodano = true;
}
else
{
MessageBox.Show("Wystąpił błąd podczas dodawania adresu email '" + email + "' dla sklepu '" + nazwaSklepu + "'" + nazwaSklepu + "':\n" + result, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Wystąpił błąd podczas dodawania sklepu '" + nazwaSklepu + "':\n" + result, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
progressBar.Value++;
}
}
else
{
MessageBox.Show("Wczytywany plik ma 0 wierszy lub 0 kolumn.", "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
opisLabel.Text += "\nZamykanie pliku excel...";
xlWorkbook.Close(false, null, null);
xlApp.Quit();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
private static void ProcessClassFolders()
{
string iRow = null;
try
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(@FileLocation);
MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"];
MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"];
Console.WriteLine("Connected to Spread Sheet");
//Console.ReadLine();
Console.WriteLine("Processing, please wait. If its a large SS then go make a coffee.");
ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value);
ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value);
string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null;
bool createnew = false;
long fileconturi = 0, recuri = 0;
//EmpList.Add(emp);
List<Sheets> sheets = new List<Sheets>();
Sheets sheet = new Sheets();
sheet.SheetName = FileLocation;
List<Rows> rows = new List<Rows>();
Rows r = new Rows();
for (var i = 2; i < ProcesstoRow + 1; i++)
{
if(i==350)
{
var dsd = "sfdsf";
}
iRow = i.ToString();
//First child level
bool bLevel3New = false;
createnew = false;
if (MySheet.Cells[i, 1].Value2 != null)
{
str1 = MySheet.Cells[i, 1].Value.ToString();
createnew = true;
}
if (MySheet.Cells[i, 2].Value2 != null)
{
str2 = MySheet.Cells[i, 2].Value.ToString();
createnew = true;
}
if (MySheet.Cells[i, 3].Value2 != null)
{
str3 = MySheet.Cells[i, 3].Value.ToString();
createnew=true;
bLevel3New = true;
}
if(createnew)
{
r = new Rows();
r.level1 = str1;
r.level2 = str2;
r.level3 = str3;
r.SheetLine = i;
rows.Add(r);
}
// New
if (MySheet.Cells[i, 4].Value2 != null)
{
if (bLevel3New == true)
{
List<Folder> lstFolder = new List<Folder>();
rows.LastOrDefault().Folders = lstFolder;
}
str4 = MySheet.Cells[i, 4].Value.ToString();
//int lvl4LoopCount = 0;
//Check to see if the group identifyer is not blank and has a g
if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g")
{
Console.WriteLine("Grouped string found in level 4");
List<string> lstLvl4 = str4.Split('/').ToList();
foreach (var kkk in lstLvl4.Where(x => x.Length > 2))
{
Folder fol = new Folder();
fol.folder = kkk.Trim();
fol.SheetLine = i;
rows.LastOrDefault().Folders.Add(fol);
//lstFolder.Add(fol);
fileconturi = recuri;
if (MySheet.Cells[i, 5].Value2 != null)
{
str5 = MySheet.Cells[i, 5].Value.ToString();
List<Subfolder> lstsubfolder = new List<Subfolder>();
if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
{
List<string> lstLvl5 = str5.Split('/').ToList();
foreach (var lll in lstLvl5.Where(x => x.Length > 2))
{
Subfolder sf = new Subfolder();
sf.subfolder = lll.Trim();
sf.SheetLine = i;
lstsubfolder.Add(sf);
}
fol.SubFolders = lstsubfolder;
}
else
{
Subfolder sf = new Subfolder();
sf.subfolder = str5.Trim();
sf.SheetLine = i;
lstsubfolder.Add(sf);
fol.SubFolders = lstsubfolder;
}
}
}
}
else
{
Folder fol = new Folder();
fol.folder = str4;
fol.SheetLine = i;
fileconturi = recuri;
if (MySheet.Cells[i, 5].Value2 != null)
{
List<Subfolder> lstsf = new List<Subfolder>();
str5 = MySheet.Cells[i, 5].Value.ToString();
//r.Subfolder = str5;
if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
{
List<string> lstLvl5 = str5.Split('/').ToList();
foreach (var lll in lstLvl5.Where(x => x.Length > 2))
{
Subfolder sf = new Subfolder();
sf.subfolder = lll.Trim();
sf.SheetLine = i;
lstsf.Add(sf);
}
}
else
{
Subfolder sf = new Subfolder();
sf.subfolder = str5;
sf.SheetLine = i;
if (MySheet.Cells[i, 7].value2 != null)
{
sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
lstsf.Add(sf);
}
fol.SubFolders = lstsf;
}
else
{
if (MySheet.Cells[i, 7].value2 != null)
{
fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
}
rows.LastOrDefault().Folders.Add(fol);
}
}
else
{
if (MySheet.Cells[i, 5].Value2 != null)
{
if (rows.LastOrDefault().Folders.LastOrDefault().SubFolders==null)
{
List<Subfolder> lstsf = new List<Subfolder>();
rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsf;
}
str5 = MySheet.Cells[i, 5].Value.ToString();
if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
{
List<string> lstLvl5 = str5.Split('/').ToList();
foreach (var lll in lstLvl5.Where(x => x.Length > 2))
{
Subfolder sf = new Subfolder();
sf.subfolder = lll.Trim();
sf.SheetLine = i;
if (MySheet.Cells[i, 7].value2 != null)
{
sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf);
}
}
else
{
Subfolder sf = new Subfolder();
sf.subfolder = str5.Trim();
sf.SheetLine = i;
if (MySheet.Cells[i, 7].value2 != null)
{
sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
//lstsubfolder.Add(sf);
//fol.SubFolders = lstsubfolder;
//rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsubfolder;
rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf);
}
}
}
//End New
}
sheet.Rows = rows;
sheets.Add(sheet);
//
string json = JsonConvert.SerializeObject(sheets.ToArray());
//FileLocation
var loc = Path.GetDirectoryName(FileLocation);
System.IO.File.WriteAllText(@loc + "\\SSprocessTIP.json", json);
//
MyBook.Save();
MyBook.Close(null, null, null);
MyApp.Quit();
Console.WriteLine("Successful in building Json file.");
Repeatmenu();
//}
}
catch (Exception exc)
{
MyBook.Save();
MyBook.Close(null, null, null);
MyApp.Quit();
//
Console.WriteLine("Error: " + exc.Message.ToString()+" - "+iRow);
return;
}
}
public static void Main(string[] args)
{
if (args == null)
{
throw new ArgumentNullException(nameof(args));
}
var xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = null;
try
{
if (args.Length != 2)
{
Console.WriteLine("usage ExcelToXml <path to xslx> <output path>");
return;
}
if (!System.IO.File.Exists(args[0]))
{
Console.WriteLine("File " + args[0] + " does not exist.");
return;
}
if (!System.IO.Directory.Exists(args[1]))
{
Console.WriteLine("The directory " + args[1] + " does not exist.");
return;
}
xlWorkbook = xlApp.Workbooks.Open(args[0]);;
Console.WriteLine("Opening file " + args[0] + " for input...");
var x = xlWorkbook.Worksheets.Count;
Excel.Worksheet activeSheet = xlWorkbook.ActiveSheet;
var count = activeSheet.Rows.Count;
// Get headers for from the spreadsheet.
var headers = GetHeaders(activeSheet);
for (int i = 2; i < count; i++)
{
var map = CreateRowMapping(activeSheet, headers, i, out var moreRows);
if (!moreRows)
{
break;
}
BuildXml(map, args[1], i);
}
}
finally
{
Console.WriteLine("Preparing to shutdown Excel");
xlWorkbook?.Close();
xlApp.Quit();
Console.WriteLine("Excel should be shut down");
}
}
private static void processFolders()
{
try
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(@FileLocation);
MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"];
MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"];
Console.WriteLine("Connected to Spread Sheet");
Console.ReadLine();
ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value);
ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value);
string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null;
bool createnew = false;
long fileconturi = 0, recuri = 0;
int lvl1Value = 10, lvl2Value = 10;
//EmpList.Add(emp);
List<Sheets> sheets = new List<Sheets>();
Sheets sheet = new Sheets();
sheet.SheetName = FileLocation;
List<Rows> rows = new List<Rows>();
Rows r = new Rows();
for (var i = 2; i < ProcesstoRow+1; i++)
{
//First child level
if (MySheet.Cells[i, y].Value2 != null)
{
str1 = MySheet.Cells[i, y].Value.ToString();
createnew = true;
lvl1Value = lvl1Value + 10;
//lvl2Value = 10;
//lvl3Value = 10;
y = y + 1;
}
else
{
y = y + 1;
}
if (MySheet.Cells[i, y].Value2 != null)
{
str2 = MySheet.Cells[i, y].Value.ToString();
if (createnew)
{
lvl2Value = lvl2Value + 10;
//lvl3Value = 10;
}
y = y + 1;
}
else
{
y = y + 1;
}
if (MySheet.Cells[i, y].Value2 != null)
{
str3 = MySheet.Cells[i, y].Value.ToString();
if (createnew)
{
r = new Rows();
r.level1 = str1;
r.level2 = str2;
r.level3 = str3;
}
y = y + 1;
}
else
{
y = y + 1;
r = rows.LastOrDefault();
}
List<Folder> lstFolder = new List<Folder>();
if (MySheet.Cells[i, 4].Value2 != null)
{
str4 = MySheet.Cells[i, 4].Value.ToString();
//Check to see if the group identifyer is not blank and has a g
if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g")
{
Console.WriteLine("Grouped string found in level 4");
List<string> lstLvl4 = str4.Split('/').ToList();
foreach (var kkk in lstLvl4.Where(x => x.Length > 2))
{
Folder fol = new Folder();
fol.folder = kkk.Trim();
lstFolder.Add(fol);
fileconturi = recuri;
if (MySheet.Cells[i, 5].Value2 != null)
{
str5 = MySheet.Cells[i, 5].Value.ToString();
List<Subfolder> lstsubfolder = new List<Subfolder>();
if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
{
List<string> lstLvl5 = str5.Split('/').ToList();
foreach (var lll in lstLvl5.Where(x => x.Length > 2))
{
Subfolder sf = new Subfolder();
sf.subfolder = lll.Trim();
lstsubfolder.Add(sf);
}
fol.SubFolders = lstsubfolder;
}
else
{
Subfolder sf = new Subfolder();
sf.subfolder = str5.Trim();
lstsubfolder.Add(sf);
fol.SubFolders = lstsubfolder;
}
}
}
}
else
{
Folder fol = new Folder();
fol.folder = str4;
fileconturi = recuri;
if (MySheet.Cells[i, 5].Value2 != null)
{
List<Subfolder> lstsf = new List<Subfolder>();
str5 = MySheet.Cells[i, 5].Value.ToString();
//r.Subfolder = str5;
if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
{
List<string> lstLvl5 = str5.Split('/').ToList();
foreach (var lll in lstLvl5.Where(x => x.Length > 2))
{
Subfolder sf = new Subfolder();
sf.subfolder = lll.Trim();
lstsf.Add(sf);
//recsuburi = CreateNewRMSubFolder(recuri, lll.Trim(), db, i);
}
}
else
{
Subfolder sf = new Subfolder();
sf.subfolder = str5;
if (MySheet.Cells[i, 7].value2 != null)
{
sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
lstsf.Add(sf);
//fol.SubFolders=lstsf;
//
//recsuburi = CreateNewRMSubFolder(recuri, str5, db, i);
}
fol.SubFolders = lstsf;
}
else
{
if (MySheet.Cells[i, 7].value2 != null)
{
fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
}
}
lstFolder.Add(fol);
}
r.Folders = lstFolder;
}
y = 1;
if (str4 != null)
{
MySheet.Cells[i, 8] = fileconturi.ToString();
}
else
{
//Remove for basic testing
//long Newuri = CreateNewRMfolder(str1, str2, str3, str4, str5, str3Title);
//
//Console.WriteLine("New folder created (Uri: " + fileconturi.ToString() + ")");
}
r.SheetLine = i;
//7
//r.RMuri = fileconturi.ToString();
//r.Timestamp = DateTime.Now;
rows.Add(r);
}
sheet.Rows = rows;
sheets.Add(sheet);
//
string json = JsonConvert.SerializeObject(sheets.ToArray());
//FileLocation
var loc = Path.GetDirectoryName(FileLocation);
System.IO.File.WriteAllText(@loc+"\\SSprocessTIP.json", json);
//
MyBook.Save();
MyBook.Close(null, null, null);
MyApp.Quit();
//}
}
catch (Exception exc)
{
MyBook.Save();
MyBook.Close(null, null, null);
MyApp.Quit();
//
Console.WriteLine("Error: "+exc.Message.ToString());
return;
}
}
/// <summary>
/// 方法，导出DataTable中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name="dt">DataTable</param>
/// <param name="strOutFileName">设定导出的文件名称</param>
/// <param name="strsHeadName">导出的列的的HeadName, 如果为null,则导出全部列</param>
/// <returns></returns>
public static bool SaveDtToExcel ( DataTable dt , string strOutFileName , string [] strsHeadName , string [] strsFldName, string strDecimalFormat="" )
{
int [] nsLineNo = null;
if ( dt.Rows.Count <= 0 )
{
MessageBox.Show ( "没有可以导出的数据！" , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog ();
//默然文件后缀
dlg.FileName = strOutFileName;
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory ();
//打开保存对话框
if ( dlg.ShowDialog () == DialogResult.Cancel )
return false;
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if ( fileNameString.Trim () == " " )
return false;
//定义表格内数据的行数和列数
int rowscount = dt.Rows.Count;
int colscount = dt.Columns.Count;
//行数必须大于0
if ( rowscount <= 0 )
{
MessageBox.Show ( "没有数据可供保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
//列数必须大于0
if ( colscount <= 0 )
{
MessageBox.Show ( "没有数据可供保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
//行数不可以大于65536
if ( rowscount > 65536 )
{
MessageBox.Show ( "数据记录数太多(最多不能超过65536条)，不能保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
//列数不可以大于255
if ( colscount > 255 )
{
MessageBox.Show ( "数据记录行数太多，不能保存 " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo ( fileNameString );
if ( file.Exists )
{
try
{
file.Delete ();
}
catch ( Exception error )
{
MessageBox.Show ( error.Message , "删除失败 " , MessageBoxButtons.OK , MessageBoxIcon.Warning );
return false;
}
}
#endregion
#region
//取得要导出的列的HeadName.
//如果没有指定特定的列，则默认转换 可见的 并且 有HeadName 的列。（控件列不用导出，控件列一般没有HeadName）
if ( strsHeadName == null )
{
MessageBox.Show ( "没有指定字段名称！" , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return false;
}
//取得所有要执行的行，（可能用户并不是要导出所有行，而是通过checkbox选择要导出的行）
//如果没有指定特定的行，则默认转出全部的行
if ( nsLineNo == null )
{
nsLineNo = new int [dt.Rows.Count];
for ( int row = 0 ; row <= dt.Rows.Count - 1 ; row++ )
{
nsLineNo [row] = row;
}
}
#endregion
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application ();
objWorkbook = objExcel.Workbooks.Add ( Missing.Value );
objsheet = (Excel.Worksheet) objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
Excel.Range rag = objsheet.get_Range ( objsheet.Cells [1 , 1] , objsheet.Cells [1 , strsHeadName.Length] );
rag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
int displayColumnsCount = 1;
foreach ( string strHeadName in strsHeadName )
{
objExcel.Cells [1 , displayColumnsCount] = strHeadName;
displayColumnsCount++;
}
//设置进度条
//tempProgressBar.Refresh();
//tempProgressBar.Visible = true;
//tempProgressBar.Minimum=1;
//tempProgressBar.Maximum=dgv.RowCount;
//tempProgressBar.Step=1;
int nRow1 = 2 , nRow2 = nRow1 + nsLineNo.Length;
int nColSel = 1;
DataColumn col ;
foreach ( string strFldName in strsFldName )
{
rag = objsheet.get_Range ( objsheet.Cells [nRow1 , nColSel] , objsheet.Cells [nRow2 , nColSel] );
col = dt.Columns[strFldName];
if (col == null)
{
string strErr = String.Format("数据库表列名称'{0}'不存在!",strFldName);
FF.Ctrl.MsgBox.ShowWarn(strErr);
return false ;
}
switch ( col.DataType.ToString () )
{
case "System.String":
rag.NumberFormatLocal = "@";
break;
case "System.DateTime":
rag.NumberFormatLocal = "yyyy-MM-dd HH:mm" ;
break;
case "System.Decimal":
rag.NumberFormatLocal = String.IsNullOrEmpty( strDecimalFormat ) ? "0.00" : strDecimalFormat ; //默认是"0.00";
rag.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
break;
//可以根据自己的需要扩展。
default:
//rag.NumberFormatLocal = "G/通用格式";
break;
}
nColSel ++;
}
//向Excel中逐行逐列写入表格中的数据
int nCol , row;
int nExcelCol , nExcelRow = 2; //Execl的行列都从1开始起算。第一行是列名，数据从第二行开始写入。
for ( int i = 0 ; i <= nsLineNo.Length - 1 ; i++ )
{
//tempProgressBar.PerformStep();
row = nsLineNo [i];
nExcelCol = 1; //Execl的行列都从1开始起算。第一行是列名，数据从第二行开始写入。
foreach ( string strFldName in strsFldName )
{
try
{
//nCol = dgv.Columns [strHeadName].Index;
//objExcel.Cells [nExcelRow , nExcelCol++] = dgv.Rows [row].Cells [nCol].Value.ToString ().Trim ();
objExcel.Cells [nExcelRow , nExcelCol++] = dt.Rows [row] [strFldName].ToString ().Trim ();
}
catch ( Exception )
{
return false ;
}
}
nExcelRow++;
}
//隐藏进度条
//tempProgressBar.Visible = false;
//保存文件
objWorkbook.SaveAs ( fileNameString , Missing.Value , Missing.Value , Missing.Value , Missing.Value ,
Missing.Value , Excel.XlSaveAsAccessMode.xlShared , Missing.Value , Missing.Value , Missing.Value ,
Missing.Value , Missing.Value );
}
catch ( Exception error )
{
MessageBox.Show ( error.Message , "警告 " , MessageBoxButtons.OK , MessageBoxIcon.Warning );
return false;
}
finally
{
//关闭Excel应用
if ( objWorkbook != null ) objWorkbook.Close ( Missing.Value , Missing.Value , Missing.Value );
if ( objExcel.Workbooks != null ) objExcel.Workbooks.Close ();
if ( objExcel != null ) objExcel.Quit ();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show ( fileNameString + "\n\n导出完毕! " , "提示 " , MessageBoxButtons.OK , MessageBoxIcon.Information );
return true;
}
public void GenerateBubbleChart(double chartWidth, double chartHeight, double bubbleScaleFactor, string dataFilePath, string outputFilePath, int worksheetToOpen)
{
SetStatus("Startar Excel", 0);
Excel.Application excelApp = null;
Excel.Workbook excelWorkbook = null;
Excel.Worksheet excelWorksheet = null;
Excel.Workbook sourceDataWorkbook = null;
try
{
excelApp = new Excel.Application();
excelWorkbook = excelApp.Workbooks.Add(1);
excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
sourceDataWorkbook = excelApp.Workbooks.Open(dataFilePath);
SetStatus("Skapar nytt diagram", 1);
var bubbleChart = CreateNewBubbleChart(excelWorksheet, chartWidth, chartHeight, bubbleScaleFactor);
SetStatus("Ritar ut datapunkter", 2);
AddDataPoints(bubbleChart, sourceDataWorkbook.Sheets[worksheetToOpen]);
SetStatus("Lägger till diagramförklaring", 3);
AddLegendAndClearDummySeriesNames(bubbleChart);
SetStatus("Beräknar placering av textrutor", 4);
SpreadOutDataLabels(bubbleChart);
SetStatus("Sparar", 5);
excelWorkbook.SaveAs(Filename: outputFilePath);
Process.Start(outputFilePath);
SetStatus("Success!", 6);
}
finally
{
if (excelWorkbook != null)
{
excelWorkbook.Saved = true; // Avoid prompting user to save
excelWorkbook.Close();
}
if (sourceDataWorkbook != null)
{
sourceDataWorkbook.Saved = true; // Avoid prompting user to save
sourceDataWorkbook.Close();
}
excelApp?.Quit();
if (excelWorksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
}
if (excelWorkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
}
if (sourceDataWorkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sourceDataWorkbook);
}
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
}
