public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath)
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Your file is saved" + SaveFilePath);
}
public void reporttoexcel_turnover(List<string> station_name_list, List<int> station_turnover_list, List<int> station_avg_list)
{
Eapp.Visible = true;
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
book = Eapp.Workbooks.Open(path + @"\отчет_станций_шаблон.xlsx");
excel.Worksheet sheet = (excel.Worksheet)book.Worksheets.get_Item(1);
excel.Range range_sheet = sheet.UsedRange;
for (int i = 2; i < station_name_list.Count + 2; i++)
{
excel.Range range_cur = range_sheet.Cells[i, 1];
range_cur.Value2 = station_name_list[i - 2];
range_cur = range_sheet.Cells[i, 2];
range_cur.Value2 = station_turnover_list[i - 2];
range_cur = range_sheet.Cells[i, 3];
range_cur.Value2 = station_avg_list[i - 2];
}
book.SaveAs(path + @"\reports\отчет_станций.xlsx");
Eapp.Quit();
}
/// <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();
}
}
//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();
}
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 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;
}
/// <summary>
/// 复制最新母表，将翻译完的Excel文件内容与之合并，并将合并结果报告写入新建的Excel文件中
/// </summary>
public static bool ExportMergedExcelFile(string mergedExcelSavePath, string reportExcelSavePath, LangExcelInfo langExcelInfo, LangExcelInfo translatedLangExcelInfo, List <string> mergeLanguageNames, out string errorString)
{
int languageCount = mergeLanguageNames.Count;
// 记录合并翻译时发现的新版母表与翻译完的Excel文件中Key相同但主语言翻译不同信息
List <MergedResultDifferentDefaultLanguageInfo> differentDefaultLanguageInfo = new List <MergedResultDifferentDefaultLanguageInfo>();
// 记录合并翻译时发现的新版母表与翻译完的Excel文件中Key不同信息
List <MergedResultDifferentKeyInfo> differentKeyInfo = new List <MergedResultDifferentKeyInfo>();
// 记录各个报告部分起始行行号
List <int> partStartRowIndexList = new List <int>();
partStartRowIndexList.Add(1);
// 复制新版母表
FileState fileState = Utils.GetFileState(AppValues.ExcelFullPath);
if (fileState == FileState.Inexist)
{
errorString = string.Format("新版母表所在路径（{0}）已不存在，请勿在使用本工具过程中对母表文件进行操作，合并操作被迫中止", AppValues.ExcelFullPath);
return(false);
}
try
{
File.Copy(AppValues.ExcelFullPath, mergedExcelSavePath, true);
}
catch (Exception exception)
{
errorString = string.Format("复制新版母表（{0}）至指定路径（{1}）失败：{2}，合并操作被迫中止", AppValues.ExcelFullPath, mergedExcelSavePath, exception.Message);
return(false);
}
// 打开复制后的母表，将翻译完的Excel文件中的内容与之合并
Excel.Application mergedApplication = new Excel.Application();
// 不显示Excel窗口
mergedApplication.Visible = false;
// 不显示警告对话框
mergedApplication.DisplayAlerts = false;
// 禁止屏幕刷新
mergedApplication.ScreenUpdating = false;
// 编辑非空单元格时不进行警告提示
mergedApplication.AlertBeforeOverwriting = false;
// 打开Excel工作簿
Excel.Workbook mergedWorkbook = mergedApplication.Workbooks.Open(mergedExcelSavePath);
// 找到名为data的Sheet表
Excel.Worksheet mergedDataWorksheet = null;
int sheetCount = mergedWorkbook.Sheets.Count;
string DATA_SHEET_NAME = AppValues.EXCEL_DATA_SHEET_NAME.Replace("$", "");
for (int i = 1; i <= sheetCount; ++i)
{
Excel.Worksheet sheet = mergedWorkbook.Sheets[i] as Excel.Worksheet;
if (sheet.Name.Equals(DATA_SHEET_NAME))
{
mergedDataWorksheet = sheet;
break;
}
}
if (mergedDataWorksheet == null)
{
errorString = string.Format("新版母表（{0}）找不到Sheet名为{1}的数据表，请勿在使用本工具过程中对母表文件进行操作，导出操作被迫中止", AppValues.ExcelFullPath, DATA_SHEET_NAME);
return(false);
}
// 还要新建一张新表，保存合并报告
Excel.Application reportApplication = new Excel.Application();
reportApplication.Visible = false;
reportApplication.DisplayAlerts = false;
reportApplication.ScreenUpdating = false;
reportApplication.AlertBeforeOverwriting = false;
// 新建Excel工作簿
Excel.Workbook reportWorkbook = reportApplication.Workbooks.Add();
// 在名为合并报告的Sheet表中填充数据
Excel.Worksheet reportWorksheet = reportWorkbook.Sheets[1] as Excel.Worksheet;
reportWorksheet.Name = "合并报告";
// 设置表格中所有单元格均为文本格式
reportWorksheet.Cells.NumberFormatLocal = "@";
// 报告Excel文件中依次按Key与主语言译文均相同、Key相同但主语言译文不同、母表不存在指定Key分成三部分进行报告
// 不同部分之间隔开的行数
const int SPACE_LINE_COUNT = 3;
// Key与主语言译文相同的报告部分，列依次为Key名、母表行号、翻译完的Excel文件中的行号、主语言译文、各外语译文，其中用无色背景标识两表译文相同的单元格，用绿色背景标识母表未翻译而翻译完的Excel文件中新增译文的单元格，用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）
const int ALL_SAME_KEY_COLUMN_INDEX = 1;
const int ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 3;
const int ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 4;
const int ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX = 5;
// 每个部分首行写入说明文字
reportWorksheet.Cells[1, 1] = "以下为已合并的译文报告，其中用无色背景标识两表译文相同的单元格，用绿色背景标识母表未翻译而翻译完的Excel文件中新增译文的单元格，用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）";
// 写入Key与主语言译文均相同部分的列标题说明
reportWorksheet.Cells[2, ALL_SAME_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[2, ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX] = "母表中的行号";
reportWorksheet.Cells[2, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[2, ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "主语言译文";
for (int i = 0; i < languageCount; ++i)
{
int columnIndex = ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + i;
reportWorksheet.Cells[2, columnIndex] = mergeLanguageNames[i];
}
// 当前报告Excel表中下一个可用空行的行号（从1开始计）
int nextCellLineNum = 3;
// 逐行读取翻译完的Excel表中的内容并与最新母表比较，若Key相同主语言翻译相同，直接将翻译完的Excel表中对应的外语译文合并到母表，若Key相同但主语言翻译不同或者翻译完的Excel表中存在母表中已没有的Key则不合并且记入报告
int translatedExcelDataCount = translatedLangExcelInfo.Keys.Count;
for (int i = 0; i < translatedExcelDataCount; ++i)
{
string mergedExcelKey = translatedLangExcelInfo.Keys[i];
if (mergedExcelKey == null)
{
continue;
}
// 判断母表中是否存在指定Key
if (langExcelInfo.Keys.Contains(mergedExcelKey))
{
// 判断母表与翻译完的Excel文件中该Key对应的主语言译文是否相同
// 母表中该Key所在行的数据索引
int excelDataIndex = langExcelInfo.KeyToDataIndex[mergedExcelKey];
string excelDefaultLanguageValue = langExcelInfo.DefaultLanguageInfo.Data[excelDataIndex];
string translatedExcelDefaultLanguageValue = translatedLangExcelInfo.DefaultLanguageInfo.Data[i];
if (excelDefaultLanguageValue.Equals(translatedExcelDefaultLanguageValue))
{
// 如果该行外语的翻译均相同，则无需合并且不需要记入报告
bool isAllSame = true;
foreach (string languageName in mergeLanguageNames)
{
string excelLanguageValue = langExcelInfo.OtherLanguageInfo[languageName].Data[excelDataIndex];
string translatedLanguageValue = translatedLangExcelInfo.OtherLanguageInfo[languageName].Data[i];
if (!excelLanguageValue.Equals(translatedLanguageValue))
{
isAllSame = false;
break;
}
}
// 存在不同的译文，则要合并到母表中并记入报告
if (isAllSame == false)
{
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_KEY_COLUMN_INDEX] = mergedExcelKey;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX] = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = i + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = i + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = translatedExcelDefaultLanguageValue;
for (int j = 0; j < languageCount; ++j)
{
string languageName = mergeLanguageNames[j];
string excelLanguageValue = langExcelInfo.OtherLanguageInfo[languageName].Data[excelDataIndex];
string translatedLanguageValue = translatedLangExcelInfo.OtherLanguageInfo[languageName].Data[i];
int columnIndex = ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + j;
// 报告中外语列单元格都要写入翻译完的Excel文件中对应的译文
reportWorksheet.Cells[nextCellLineNum, columnIndex] = translatedLanguageValue;
if (!excelLanguageValue.Equals(translatedLanguageValue))
{
int mergedExcelRowIndex = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
int mergedExcelColumnIndex = langExcelInfo.OtherLanguageInfo[languageName].ColumnIndex;
if (string.IsNullOrEmpty(excelLanguageValue))
{
// 母表中原来没有译文，则将报告Excel表中对应单元格背景色设为绿色
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).Interior.ColorIndex = 4;
}
else
{
// 母表中和翻译完的Excel表中译文不同，则用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).Interior.ColorIndex = 6;
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).AddComment(string.Concat("母表中旧的译文：", System.Environment.NewLine, excelLanguageValue));
}
// 将翻译完的Excel表中的译文写入母表
mergedDataWorksheet.Cells[mergedExcelRowIndex, mergedExcelColumnIndex] = translatedLanguageValue;
}
}
++nextCellLineNum;
}
}
else
{
// Key相同，主语言译文不同则不合并且记入报告
MergedResultDifferentDefaultLanguageInfo info = new MergedResultDifferentDefaultLanguageInfo();
info.ExcelLineNum = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
info.TranslatedExcelLineNum = i + AppValues.EXCEL_DATA_START_INDEX;
info.Key = mergedExcelKey;
info.ExcelDefaultLanguageValue = excelDefaultLanguageValue;
info.TranslatedExcelDefaultLanguageValue = translatedExcelDefaultLanguageValue;
differentDefaultLanguageInfo.Add(info);
}
}
else
{
// 翻译完的Excel表中存在母表中已没有的Key则不合并且记入报告
MergedResultDifferentKeyInfo info = new MergedResultDifferentKeyInfo();
info.TranslatedExcelLineNum = i + AppValues.EXCEL_DATA_START_INDEX;
info.Key = mergedExcelKey;
info.TranslatedExcelDefaultLanguageValue = translatedLangExcelInfo.DefaultLanguageInfo.Data[i];
differentKeyInfo.Add(info);
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, 1, nextCellLineNum - 1, ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + languageCount - 1);
// Key相同但主语言译文不同的报告部分，列依次为Key名、母表行号、翻译完的Excel文件中的行号、母表中主语言译文、翻译完的Excel文件中主语言译文
const int KEY_SAME_KEY_COLUMN_INDEX = 1;
const int KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 3;
const int KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 4;
const int KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 5;
if (differentDefaultLanguageInfo.Count > 0)
{
nextCellLineNum = nextCellLineNum + SPACE_LINE_COUNT;
partStartRowIndexList.Add(nextCellLineNum);
// 每个部分首行写入说明文字
reportWorksheet.Cells[nextCellLineNum, 1] = "以下为母表与翻译完的Excel表中Key相同但主语言译文不同，无法进行合并的信息";
++nextCellLineNum;
// 写入Key相同但主语言译文不同部分的列标题说明
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX] = "母表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "母表中主语言译文";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "翻译完的Excel文件中主语言译文";
++nextCellLineNum;
// 将所有Key相同但主语言译文不同信息写入报告
foreach (MergedResultDifferentDefaultLanguageInfo info in differentDefaultLanguageInfo)
{
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_KEY_COLUMN_INDEX] = info.Key;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX] = info.ExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = info.TranslatedExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.ExcelDefaultLanguageValue;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.TranslatedExcelDefaultLanguageValue;
++nextCellLineNum;
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, partStartRowIndexList[1], nextCellLineNum - 1, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX);
// 母表不存在指定Key的报告部分，列依次为Key名、翻译完的Excel文件中的行号以及主语言译文
const int KEY_DIFFERENT_KEY_COLUMN_INDEX = 1;
const int KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 3;
if (differentKeyInfo.Count > 0)
{
nextCellLineNum = nextCellLineNum + SPACE_LINE_COUNT;
partStartRowIndexList.Add(nextCellLineNum);
// 每个部分首行写入说明文字
reportWorksheet.Cells[nextCellLineNum, 1] = "以下为翻译完的Excel文件含有但母表已经没有的Key，无法进行合并的信息";
++nextCellLineNum;
// 写入母表中已没有的Key部分的列标题说明
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "翻译完的Excel文件中主语言译文";
++nextCellLineNum;
// 将所有母表中已没有的Key信息写入报告
foreach (MergedResultDifferentKeyInfo info in differentKeyInfo)
{
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_KEY_COLUMN_INDEX] = info.Key;
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = info.TranslatedExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.TranslatedExcelDefaultLanguageValue;
++nextCellLineNum;
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, partStartRowIndexList[2], nextCellLineNum - 1, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX);
// 美化生成的Excel文件
_BeautifyExcelWorksheet(reportWorksheet, 40, nextCellLineNum - 1);
// 因为Excel中执行过合并的单元格即便设置了自动换行也无法实现效果，故为了防止每个部分首行的描述文字不完全可见，手工修改其行高
for (int i = 0; i < partStartRowIndexList.Count; ++i)
{
int partStartRowIndex = partStartRowIndexList[i];
reportWorksheet.get_Range("A" + partStartRowIndex).EntireRow.RowHeight = 80;
}
// 保存报告Excel文件
reportWorksheet.SaveAs(reportExcelSavePath);
reportWorkbook.SaveAs(reportExcelSavePath);
// 关闭Excel
reportWorkbook.Close(false);
reportApplication.Workbooks.Close();
reportApplication.Quit();
Utils.KillExcelProcess(reportApplication);
// 保存合并后的Excel文件
mergedDataWorksheet.SaveAs(mergedExcelSavePath);
mergedWorkbook.SaveAs(mergedExcelSavePath);
// 关闭Excel
mergedWorkbook.Close(false);
mergedApplication.Workbooks.Close();
mergedApplication.Quit();
Utils.KillExcelProcess(mergedApplication);
errorString = null;
return(true);
}
public void SaveAndQuiteExcel(string path)
{
MyBook.SaveAs(path);
QuiteBook();
QuiteExcel();
}
public static void ExportToExcel(DataSet dataSet, string outputPath)
{
// Create the Excel Application object
// ApplicationClass excelApp = new ApplicationClass();
Excel.Application excelApp;
excelApp = new Excel.Application();
// Create a new Excel Workbook
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
// Copy each DataTable
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
// Mark the first row as BOLD
//((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
// Save and Close the Workbook
excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}
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 btnExcel_Click(object sender, EventArgs e)
{
if ( !timer1.Enabled )
btnStart_Click(sender, e);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlFunction = xlApp.WorksheetFunction;
xlWorkSheetData = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheetData.Name = "Pendulum";
xlWorkSheetData.Activate();
xlWorkSheetData.Cells[1, 1] = "Constants used, mass: " + tbm.Text + ", gravity: " + tbG.Text + ", Spring Constant: " + tbk.Text + ", Length: " + tbH.Text;
xlRng = xlWorkSheetData.get_Range("A1", "N1");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[2, 1] = "Initial Values used, Intial X: " + tbXi.Text + ", Initial Y: " + tbYi.Text + ", Initial X Velocity: " + vx0.Text + ", Initial Y Velocity: " + vy0.Text;
xlRng = xlWorkSheetData.get_Range("A2", "N2");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[lastRowExcel, 1] = "t"; // changes these to whatever you want
xlWorkSheetData.Cells[lastRowExcel, 2] = "X";
xlWorkSheetData.Cells[lastRowExcel, 3] = "Y";
xlWorkSheetData.Cells[lastRowExcel, 4] = "Vx";
xlWorkSheetData.Cells[lastRowExcel, 5] = "Vy";
lblTransfer.Visible = true;
for (int i = 0; i < excelData.Count; i++)
{
xlWorkSheetData.Cells[i + 4, 1] = (excelData[i].time / 1000.00).ToString();
xlWorkSheetData.Cells[i + 4, 2] = excelData[i].x.ToString();
xlWorkSheetData.Cells[i + 4, 3] = excelData[i].y.ToString();
xlWorkSheetData.Cells[i + 4, 4] = excelData[i].vx.ToString();
xlWorkSheetData.Cells[i + 4, 5] = excelData[i].vy.ToString();
}
lblTransfer.Visible = false;
try //essaye le sauvegarde
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//sauvegarde le classeur courant
xlWorkBook.SaveAs(saveFileDialog1.FileName,
Excel.XlFileFormat.xlWorkbookDefault, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue,
misValue, misValue, misValue);
xlWorkBook.Close();
}
}
catch //en cas d'erreur affiche le message
{
MessageBox.Show("Impossible de sauvegarder le fichier.", "Erreur de sauvegarde de fichier Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public void DataGridViewToExcel3(DataGridView dgv)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#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中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//设置进度条
//tempProgressBar.Refresh();
//tempProgressBar.Visible = true;
//tempProgressBar.Minimum=1;
//tempProgressBar.Maximum=dgv.RowCount;
//tempProgressBar.Step=1;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//隐藏进度条
//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;
}
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);
}
public static void ExportExcelByMicrosoft(DataGridView dgv, string title, string path, int colBegin)
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Lỗi không thể sử dụng được thư viện EXCEL");
return;
}
xlApp.Visible = false;
object misValue = System.Reflection.Missing.Value;
Excel.Workbook wb = xlApp.Workbooks.Add(misValue);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
if (ws == null)
{
MessageBox.Show("Không thể tạo được WorkSheet");
return;
}
int row = 1;
string fontName = "Times New Roman";
int fontSizeTieuDe = 18;
int fontSizeTenTruong = 14;
int fontSizeNoiDung = 12;
Excel.Range rangetitle = ws.get_Range("A1", "L1");
rangetitle.Merge();
rangetitle.Font.Bold = true;
rangetitle.Font.Size = fontSizeTieuDe;
rangetitle.Font.Name = fontName;
rangetitle.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rangetitle.Value2 = title;
Excel.Range titleTable;
int col = colBegin;
for (int i = 0; i < dgv.Columns.Count; i++)
{
// char Cot = char.Parse((KyTu++).ToString());
if (dgv.Columns[i].Visible == true)
{
KeysConverter key = new KeysConverter();
String s = key.ConvertFromString((col++).ToString()).ToString();
// MessageBox.Show(s);
titleTable = ws.get_Range(s + "2", s + "2");
titleTable.Font.Size = fontSizeTenTruong;
titleTable.Font.Name = fontName;
titleTable.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
titleTable.Value2 = dgv.Columns[i].HeaderText;
titleTable.ColumnWidth = dgv.Columns[i].HeaderText.Length + 5;
}
}
titleTable = ws.get_Range("A2", "L2");
titleTable.Interior.Color = Color.FromArgb(0xCC, 0xCC, 0xFF);
//Để ghi nội dung
int soDongCanGhi = dgv.RowCount + 3;
Excel.Range contentTable;
for (int i = 3; i < soDongCanGhi; i++)
{
col = colBegin;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true)
{
KeysConverter key = new KeysConverter();
String s = key.ConvertFromString((col++).ToString()).ToString();
contentTable = ws.get_Range(s + i, s + i);
contentTable.Font.Size = fontSizeNoiDung;
contentTable.Font.Name = fontName;
contentTable.Value2 = dgv.Rows[i - 3].Cells[j].Value.ToString();
}
}
}
Excel.Range boderTable;
boderTable = ws.get_Range("A2", string.Format("L{0}", soDongCanGhi - 1));
BorderAround(boderTable);
//Lưu file
wb.SaveAs(path);
//đóng file để hoàn tất quá trình lưu trữ
wb.Close(true, misValue, misValue);
//thoát và thu hồi bộ nhớ cho COM
xlApp.Quit();
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
//Mở File excel sau khi Xuất thành công
System.Diagnostics.Process.Start(path);
}
/// <summary>
/// Export selected items
/// </summary>
/// <param name="sSheetName"></param>
/// <param name="sPath"></param>
/// <param name="lv">ListView with selectes items</param>
/// <returns></returns>
public static bool ExportListViewItems2Excel07(string sSheetName, string sPath, ListView lv)
{
try
{
exApp = new COMExcel.Application();
exBook = exApp.Workbooks.Add(
COMExcel.XlWBATemplate.xlWBATWorksheet);
COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
exSheet.Activate();
exSheet.Name = sSheetName;
for (int iColumn = 1; iColumn < lv.Columns.Count; iColumn++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[1, iColumn];
r.Value2 = lv.Columns[iColumn].Text.ToString();
r.Columns.AutoFit();
}
for (int iRow = 0; iRow < lv.SelectedItems.Count; iRow++)
{
for (int iColumn = 1; iColumn < lv.Columns.Count; iColumn++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[iRow + 2, iColumn];
r.Value2 = lv.SelectedItems[iRow].SubItems[iColumn].Text.ToString();
}
}
exApp.Visible = false;
exBook.SaveAs(sPath, COMExcel.XlFileFormat.xlWorkbookNormal,
null, null, false, false,
COMExcel.XlSaveAsAccessMode.xlExclusive,
false, false, false, false, false);
exBook.Close(false, false, false);
exApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
return true;
}
catch
{
CloseExcel();
return false;
}
}
//string idBill, string idUser, string idCustomer, DateTime time, long subtractMoney, long totalMoney)
public static bool ExportListViews2Excel(string sSheetName, string sPath, List<ListView> lv)
{
try
{
// Khởi động chtr Excell
exApp = new COMExcel.Application();
// Thêm file temp xls
exBook = exApp.Workbooks.Add(
COMExcel.XlWBATemplate.xlWBATWorksheet);
// Lấy sheet 1.
COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
exSheet.Activate();
exSheet.Name = sSheetName;
List<int> list_iMaxLength = new List<int>(); //Gia tri max de so sanh AutoFit column
int iRowFit = 1;
int iColumnFit = 1;
bool isNewMaxLength = true;
int rowIndex = 0;
foreach (ListView listView in lv)
{
int[] listOldMaxLength = new int[list_iMaxLength.Count];
list_iMaxLength.CopyTo(listOldMaxLength);
list_iMaxLength.Clear();
for (int iColumn = 0; iColumn < listView.Columns.Count; iColumn++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[rowIndex + 1, iColumn + 1];
r.Font.Bold = true;
r.Value2 = listView.Columns[iColumn].Text.ToString();
//r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);
if (iColumn < listOldMaxLength.Length && listView.Columns[iColumn].Text.Length < listOldMaxLength[iColumn])
{
list_iMaxLength.Add(listOldMaxLength[iColumn]);
}
else
{
list_iMaxLength.Add(listView.Columns[iColumn].Text.Length);
COMExcel.Range rFit = (COMExcel.Range)exSheet.Cells[rowIndex + 1, iColumn + 1];
rFit.Columns.AutoFit();
}
}
//rowIndex += 1;
for (int iColumn = 0; iColumn < listView.Columns.Count; iColumn++)
{
iRowFit = rowIndex + 1;
iColumnFit = iColumn + 1;
for (int iRow = rowIndex; iRow < listView.Items.Count + rowIndex; iRow++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[iRow + 2, iColumn + 1];
r.Value2 = listView.Items[iRow - rowIndex].SubItems[iColumn].Text.ToString();
//r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);
if (listView.Items[iRow - rowIndex].SubItems[iColumn].Text.Length > list_iMaxLength[iColumn])
{
list_iMaxLength[iColumn] = listView.Items[iRow - rowIndex].SubItems[iColumn].Text.Length;
iRowFit = iRow + 2;
iColumnFit = iColumn + 1;
isNewMaxLength = true;
}
}
if (isNewMaxLength)
{
COMExcel.Range rFit = (COMExcel.Range)exSheet.Cells[iRowFit, iColumnFit];
rFit.Columns.AutoFit();
isNewMaxLength = false;
}
}
rowIndex += listView.Items.Count;
rowIndex += 1;
}
exApp.Visible = false;
exBook.SaveAs(sPath, COMExcel.XlFileFormat.xlWorkbookNormal,
null, null, false, false,
COMExcel.XlSaveAsAccessMode.xlExclusive,
false, false, false, false, false);
exBook.Close(false, false, false);
exApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
return true;
}
catch
{
CloseExcel();
return false;
}
}
private void createReportButton_Click(object sender, EventArgs e)
{
if (Directory.Exists(Settings.Default.FilesDir))
{
createReportButton.Enabled = false;
// Получаем список файлов в каталоге
string[] filesArray = Directory.GetFiles(Settings.Default.FilesDir);
foreach (string fileName in filesArray)
{
// Проверяем расширение файла
FileInfo infoFile = new FileInfo(fileName);
// Совпадает с датами формата: 1900-01-01 2007/08/13 1900.01.01 1900 01 01 1900-01.01
//string fileNamePattern = @"(19|20)\d\d([- /.])(0[1-9]|1[012])([- /.])(0[1-9]|[12][0-9]|3[01])";
string month;
if (dateTimePicker1.Value.Month <= 9) { month = "0" + dateTimePicker1.Value.Month; }
else { month = dateTimePicker1.Value.Month.ToString(); };
string fileNamePattern;
if (checkBox1.Checked)
{
fileNamePattern = "(" + dateTimePicker1.Value.Year + @")([- /.])("
+ month + @")([- /.])(0[1-9]|[12][0-9]|3[01])";
}
else
{
fileNamePattern = ".";
}
// Обнуляем счётчик обработаных файлов
filesCount = 0;
if ((File.Exists(fileName)) && Regex.IsMatch(fileName, fileNamePattern) && (infoFile.Extension == ".dpm"))
{
filesCount++;
// Создаём потоки чтения файлов.
FileStream fileStream = new FileStream(
fileName,
FileMode.Open,
FileAccess.Read
);
StreamReader streamReader = new StreamReader(
fileStream,
Encoding.GetEncoding(1251)
);
toolStripStatusLabel2.Text = "Обработка файла: " + infoFile.Name;
// Считываем построчно файл до самого конца
while (!streamReader.EndOfStream)
{
// Разделяем полученную строку
// Структтура массива:
// info[0] - знак выхождения или выхода трека (">" или "<")
// info[1] - дата и время выхода пести
// info[2] - идентификатор трека при составлении плейлиста
// info[3] - псевдоним, который определяет положение треков на диске
// info[4] - имя файла трека относительно псевдонима
// info[5] - длительность трека
string[] info = streamReader.ReadLine().Split('\t');
// Считаем только вхождение файла
// Т.е. проверяем на символ ">" и проверяем только определённый псевдоним
string[] aliases = Properties.Settings.Default.Aliases.Split(';');
foreach (string alias in aliases)
{
if ((info[0].Trim() == ">") && (info[3].Trim() == alias.Trim()))
{
if (!tableCount.Contains(info[4]))
{
// Если записи нет, создаём новую
tableCount.Add(info[4], 1);
tableDuration.Add(info[4], info[5]);
}
else
{
// Если запись есть, увеличиваем счётчик
tableCount[info[4]] = (int)tableCount[info[4]] + 1;
}
}
}
}
// Закрываем потоки чтения файлов.
streamReader.Close();
fileStream.Close();
} // End If File Exist
} // End Foreach FileNames
// Меняем статус
toolStripStatusLabel2.Text = "Обработано файлов: " + filesCount;
// Создаём нумератор для управление Хэш массивом
IDictionaryEnumerator tableCountEnum = tableCount.GetEnumerator();
toolStripStatusLabel2.Text = "Создание файла отчёта.";
#region СОЗДАЁМ НОВЫЙ ДОКУМЕНТ EXCEL
// Открываем приложение
excelapp = new Excel.Application();
// Прячем окно программы
excelapp.Visible = false;
// Создаём документ с одной книгой
excelapp.SheetsInNewWorkbook = 1;
// Добавляем книгу
excelapp.Workbooks.Add(Type.Missing);
//Запрашивать сохранение
//excelapp.DisplayAlerts = true;
excelapp.DisplayAlerts = false;
//Получаем набор ссылок на объекты Workbook (на созданные книги)
excelappworkbooks = excelapp.Workbooks;
//Получаем ссылку на книгу 1 - нумерация от 1
excelappworkbook = excelappworkbooks[1];
#endregion
toolStripStatusLabel2.Text = "Генерация отчёта.";
#region РАБОТА С ЯЧЕЙКАМИ
excelsheets = excelappworkbook.Worksheets;
// Получаем ссылку на лист 1
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
#region Примеры: Выделение группы ячеек
//excelcells = excelworksheet.get_Range("A1", "С10");
// Тоже
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
//excelcells = excelworksheet.get_Range("A1", "С10").Rows;
//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
// Одну ячейку
//excelcells = excelworksheet.get_Range("A1", "A1");
//excelcells = excelworksheet.get_Range("A1", Type.Missing);
// Десять строк с 1 по 10ю
//excelcells = (Excel.Range)excelworksheet.Rows["1:10", Type.Missing];
// Три столбца
//excelcells = (Excel.Range)excelworksheet.Columns["A:C", Type.Missing];
// Одну строку
//excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
// Один столбец
//excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
#endregion
// Выбираем первую ячейку
excelcells = excelworksheet.get_Range("A1", Type.Missing).Cells;
excelcells.Value2 = "Испольнитель";
excelcells = excelworksheet.get_Range("B1", Type.Missing).Cells;
excelcells.Value2 = "Трек";
excelcells = excelworksheet.get_Range("C1", Type.Missing).Cells;
excelcells.Value2 = "Длительность";
excelcells = excelworksheet.get_Range("D1", Type.Missing).Cells;
excelcells.Value2 = "Количество";
excelcells = excelworksheet.get_Range("A1", "D1").Cells;
excelcells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
#endregion
#region ЗАПИСЫВАЕМ ДАННЫЕ В ФАЙЛ
// Сбрасываем счётчик Хэша в начало
tableCountEnum.Reset();
// В цикле перебираем все записи Хэша
for (int i = 0; i < tableCount.Count; i++)
{
// Переход к следующей записи
tableCountEnum.MoveNext();
// Выделяем имя файла из пути к файлу
string trackName = Regex.Match(tableCountEnum.Key.ToString(), @"[^\\]*$").Value;
// Отрезаем расширение файла
trackName = trackName.Substring(0, trackName.Length - 4);
// Заменяем тире с пробелами на знак %
trackName = trackName.Replace("-", "%");
// Разделяем название группы и название трека
string[] fullName = trackName.Split('%');
// Работаем с документом
// Начинаем запись исполнителей со второй строки
int m = i + 2;
excelcells = (Excel.Range)excelworksheet.Cells[m, 1];
excelcells.Value2 = fullName[0].Trim();
excelcells = (Excel.Range)excelworksheet.Cells[m, 2];
if (fullName.Length > 1) {excelcells.Value2 = fullName[1].Trim(); }
excelcells = (Excel.Range)excelworksheet.Cells[m, 3];
excelcells.Value2 = tableDuration[tableCountEnum.Key];
excelcells = (Excel.Range)excelworksheet.Cells[m, 4];
excelcells.Value2 = tableCountEnum.Value.ToString();
}
#endregion
toolStripStatusLabel2.Text = "Сохранение документа.";
#region ЗАВЕРШАЕМ РАБОТУ С EXCEL
//Ссылку можно получить и так, но тогда надо знать имена книг,
//причем, после сохранения - знать расширение файла
//excelappworkbook=excelappworkbooks["Книга 1"];
//Запроса на сохранение для книги не должно быть
excelappworkbook.Saved = true;
// ФОрмат сохранения документа
excelapp.DefaultSaveFormat = Excel.XlFileFormat.xlWorkbookNormal;
// Сохраняем книгу
try
{
// Определяем имя путь сохранения файла
// Если каталог указан, проверяем его корректность и сохраняем файл
// Если каталог не указан или не существует, сохраняем отчёт в папке с программой
string saveFileName;
if (Directory.Exists(Settings.Default.ReportDir) && Settings.Default.ReportDir != "")
{
saveFileName = Settings.Default.ReportDir + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
else
{
saveFileName = Application.StartupPath + "\\Report " +
DateTime.Now.ToShortDateString() + ".xls";
}
//excelappworkbook.Save();
excelappworkbook.SaveAs(saveFileName, Excel.XlFileFormat.xlWorkbookNormal);
MessageBox.Show("Отчёт успешно сгенерирован и сохранён в файл: " + saveFileName, "Готово",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка сохранения файла отчёта.",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Закрываем приложение
excelapp.Quit();
createReportButton.Enabled = true;
}
#endregion
toolStripStatusLabel2.Text = "";
} // End if DirectoryExists
else
{
// Выводим сообщение, если каталог отчётов не указан
MessageBox.Show("Каталог с файлами отчётов не найден!",
"Ошибка открытия каталога.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static bool ExportGridViewData2Excel07(string sPath, DataGridView dgv)
{
try
{
// Khởi động chtr Excel
exApp = new COMExcel.Application();
// Thêm file temp xls
exBook = exApp.Workbooks.Add(
COMExcel.XlWBATemplate.xlWBATWorksheet);
// Lấy sheet 1.
COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
////Open excel co san
//string workbookPath = sPath;
//COMExcel.Workbook exBook = exApp.Workbooks.Open(workbookPath,
// 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
// true, false, 0, true, false, false);
//
//
exSheet.Activate();
exSheet.Name = "Export HRSTG";
//// Range là ô [1,1] (A1)
//COMExcel.Range r = (COMExcel.Range)exSheet.Cells[1, 1];
//// Ghi dữ liệu
//r.Value2 = "Demo excel value";
//// Giãn cột
//r.Columns.AutoFit();
List<int> list_iMaxLength = new List<int>(); //Gia tri max de so sanh AutoFit column
//Dong va cot de fit
int iRowFit = 1;
int iColumnFit = 1;
for (int iColumn = 0; iColumn < dgv.ColumnCount; iColumn++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[1, iColumn + 1];
r.Value2 = dgv.Columns[iColumn].HeaderText;
r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);
list_iMaxLength.Add(dgv.Columns[iColumn].HeaderText.Length);
}
for (int iColumn = 0; iColumn < dgv.ColumnCount; iColumn++)
{
iRowFit = 1;
iColumnFit = 1;
for (int iRow = 0; iRow < dgv.RowCount; iRow++)
{
COMExcel.Range r = (COMExcel.Range)exSheet.Cells[iRow + 2, iColumn + 1];
r.Value2 = dgv[iColumn, iRow].Value.ToString();
r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, 1);
//int iLength = dgv[iColumn, iRow].Value.ToString().Length;
if (dgv[iColumn, iRow].Value.ToString().Length > list_iMaxLength[iColumn])
{
list_iMaxLength[iColumn] = dgv[iColumn, iRow].Value.ToString().Length;
iRowFit = iRow + 2;
iColumnFit = iColumn + 1;
}
}
COMExcel.Range rFit = (COMExcel.Range)exSheet.Cells[iRowFit, iColumnFit];
rFit.Columns.AutoFit();
}
//// Hiển thị chương trình excel
//exApp.Visible = true;
//// Đóng chương trình excel
//Console.WriteLine("Wait to excel.exe");
//Console.ReadLine();
//exApp.Quit();
// Ẩn chương trình
exApp.Visible = false;
// Save file
exBook.SaveAs(sPath, COMExcel.XlFileFormat.xlWorkbookNormal,
null, null, false, false,
COMExcel.XlSaveAsAccessMode.xlExclusive,
false, false, false, false, false);
exBook.Close(false, false, false);
exApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
return true;
}
catch
{
CloseExcel();
return false;
}
}
void GenerarReporte()
{
if (app == null)
app = new E.Application();
app.Visible = false;
wb = app.Workbooks.Open(@rutas.RutaDeReportes + "\\facapt04.xlsx");
E.Worksheet ws = wb.Worksheets[1];
int index = 8;
for (int i = dgvLista.SelectedRows.Count - 1; i >= 0 ; i--)
{
ws.Range["A" + index].Value2 = dgvLista.SelectedRows[i].Cells[1].Value.ToString();
ws.Range["B" + index].Value2 = dgvLista.SelectedRows[i].Cells[2].Value.ToString();
ws.Range["C" + index].Value2 = dgvLista.SelectedRows[i].Cells[3].Value.ToString();
ws.Range["D" + index].Value2 = dgvLista.SelectedRows[i].Cells[4].Value.ToString();
ws.Range["E" + index].Value2 = dgvLista.SelectedRows[i].Cells[5].Value.ToString();
ws.Range["F" + index].Value2 = dgvLista.SelectedRows[i].Cells[6].Value.ToString();
ws.Range["G" + index].Value2 = dgvLista.SelectedRows[i].Cells[7].Value.ToString();
ws.Range["H" + index].Value2 = dgvLista.SelectedRows[i].Cells[8].Value.ToString();
ws.Range["I" + index].Value2 = DateTime.Parse(dgvLista.SelectedRows[i].Cells[9].Value.ToString()).ToString("dd/MM/yyyy");
if (string.IsNullOrEmpty(dgvLista.SelectedRows[i].Cells[10].Value.ToString() ) || string.IsNullOrWhiteSpace(dgvLista.SelectedRows[i].Cells[10].Value.ToString()))
ws.Range["J" + index].Value2 = string.Empty;
else
ws.Range["J" + index].Value2 = DateTime.Parse(dgvLista.SelectedRows[i].Cells[10].Value.ToString()).ToString("dd/MM/yyyy");
index++;
}
if (System.IO.File.Exists(System.IO.Path.GetTempPath() + "reporte.xlsx"))
System.IO.File.Delete(System.IO.Path.GetTempPath() + "reporte.xlsx");
if (System.IO.File.Exists(@rutas.RutaDeTemp + "/rpt_ProdEmpacado.pdf"))
System.IO.File.Delete(@rutas.RutaDeTemp + "/rpt_ProdEmpacado.pdf");
wb.SaveAs(System.IO.Path.GetTempPath() + "reporte.xlsx");
wb.ExportAsFixedFormat(E.XlFixedFormatType.xlTypePDF, @rutas.RutaDeTemp + "/rpt_ProdEmpacado.pdf");
VistasPrevias vp = new VistasPrevias();
vp.Navegar(@rutas.RutaDeTemp + "/rpt_ProdEmpacado.pdf");
vp.ShowDialog();
vp.Dispose();
wb.Close(false);
app.Quit();
}
private void drawGraph()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook
while (thread1.IsAlive)
{
//son satır bulunuyor excel dosyasındaki
Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlWorkSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz
// alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
string[] HeatingData = ReceiveData.Split(':');
string[] HeatingData2 = HeatingData[1].Split('D');
var result = HeatingData2[0];
double heating = Convert.ToDouble(result);
theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");
Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor!
this.Invoke((MethodInvoker)delegate
{
chart1.Series["Series1"].Points.AddY(result);
// excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
});
}
}
catch
{
// MessageBox.Show("Dosya bulunamadı");
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Zaman";
xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";
xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 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("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
}
}
private void _writeDastaSheetExcelMIMF()
{
var filepath = designssheetlink;
MyApp = new ExcelM.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(filepath);
#region Update MI
MySheet_MI = (ExcelM.Worksheet)MyBook.Sheets[tagFISName + " MI"]; // Explicit cast is not required here
ExcelM.Range cell_MI = MySheet_MI.Range[MySheet_MI.Cells[3, 1], MySheet_MI.Cells[excel_MI.Count + 2, 3]];
if (excel_MI.Count > 0)
{
foreach (ExcelM.Range item in cell_MI)
{
switch (item.Column)
{
case 1:
item.Value = excel_MI.ElementAt(item.Row - 3).AddressFIS;
break;
case 2:
item.Value = excel_MI.ElementAt(item.Row - 3).Trigger;
break;
case 3:
item.Value = excel_MI.ElementAt(item.Row - 3).FaultDescription;
break;
default:
break;
}
}
}
#endregion
# region update MF
MySheet_MF = (ExcelM.Worksheet)MyBook.Sheets[tagFISName + " MF"]; // Explicit cast is not required here
ExcelM.Range cell_MF = MySheet_MF.Range[MySheet_MF.Cells[3, 1], MySheet_MF.Cells[excel_MF.Count + 2, 3]];
if (excel_MF.Count > 0)
{
foreach (ExcelM.Range item in cell_MF)
{
// item.Value = string.Format("row:{0:D2} col:{1:D2}", item.Row, item.Column);
// item.Value = excel_MI.ElementAt(1).Address;
switch (item.Column)
{
case 1:
item.Value = excel_MF.ElementAt(item.Row - 3).AddressFIS;
break;
case 2:
item.Value = excel_MF.ElementAt(item.Row - 3).Trigger;
break;
case 3:
item.Value = excel_MF.ElementAt(item.Row - 3).FaultDescription;
break;
default:
break;
}
}
}
#endregion
// try
// {
MyBook.SaveAs(Filename: filepath);
MyBook.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.ToString());
// }
}
public void WriteResultToExcelSterter()
{
this.xlApp = new Microsoft.Office.Interop.Excel.Application();
if (this.xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
}
this.xlApp.Visible = false;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1);
Excel.Sheets xlWorksheet = xlWorkBook.Worksheets;
var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]);
int row = 1;
foreach (Starters student in statersList)
{
xlNewSheet.Cells[row, 1] = student.StudentNo;
xlNewSheet.Cells[row, 2] = student.Name;
xlNewSheet.Cells[row, 3] = student.Visa;
xlNewSheet.Cells[row, 4] = student.CourseCode;
xlNewSheet.Cells[row, 5] = student.StartDate;
xlNewSheet.Cells[row, 6] = student.EndDate;
xlNewSheet.Cells[row, 10] = student.Agent;
if (student.Visa == "Student")
{
xlNewSheet.Cells[row, 11] = student.CoEStartDate;
xlNewSheet.Cells[row, 12] = student.CoEEndDate;
xlNewSheet.Cells[row, 13] = student.CoEDescription;
xlNewSheet.Cells[row, 14] = student.CoEStatus;
if (student.CoEStatus != "Okay")
{
switch (student.CoEDateChecker)
{
case 1:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 2:
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 3:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 4:
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 5:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 6:
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 7:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
}
}
}
else
{
xlNewSheet.Cells[row, 14] = "N/A";
}
row++;
////ArrayList payments = student.GetPayments();
//foreach (Payment payment in payments)
//{
// xlNewSheet.Cells[row, 1] = student.StNo;
// if (payment.DueDate < System.DateTime.Today)
// {
// Excel.Range cell = xlNewSheet.Cells[row, 7];
// cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// }
// xlNewSheet.Cells[row, 7] = payment.DueDate;
// xlNewSheet.Cells[row, 8] = payment.PaymentAmount;
// xlNewSheet.Cells[row, 9] = payment.Note;
// row++;
//}
}
//Excel.Range range = xlNewSheet.UsedRange;
//range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls");
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
MessageBox.Show("Done!!!!");
}
static void Main(string[] args)
{
//optionally you can pass in command line arguements
//debug_mode will take a 1 or 0 to turn on additional logging
//int i_debug_mode = Int32.Parse(args[0]);
//string s_sourcefile = args[1];
//string s_password = args[2];
int i_debug_mode = 0;
//file path for excel sheet that will be modified
string s_sourcefile = @"FILE\PATH\HERE";
//Enter the password that will be used for the file
string s_password = "******";
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
try
{
if (i_debug_mode > 0)
{
Console.WriteLine("The filename is: " + s_sourcefile);
Console.WriteLine("Step1 Open the Excel file: " + s_sourcefile);
}
//open the Excel application
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelWorkbook = excelApp.Workbooks.Open(s_sourcefile,
0,
false,
Type.Missing,
Type.Missing,
Type.Missing,
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
false,
false,
0,
false,
false,
false);
if (i_debug_mode > 0)
{
Console.WriteLine("Step1 Open the Excel file Completed: " + s_sourcefile);
Console.WriteLine("__________________________________");
Console.WriteLine("Step2 Save the Excel file: " + s_sourcefile);
}
//Save the file with the "s_sourcefile" name
excelApp.DisplayAlerts = false;
excelWorkbook.SaveAs(s_sourcefile,
Type.Missing,
s_password, //password is used in the saveas function
Type.Missing,
Type.Missing,
Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
true,
Type.Missing,
Type.Missing,
Type.Missing);
if (i_debug_mode > 0)
{
Console.WriteLine("Step2 save the Excel file Completed with password: "******"__________________________________");
Console.WriteLine("Completed processing Excel File" + s_sourcefile);
}
}
catch (Exception e)
{
Console.WriteLine("The error message is : " + e.Message);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
//Close the workbook
excelWorkbook.Close(true, s_sourcefile, false);
Marshal.FinalReleaseComObject(excelWorkbook);
excelWorkbook = null;
//Quit the application
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
//Console.WriteLine("All connections are closed");
}
}
private void btnExportToXml_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Xml files (*.xml)|*.xml|Excel files (*.xls)";
dialog.OverwritePrompt = true;
dialog.RestoreDirectory = true;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
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();
}
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 void btnXuatExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
//Tạo đối tượng lưu tệp tin
SaveFileDialog fsave = new SaveFileDialog();
//Chỉ ra đuôi ở đây là .xlsx
fsave.Filter = "(Tất cả các tệp)|*.*|(Các tệp excel)|*.xlsx";
fsave.ShowDialog();
//Xử lý
if (fsave.FileName != "")
{
//Tạo Excel App
Excel.Application app = new Excel.Application();
//Tạo Workbook
Excel.Workbook wb = app.Workbooks.Add(Type.Missing);
//Tạo Worksheet
Excel._Worksheet sheet = null;
try
{
//Đọc dữ liệu từ ListView xuất ra file excel có định dạng
sheet = wb.ActiveSheet;
sheet.Name = "Danh Sách Nhà Cung Cấp";
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, lsvNhaCC.Columns.Count]].Merge();
sheet.Cells[1, 1].Value = "Danh sách nhà cung cấp";
sheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
sheet.Cells[1, 1].Font.Size = 20;
sheet.Cells[1, 1].Borders.Weight = Excel.XlBorderWeight.xlThin;
//Sinh tiêu đề
for (int i = 1; i <= lsvNhaCC.Columns.Count; i++)
{
sheet.Cells[2, i] = lsvNhaCC.Columns[i - 1].Text;
sheet.Cells[2, i].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
sheet.Cells[2, i].Font.Bold = true;
sheet.Cells[2, i].Borders.Weight = Excel.XlBorderWeight.xlThin;
}
//Sinh dữ liệu
for (int i = 1; i <= lsvNhaCC.Items.Count; i++)
{
ListViewItem item = lsvNhaCC.Items[i - 1];
sheet.Cells[i + 2, 1] = item.Text;
sheet.Cells[i + 2, 1].Borders.Weight = Excel.XlBorderWeight.xlThin;
for (int j = 2; j <= lsvNhaCC.Columns.Count; j++)
{
sheet.Cells[i + 2, j] = item.SubItems[j - 1].Text;
sheet.Cells[i + 2, j].Borders.Weight = Excel.XlBorderWeight.xlThin;
}
}
//Ghi lại
wb.SaveAs(fsave.FileName);
MessageBox.Show("Ghi thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
app.Quit();
wb = null;
}
}
}
public static void ExportToExcel(ListView pListView)
{
if (pListView.Items == null)
{
return;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd");
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
//这里直接删除，因为saveDialog已经做了文件是否存在的判断
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
xlApp.Visible = false;
//填充列
for (int i = 0; i < pListView.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = pListView.Columns[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Font.Bold = true;
}
//填充数据（这里分了两种情况，1：lv带CheckedBox，2：不带CheckedBox）
//带CheckedBoxes
if (pListView.CheckBoxes == true)
{
int tmpCnt = 0;
for (int i = 0; i < pListView.Items.Count; i++)
{
if (pListView.Items[i].Checked == true)
{
for (int j = 0; j < pListView.Columns.Count; j++)
{
if (j == 0)
{
worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
tmpCnt++;
}
}
}
else //不带Checkedboxe
{
for (int i = 0; i < pListView.Items.Count; i++)
{
for (int j = 0; j < pListView.Columns.Count; j++)
{
if (j == 0)
{
worksheet.Cells[2 + i, j + 1] = pListView.Items[i].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
worksheet.Cells[2 + i, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
}
}
}
object missing = System.Reflection.Missing.Value;
try
{
workbook.Saved = true;
workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e1)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + e1.Message);
}
finally
{
xlApp.Quit();
System.GC.Collect();
}
MessageBox.Show("导出Excle成功！");
}
public string GenerateInventoryReportExcelFileV2(IList <FCRegularLocationDetail> inventoryList, string sizeBundle)
{
inventoryList = CombineAndUnifyInventoryList(inventoryList);
var sizeArray = sizeBundle.Split(',');
if (sizeArray.Length <= 1)
{
sizeBundle = "XS,S,M,L,XL,XXL,XXXL,XXXXL,XXXXXL,XXXXXXL,2XL,3XL,4XL,5XL,1X,2X,3X,4X,5X,6X,2T,3T,4T,PS,PM,PL,PXL,XXS(4/5),XS(4/5),S(4),S(4/5),S(7/8),S(8),M(5/6),M(7/8),M(10/12),L(6X),L(7),L(14),L(14/16),L(16),L(19),XL(16),XL(18/20),12M,18M,24M,MT,LT,XLT,2XLT,3XLT,4XLT,SZ1,SZ2,SZ3,SZ4,SZ5,SZ6,SIZE 6,SIZE 7,SIZE 8,SIZE 10,SIZE 12,SIZE 14,SIZE 16,N/A";
sizeArray = sizeBundle.Split(',');
}
_ws = _wb.Worksheets[1];
for (int s = 0; s < sizeArray.Length; s++)
{
_ws.Cells[6, 5 + s] = sizeArray[s];
}
var poGroup = inventoryList.GroupBy(x => x.PurchaseOrder);
var currentRow = 7;
List <IGrouping <string, FCRegularLocationDetail> > colorGroup = new List <IGrouping <string, FCRegularLocationDetail> >();
List <IGrouping <string, FCRegularLocationDetail> > styleGroup = new List <IGrouping <string, FCRegularLocationDetail> >();
foreach (var p in poGroup)
{
var groupByStyle = p.GroupBy(x => x.Style);
foreach (var s in groupByStyle)
{
styleGroup.Add(s);
}
}
foreach (var s in styleGroup)
{
var groupByColor = s.GroupBy(x => x.Color);
foreach (var c in groupByColor)
{
colorGroup.Add(c);
}
}
foreach (var c in colorGroup)
{
_ws.Cells[currentRow, 1] = c.First().PurchaseOrder;
_ws.Cells[currentRow, 2] = c.First().Style;
_ws.Cells[currentRow, 3] = c.First().CustomerCode;
_ws.Cells[currentRow, 4] = c.First().Color;
foreach (var s in c)
{
var size = s.SizeBundle;
var columnIndex = 5 + Array.IndexOf(sizeArray, size);
if (columnIndex == 4)
{
_ws.Cells[currentRow, 5] = "Unidentified Size:";
_ws.Cells[currentRow, 6] = size;
continue;
}
_ws.Cells[currentRow, columnIndex] = s.AvailablePcs;
}
currentRow += 1;
}
for (int i = 0; i < sizeArray.Length; i++)
{
var size = sizeArray[i];
_ws.Cells[currentRow + 1, i + 5] = inventoryList.Where(x => x.SizeBundle == size).Sum(x => x.AvailablePcs);
}
_ws.Cells[currentRow + 3, 1] = "Total Pcs:";
var sum3 = inventoryList.Sum(x => x.AvailablePcs);
_ws.Cells[currentRow + 3, 2] = sum3;
var fullPath = @"D:\InventoryReport\InventoryReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
return(fullPath);
}
private void ReadHeader(string fileName)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
_excelApp = new Excel.ApplicationClass();
_excelBook = _excelApp.Workbooks.Open(fileName,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
if (_excelBook.Sheets.Count == 0)
{
ShowInvalidExcel();
CloseExcel();
System.Threading.Thread.CurrentThread.CurrentCulture = _oldCi;
return;
}
_excelSheet = TNSHelper.GetFirtVisbleSheet(_excelBook);
var rangeName = ((Excel.Range)_excelSheet.Cells[1, 1]);
var text = rangeName != null ? rangeName.Value2.ToString() : "";
TenNguon.Text = text;
if (TenNguon.Text != "")
{
text = text.Substring(text.IndexOf(']') + 1);
var temp = text.Split('|');
if (temp.Length > 0)
{
Tartget.Text = temp[0].TrimStart(' ', '(').Trim();
var tg = DMTnsTarget.GetTargetByName(Tartget.Text);
SetTarget(tg.Id, tg.Name, tg.DiplayName);
if (temp.Length > 1)
{
ThiTruong.Text = temp[1].TrimEnd(')').Trim();
var tt = DMTnsThiTruong.GetThiTruongByName(ThiTruong.Text);
SetThiTruong(tt.Id, tt.Name, tt.DiplayName);
}
}
}
_excelSheet.Cells[RowHeader, ColChannel] = TNS_SALE_REV_SCR_CT.KENH_PHAT_NAME;
_excelSheet.Cells[RowHeader, ColTimeline] = TNS_SALE_REV_SCR_CT.TIME_BAND;
_excelSheet.Cells[RowHeader, ColAdvertiser] = TNS_SALE_REV_SCR_CT.ADVERTISER_NAME;
_excelSheet.Cells[RowHeader, ColBrand] = TNS_SALE_REV_SCR_CT.BRAND_NAME;
_excelSheet.Cells[RowHeader, ColSector] = TNS_SALE_REV_SCR_CT.SECTOR_NAME;
_excelSheet.Cells[RowHeader, ColGroup] = TNS_SALE_REV_SCR_CT.GROUP_NAME;
_excelSheet.Cells[RowHeader, ColAgency] = TNS_SALE_REV_SCR_CT.AGENCY_NAME;
int currentDateCol = ColStartDate;
_listDates = new List<DateTime?>();
while (true)
{
var endDateCell = TNSHelper.GetCell(_excelSheet, RowDate, currentDateCol);
if (endDateCell == null || endDateCell.Value2 == null || endDateCell.Value2.ToString() == "")
break;
var date = TNSHelper.GetDateValue(endDateCell.Value2);
var name = TNSHelper.GetSubFix(date);
_excelSheet.Cells[RowHeader, currentDateCol + ColCostInBand] = TNS_SALE_REV_SCR_CT.ALTERNATIVE_COST +
name;
_excelSheet.Cells[RowHeader, currentDateCol + ColGrpInBand] = TNS_SALE_REV_SCR_CT.GRP + name;
_excelSheet.Cells[RowHeader, currentDateCol + ColDiscountInBand] = TNS_SALE_REV_SCR_CT.DISCOUNT + name;
_excelSheet.Cells[RowHeader, currentDateCol + ColCppInBand] = TNS_SALE_REV_SCR_CT.CPP + name;
_excelSheet.Cells[RowHeader, currentDateCol + ColGrpPercentInBand] = TNS_SALE_REV_SCR_CT.GRP_PERCENT + name;
_listDates.Add(date);
currentDateCol += endDateCell.MergeArea.Count;
}
var startDate = _listDates.Count == 0 ? null : _listDates[0];
var endDate = _listDates.Count == 0 ? null : _listDates[_listDates.Count - 1];
if (startDate == null || endDate == null)
{
ShowInvalidExcel();
}
else
{
TuThang.Value = startDate.Value.Month;
TuNam.Value = startDate.Value.Year;
DenNam.Value = startDate.Value.Year;
DenThang.Value = endDate.Value.Month;
}
_sheeName = Guid.NewGuid().ToString().Substring(0, 5) + DateTime.Today.ToString("yyyyMMddhhmmss");
_excelSheet.Name = _sheeName;
for (int i = 1; i < RowHeader; i++)
{
((Excel.Range)_excelSheet.Cells[1, 1]).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
Excel.XlFileFormat xlFormat = _excelBook.FileFormat;
_filePath = AppUtil.GetTempFile(Path.GetExtension(fileName));
_excelBook.SaveAs(_filePath, xlFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
true, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_excelVersion = _excelApp.Version;
CloseExcel();
System.Threading.Thread.CurrentThread.CurrentCulture = _oldCi;
}
catch (Exception ex)
{
PLException.AddException(ex);
ShowInvalidExcel();
CloseExcel();
}
}
void crearExcel(string path)
{
//Indicamos que vamos a trabajar con un tipo de archivo excel.
//Inicializar la aplicación Excel Object.
Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
try
{
//Verificamos que Excel este intalado en el equipo.
if (excel == null)
{
MessageBox.Show("¡No Tienes instalado Excel!");
return;
}
xlWorkBook = excel.Workbooks.Add(misValue); //Creamos un librio nuevo.
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(); //Creamos una hoja nueva.
xlWorkSheet.Name = "PrimeraComunion"; //Nombramos la hoja.
((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete(); //Eliminamos la hoja por defecto de excel.
//Insertamos los datos al libro.
inserta(xlWorkSheet);
//Damos formato al excel.
formato(xlWorkSheet);
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//Importenate cerrar todo.
xlWorkBook.Close(true, misValue, misValue);
excel.UserControl = false;
excel.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(excel);
xlWorkSheet = null;
xlWorkBook = null;
excel = null;
MessageBox.Show("Su archivo " + txtFileName.Text + " se creo correctamente lo puede encontrar en la ruta " + lbPath.Text
+ "", "Exito", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception ex)
{
xlWorkBook.Close(true, misValue, misValue);
excel.UserControl = false;
excel.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(excel);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
GC.Collect();
}
}
public TaskPriority()
{
InitializeComponent();
missing = System.Reflection.Missing.Value;
config_data.ConfigFile = Environment.GetEnvironmentVariable("USERPROFILE")+"\\IntCallBack.xls";
xlApp = new msexcel.Application();
time_wasting = false;
if (File.Exists(config_data.ConfigFile))
{
xlWorkBook = xlApp.Workbooks.Open(config_data.ConfigFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
xlWorkSheet = (msexcel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
double dummy = (double) (xlWorkSheet.Cells[1, 2] as msexcel.Range ).Value ;
config_data.PopUp = ((int)dummy == 1) ? true : false;
config_data.RFrequency = (int)(xlWorkSheet.Cells[2, 2] as msexcel.Range).Value;
config_data.Urgent_Hrs = (int)(xlWorkSheet.Cells[3, 2] as msexcel.Range).Value;
config_data.Urgent_Mins = (int)(xlWorkSheet.Cells[4, 2] as msexcel.Range).Value;
config_data.task1 = (string) (xlWorkSheet.Cells[5, 2] as msexcel.Range).Value;
config_data.task2 = (string)(xlWorkSheet.Cells[6, 2] as msexcel.Range).Value;
config_data.task3 = (string)(xlWorkSheet.Cells[7, 2] as msexcel.Range).Value;
config_data.task4 = (string)(xlWorkSheet.Cells[8, 2] as msexcel.Range).Value;
re_load_flag = true;
}
else
{
xlWorkBook = xlApp.Workbooks.Add(missing);
xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
config_data.PopUp = true;
config_data.RFrequency = 3;
config_data.Urgent_Hrs = 8;
config_data.Urgent_Mins = 0;
config_data.task1 = config_data.task2 = config_data.task3 = config_data.task4 = "";
xlWorkSheet.Cells[1, 1] = "PopUP";
xlWorkSheet.Cells[2, 1] = "Frequency";
xlWorkSheet.Cells[3, 1] = "Urgent Hrs";
xlWorkSheet.Cells[4, 1] = "Urgent Mins";
xlWorkSheet.Cells[5,1] = "Task 1";
xlWorkSheet.Cells[6,1] = "Task 2";
xlWorkSheet.Cells[7,1] = "Task 3";
xlWorkSheet.Cells[8,1] = "Task 4";
xlWorkSheet.Cells[1, 2] = (config_data.PopUp == true) ? "1" : "2";
xlWorkSheet.Cells[2, 2] = config_data.RFrequency.ToString();
xlWorkSheet.Cells[3, 2] = config_data.Urgent_Hrs.ToString();
xlWorkSheet.Cells[4, 2] = config_data.Urgent_Mins.ToString();
xlWorkSheet.Cells[5, 2] = config_data.task1;
xlWorkSheet.Cells[6, 2] = config_data.task1;
xlWorkSheet.Cells[7, 2] = config_data.task1;
xlWorkSheet.Cells[8, 2] = config_data.task1;
xlWorkBook.SaveAs(config_data.ConfigFile, msexcel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, msexcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
//xlWorkBook.Close();
re_load_flag = false;
}
}
public void ExportExcel(string filePath, System.Data.DataTable dt)
{
Excel.Application app = null;
Excel.Workbooks wbs = null;
Excel.Workbook wb = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
Excel.Range range = null;
try
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add();
sheets = wb.Sheets;
sheet = sheets[1];
for (int i = 0; i < dt.Columns.Count; i++)
{
range = sheet.Cells[1, i + 1];
range.Value = dt.Columns[i].Caption;
range.Font.Bold = true;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
range = sheet.Cells[i + 2, j + 1];
range.Value = $"{dt.Rows[i][j]}";
}
}
try
{
app.DisplayAlerts = false;
wb.SaveAs(filePath);
wb.Close(true, filePath, Type.Missing);
app.DisplayAlerts = true;
}
catch (COMException ex) { }
}
finally
{
app.Quit();
if (range != null)
{
Marshal.ReleaseComObject(range);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
if (wb != null)
{
Marshal.ReleaseComObject(wb);
}
if (app != null)
{
Marshal.ReleaseComObject(app);
}
range = null;
sheet = null;
wb = null;
app = null;
GC.Collect();
}
}
public void SaveAndExit(string filePath)
{
workbook.SaveAs(filePath);
workbook.Close();
app.Quit();
}
public ActionResult Export(int?department, long?contracttype, int?from, int?to, int?month1, int?nguoithan, int?education, int?phongban, DateTime?fromdate, DateTime?todate, int?gender, int?skill, string sugget = "")
{
int _depart = department.HasValue ? department.Value : -1;
int _from = from.HasValue ? from.Value : -1;
int _to = to.HasValue ? to.Value : -1;
//try
//{
Excel.Application application = new Excel.Application();
Excel.Workbook workbook = application.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet worksheet = workbook.ActiveSheet;
EmployeeViewModel pm = new EmployeeViewModel();
var model = _employeeService.GetAll();
_depart = (int)Session["_depart"];
sugget = Session["sugget"].ToString();
if (Session["baocao"].ToString() == "1")
{
fromdate = (DateTime)Session["fromdate"];
todate = (DateTime)Session["todate"];
model = _employeeService.SearchByDateOfBirth(_depart, fromdate, todate, sugget);
}
else if (Session["baocao"].ToString() == "2")
{
gender = (int)Session["gender"];
model = _employeeService.SearchByGender(_depart, gender, sugget);
}
else if (Session["baocao"].ToString() == "3")
{
education = (int)Session["education"];
model = _employeeService.SearchByEducation(_depart, education, sugget);
}
else if (Session["baocao"].ToString() == "4")
{
nguoithan = (int)Session["nguoithan"];
model = _employeeService.SearchByNguoiThan(nguoithan);
}
else if (Session["baocao"].ToString() == "5")
{
skill = (int)Session["skill"];
model = _employeeService.SearchBySkill(_depart, skill, sugget);
}
else if (Session["baocao"].ToString() == "6")
{
_from = (int)Session["_from"];
_to = (int)Session["_to"];
model = _employeeService.SearchByOld(_depart, _from, _to, sugget);
}
else if (Session["baocao"].ToString() == "7")
{
month1 = (int)Session["month1"];
_to = (int)Session["_to"];
model = _employeeService.SearchByDayOfBirth(month1);
}
//else if (Session["baocao"].ToString() == "8")
//{
// Session["baocao"] = "8";
// contracttype = (int)Session["contracttype"];
// model = _employeeService.SearchByContractTypes(_depart, contracttype);
//}
else
{
model = _employeeService.GetAll();
}
worksheet.Cells[1, 1] = "Mã NV";
worksheet.Cells[1, 2] = "Tên NV";
worksheet.Cells[1, 3] = "Quốc tịch";
worksheet.Cells[1, 4] = "Giới tính";
worksheet.Cells[1, 5] = "CMND";
worksheet.Cells[1, 6] = "Ngày sinh";
worksheet.Cells[1, 7] = "Nơi sinh";
worksheet.Cells[1, 8] = "Ngày cấp";
worksheet.Cells[1, 9] = "Nơi cấp";
worksheet.Cells[1, 10] = "Dân tộc";
worksheet.Cells[1, 11] = "Tôn giáo";
worksheet.Cells[1, 12] = "Địa chỉ";
worksheet.Cells[1, 13] = "Điện thoại";
worksheet.Cells[1, 14] = "Email";
worksheet.Cells[1, 15] = "Ngày nghỉ phép";
worksheet.Cells[1, 16] = "Trình độ";
worksheet.Cells[1, 17] = "Học vấn";
worksheet.Cells[1, 18] = "Chuyên môn";
worksheet.Cells[1, 19] = "Đoàn viên";
worksheet.Cells[1, 20] = "BHXH";
worksheet.Cells[1, 21] = "Ngày tham gia BHXH";
worksheet.Cells[1, 22] = "Số tài khoản ngân hàng";
worksheet.Cells[1, 23] = "Ngân hàng";
worksheet.Cells[1, 24] = "Ngày ký hợp đồng";
worksheet.Cells[1, 25] = "Ngày kết thúc hợp đông";
int row = 2;
foreach (EmployeeViewModel p in model)
{
worksheet.Cells[row, 1] = p.LastName;
worksheet.Cells[row, 2] = p.FirstName;
worksheet.Cells[row, 3] = p.Nationality.Name;
worksheet.Cells[row, 4] = p.Gender;
worksheet.Cells[row, 5] = p.IdentityNo;
worksheet.Cells[row, 6] = p.DateOfBirth;
worksheet.Cells[row, 7] = p.PlaceOfBirth;
worksheet.Cells[row, 8] = p.DateIssueIdentity;
worksheet.Cells[row, 9] = p.PlaceIssueIdentity;
worksheet.Cells[row, 10] = p.EthnicGroup.Name;
worksheet.Cells[row, 11] = p.Religion.Name;
worksheet.Cells[row, 12] = p.Address;
worksheet.Cells[row, 13] = p.Phone;
worksheet.Cells[row, 14] = p.Email;
worksheet.Cells[row, 15] = p.YearDayOff;
worksheet.Cells[row, 16] = p.Education.Title;
worksheet.Cells[row, 17] = p.DetailEducation;
worksheet.Cells[row, 18] = p.Certificate;
worksheet.Cells[row, 19] = p.CommunistYouthUnion;
worksheet.Cells[row, 20] = p.SocialInsuranceNo;
worksheet.Cells[row, 21] = p.DateIssueSocialInsurance;
worksheet.Cells[row, 22] = p.BankAccount;
worksheet.Cells[row, 23] = p.Bank;
worksheet.Cells[row, 24] = p.DateSignContract;
worksheet.Cells[row, 25] = p.DateOffContract;
row++;
}
workbook.SaveAs("d:\\myexcel.xls");
workbook.Close();
Marshal.ReleaseComObject(workbook);
application.Quit();
Marshal.FinalReleaseComObject(application);
ViewBag.Result = "Done";
return(View("Export"));
}
/// <summary>
/// Метод создания и сохранения документов
/// в форматах Microsoft Word (doc, PDF),
/// Excel (exls)
/// </summary>
/// <param name="type">Тип создаваемого документа
/// отчёт или статистика</param>
/// <param name="format">Формат сохранения
/// документ или таблица</param>
/// <param name="name">Название документа</param>
/// <param name="table">Входная таблица с данными</param>
public void Document_Create(Document_Type type,
Document_Format format, string name,
DataTable table)
{
//Получение данных о конфигурации документа
Configuration_class configuration_Class
= new Configuration_class();
configuration_Class.Document_Configuration_Get();
//Проверка на пустоту названия
switch (name != "" || name != null)
{
case true:
//Выбор формата либо Word либо Excel
switch (format)
{
case Document_Format.Word:
//Запуск процесса в дистпечере задач
word.Application application
= new word.Application();
//создание документа в процессе
word.Document document
//Присвоение документа процессу, Visible: true
//возможность редактирования документа
= application.Documents.Add(Visible: true);
try
{
//Объявление дипапазона для формирования текста
word.Range range = document.Range(0, 0);
//89Настройка отступов в документе
document.Sections.PageSetup.LeftMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Left_Merge);
document.Sections.PageSetup.TopMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Top_Merge);
document.Sections.PageSetup.RightMargin
= application.
CentimetersToPoints((float)
Configuration_class.doc_Right_Merg);
document.Sections.PageSetup.BottomMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Bottom_Merge);
//Присвоение текстового знеачения в дипазон
range.Text =
"Мвидео";
//Настройка выравнивания текста
range.ParagraphFormat.Alignment =
word.WdParagraphAlignment.
wdAlignParagraphCenter;
//Настройка интервала после абзаца
range.ParagraphFormat.SpaceAfter = 1;
//Настройка интервала перед абзаца
range.ParagraphFormat.SpaceBefore = 1;
//Настройка межстрочного интервала
range.ParagraphFormat.LineSpacingRule
= word.WdLineSpacing.wdLineSpaceSingle;
//Настройка названия шрифта
range.Font.Name = "Times New Roman";
//Настройка размера шрифта
range.Font.Size = 12;
//Добавление параграфов
document.Paragraphs.Add(); //В конце текста
document.Paragraphs.Add(); //Свободный
document.Paragraphs.Add(); //Для будущего текста
//Параграф для названия документа
word.Paragraph Document_Name
= document.Paragraphs.Add();
//Настройка параграфа через свойство диапазона
Document_Name.Format.Alignment
= word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
//Проверка на тип документа, отчёт или статистика
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type.Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//Добавление таблицы в область документа
//Указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range,
table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment
= word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment =
word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документа");
break;
}
}
//This method wirtes the sim data to the excel file.
public void WriteToExcel()
{
int iSimRunNumber = 0;
int linenumber = 0;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
//Add a new sheets object.
oXLSheets = oXL.Sheets as Excel.Sheets;
foreach (DOE22SimFile simfile in DOESimFiles)
{
iSimRunNumber++;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "RUN-" + iSimRunNumber.ToString();
//oSheet.Name = Path.GetFileName(simfile.filepath);
linenumber = 0;
// Output BEPS to excel Sheet.
oSheet.Cells[linenumber = 1, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print bpes report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
//reset linenumber for All sheet.
linenumber = 0;
oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oSheet.Name = "ALL";
foreach (DOE22SimFile simfile in DOESimFiles)
{
linenumber++;
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = Path.GetFileName(simfile.filepath);
linenumber++;
if (bWriteBEPS == true)
{
// Output Filename to excel Sheet.
oSheet.Cells[linenumber, 1] = "BEPS";
linenumber++;
//print beps report.
PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
}
//Print ES-D
if (bWriteESD == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "ES-D";
linenumber++;
//Print es-d report.
PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
linenumber = linenumber + simfile.esdTable.Rows.Count + 1;
}
//Print Zone Annual Data
if (bWriteZoneAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "Zone Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.ZoneAnnualTable, oSheet);
linenumber = linenumber + simfile.ZoneAnnualTable.Rows.Count + 1;
}
//Print System Annual Data
if (bWriteSystemAnnualData == true)
{
linenumber++;
oSheet.Cells[linenumber, 1] = "System Annual Data";
linenumber++;
//Print Zone Annual Data report.
PrintTableToExcel(linenumber, simfile.SystemAnnualTable, oSheet);
linenumber = linenumber + simfile.SystemAnnualTable.Rows.Count + 1;
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[simfile.bepsTable.Rows.Count,
simfile.bepsTable.Columns.Count]);
oRange.EntireColumn.AutoFit();
}
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(sFoldername + @"\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
/// <summary>
/// 复制最新母表，并将新增Key、主语言翻译变动所在行信息用指定颜色突出标注。返回true表示生成了Excel文件，反之表示无需生成或存在错误
/// </summary>
public static bool ExportComparedExcelFile(Color colorForAdd, Color colorForChange, string fillNullCellText, string savePath, out string errorString, out string promptMessage)
{
promptMessage = null;
errorString = null;
// 旧表中含有但新表中已删除的Key所在旧表中的数据索引（下标从0开始）
List <int> delectedKeyIndex = new List <int>();
// 新表中新增Key所在新表中的数据索引（下标从0开始）
List <int> newKeyIndex = new List <int>();
// 新表中的主语言翻译相对旧表变动的Key所在新表中的数据索引（下标从0开始）
List <int> translationChangedIndex = new List <int>();
List <string> oldKeys = AppValues.OldLangExcelInfo.Keys;
List <string> oldDefaultLanguageDataList = AppValues.OldLangExcelInfo.DefaultLanguageInfo.Data;
// 找到所有外语语种所在Excel文件中的列号（从1开始计）
List <int> otherLanguageColumnIndex = new List <int>();
foreach (LanguageInfo info in AppValues.LangExcelInfo.OtherLanguageInfo.Values)
{
otherLanguageColumnIndex.Add(info.ColumnIndex);
}
// 进行新旧母表对比
CompareExcelFile(AppValues.LangExcelInfo, AppValues.OldLangExcelInfo, out delectedKeyIndex, out newKeyIndex, out translationChangedIndex);
if (delectedKeyIndex.Count == 0 && newKeyIndex.Count == 0 && translationChangedIndex.Count == 0)
{
promptMessage = "新旧母表经对比未发现需要更新翻译的内容";
return(false);
}
// 新版母表中已删除的Key信息写入新建文本文件中，路径与选择导出的新建Excel文件相同
if (delectedKeyIndex.Count > 0)
{
StringBuilder delectedKeyInfoBuilder = new StringBuilder();
for (int i = 0; i < delectedKeyIndex.Count; ++i)
{
int dataIndex = delectedKeyIndex[i];
delectedKeyInfoBuilder.AppendFormat("第{0}行，Key为\"{1}\"，主语言译文为\"{2}\"", dataIndex + AppValues.EXCEL_DATA_START_INDEX, oldKeys[dataIndex], oldDefaultLanguageDataList[dataIndex]).AppendLine();
}
string txtFileName = string.Format("新版母表相对于旧版已删除Key信息 {0:yyyy年MM月dd日 HH时mm分ss秒}.txt", DateTime.Now);
string txtFileSavePath = Utils.CombinePath(Path.GetDirectoryName(savePath), txtFileName);
if (Utils.SaveFile(txtFileSavePath, delectedKeyInfoBuilder.ToString(), out errorString) == true)
{
promptMessage = string.Format("发现新版母表相对于旧版存在已删除的Key，相关信息已保存到{0}", txtFileSavePath);
}
else
{
promptMessage = string.Format("发现以下新版母表相对于旧版存在已删除的Key信息：\n{0}", delectedKeyInfoBuilder.ToString());
}
}
// 新增Key、主语言翻译变动所在行需在复制的新版母表中用指定背景色进行标注
if (newKeyIndex.Count > 0 || translationChangedIndex.Count > 0)
{
// 复制新版母表
FileState fileState = Utils.GetFileState(AppValues.ExcelFullPath);
if (fileState == FileState.Inexist)
{
errorString = string.Format("新版母表所在路径（{0}）已不存在，请勿在使用本工具过程中对母表文件进行操作，导出操作被迫中止", AppValues.ExcelFullPath);
return(false);
}
try
{
File.Copy(AppValues.ExcelFullPath, savePath, true);
}
catch (Exception exception)
{
errorString = string.Format("复制新版母表（{0}）至指定路径（{1}）失败：{2}，导出操作被迫中止", AppValues.ExcelFullPath, savePath, exception.Message);
return(false);
}
// 打开复制后的母表
// 导出待翻译内容到指定的新建Excel文件中
Excel.Application application = new Excel.Application();
// 不显示Excel窗口
application.Visible = false;
// 不显示警告对话框
application.DisplayAlerts = false;
// 禁止屏幕刷新
application.ScreenUpdating = false;
// 编辑非空单元格时不进行警告提示
application.AlertBeforeOverwriting = false;
// 打开Excel工作簿
Excel.Workbook workbook = application.Workbooks.Open(savePath);
// 找到名为data的Sheet表
Excel.Worksheet dataWorksheet = null;
int sheetCount = workbook.Sheets.Count;
string DATA_SHEET_NAME = AppValues.EXCEL_DATA_SHEET_NAME.Replace("$", "");
for (int i = 1; i <= sheetCount; ++i)
{
Excel.Worksheet sheet = workbook.Sheets[i] as Excel.Worksheet;
if (sheet.Name.Equals(DATA_SHEET_NAME))
{
dataWorksheet = sheet;
break;
}
}
if (dataWorksheet == null)
{
errorString = string.Format("新版母表（{0}）找不到Sheet名为{1}的数据表，请勿在使用本工具过程中对母表文件进行操作，导出操作被迫中止", AppValues.ExcelFullPath, DATA_SHEET_NAME);
return(false);
}
// 先将所有行的背景色清除
dataWorksheet.Cells.Interior.ColorIndex = 0;
// 将新增Key所在行背景色调为指定颜色并将对应译文部分统一填充为指定的字符串
int newKeyCount = newKeyIndex.Count;
for (int i = 0; i < newKeyCount; ++i)
{
int excelDataIndex = newKeyIndex[i];
int rowIndex = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
// 调整背景色
dataWorksheet.get_Range(string.Concat("A", rowIndex)).EntireRow.Interior.Color = ColorTranslator.ToOle(colorForAdd);
// 新增Key所在行的外语单元格填充为指定的字符串
foreach (int columnIndex in otherLanguageColumnIndex)
{
dataWorksheet.Cells[rowIndex, columnIndex] = fillNullCellText;
}
}
// 将翻译变动Key所在行背景色调为指定颜色，保留新版母表中储存的旧版外语译文
int translationChangedCount = translationChangedIndex.Count;
for (int i = 0; i < translationChangedCount; ++i)
{
int excelDataIndex = translationChangedIndex[i];
int rowIndex = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
// 调整背景色
dataWorksheet.get_Range(string.Concat("A", rowIndex)).EntireRow.Interior.Color = ColorTranslator.ToOle(colorForChange);
}
// 保存Excel
dataWorksheet.SaveAs(savePath);
workbook.SaveAs(savePath);
// 关闭Excel
workbook.Close(false);
application.Workbooks.Close();
application.Quit();
Utils.KillExcelProcess(application);
return(true);
}
return(false);
}
//先上导出代码
/// <summary>
/// 导出速度最快
/// </summary>
/// <param name="list">Key, Value</param>
/// <param name="filepath"></param>
/// <returns></returns>
public bool Export(List <DictionaryEntry> list, string filepath)
{
var bSuccess = true;
var miss = System.Reflection.Missing.Value;
var appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = null;
workbookdata = appexcel.Workbooks.Add();
//设置对象不可见
appexcel.Visible = false;
appexcel.DisplayAlerts = false;
try
{
foreach (var lv in list)
{
var keys = lv.Key as List <string>;
var values = lv.Value as List <IList <object> >;
var worksheetdata = (Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);
Debug.Assert(keys != null, "keys != null");
for (var i = 0; i < keys.Count - 1; i++)
{
//给工作表赋名称
worksheetdata.Name = keys[0];//列名的第一个数据位表名
worksheetdata.Cells[1, i + 1] = keys[i + 1];
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
//irowcount为实际行数，最大行
if (values == null)
{
continue;
}
{
var irowcount = values.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
const int ieachsize = 10000;
//icolumnaccount为实际列数，最大列数
var icolumnaccount = keys.Count - 1;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
var objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow;
}
//用for循环给数组赋值
for (var i = 0; i < icurrsize; i++)
{
for (var j = 0; j < icolumnaccount; j++)
{
var v = values[i + iparstedrow][j];
objval[i, j] = v?.ToString() ?? "";
}
}
var x = "A" + ((int)(iparstedrow + 2)).ToString();
var col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
var xlrang = worksheetdata.Range[x, col];
xlrang.NumberFormat = "@";
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
}
}
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet1"]).Delete();
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet2"]).Delete();
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet3"]).Delete();
//保存工作表
workbookdata.SaveAs(filepath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbookdata.Close(false, miss, miss);
appexcel.Workbooks.Close();
appexcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel);
GC.Collect();
}
catch (Exception)
{
//ErrorMsg = ex.Message;
bSuccess = false;
}
finally
{
//ExcelImportHelper.KillSpecialExcel(appexcel);
}
return(bSuccess);
}
/// <summary>
/// 导出新版母表相对旧版新增Key、主语言翻译变动所在行信息到新建的Excel文件中。返回true表示生成了Excel文件，反之表示无需生成或存在错误
/// </summary>
public static bool ExportNeedTranslateExcelFile(string savePath, out string errorString, out string promptMessage)
{
promptMessage = null;
errorString = null;
// 旧表中含有但新表中已删除的Key所在旧表中的数据索引（下标从0开始）
List <int> delectedKeyIndex = new List <int>();
// 新表中新增Key所在新表中的数据索引（下标从0开始）
List <int> newKeyIndex = new List <int>();
// 新表中的主语言翻译相对旧表变动的Key所在新表中的数据索引（下标从0开始）
List <int> translationChangedIndex = new List <int>();
List <LanguageInfo> languageInfoList = AppValues.LangExcelInfo.GetAllLanguageInfoList();
List <LanguageInfo> oldLanguageInfoList = AppValues.OldLangExcelInfo.GetAllLanguageInfoList();
List <string> keys = AppValues.LangExcelInfo.Keys;
List <string> oldKeys = AppValues.OldLangExcelInfo.Keys;
List <string> defaultLanguageDataList = AppValues.LangExcelInfo.DefaultLanguageInfo.Data;
List <string> oldDefaultLanguageDataList = AppValues.OldLangExcelInfo.DefaultLanguageInfo.Data;
// 进行新旧母表对比
CompareExcelFile(AppValues.LangExcelInfo, AppValues.OldLangExcelInfo, out delectedKeyIndex, out newKeyIndex, out translationChangedIndex);
if (delectedKeyIndex.Count == 0 && newKeyIndex.Count == 0 && translationChangedIndex.Count == 0)
{
promptMessage = "新旧母表经对比未发现需要更新翻译的内容";
return(false);
}
// 新版母表中已删除的Key信息写入新建文本文件中，路径与选择导出的新建Excel文件相同
if (delectedKeyIndex.Count > 0)
{
StringBuilder delectedKeyInfoBuilder = new StringBuilder();
for (int i = 0; i < delectedKeyIndex.Count; ++i)
{
int dataIndex = delectedKeyIndex[i];
delectedKeyInfoBuilder.AppendFormat("第{0}行，Key为\"{1}\"，主语言译文为\"{2}\"", dataIndex + AppValues.EXCEL_DATA_START_INDEX, oldKeys[dataIndex], oldDefaultLanguageDataList[dataIndex]).AppendLine();
}
string txtFileName = string.Format("新版母表相对于旧版已删除Key信息 {0:yyyy年MM月dd日 HH时mm分ss秒}.txt", DateTime.Now);
string txtFileSavePath = Utils.CombinePath(Path.GetDirectoryName(savePath), txtFileName);
if (Utils.SaveFile(txtFileSavePath, delectedKeyInfoBuilder.ToString(), out errorString) == true)
{
promptMessage = string.Format("发现新版母表相对于旧版存在已删除的Key，相关信息已保存到{0}", txtFileSavePath);
}
else
{
promptMessage = string.Format("发现以下新版母表相对于旧版存在已删除的Key信息：\n{0}", delectedKeyInfoBuilder.ToString());
}
}
// 新增Key、主语言翻译变动信息需写入新建Excel文件中
if (newKeyIndex.Count > 0 || translationChangedIndex.Count > 0)
{
// 导出待翻译内容到指定的新建Excel文件中
Excel.Application application = new Excel.Application();
// 不显示Excel窗口
application.Visible = false;
// 不显示警告对话框
application.DisplayAlerts = false;
// 禁止屏幕刷新
application.ScreenUpdating = false;
// 编辑非空单元格时不进行警告提示
application.AlertBeforeOverwriting = false;
// 新建Excel工作簿
Excel.Workbook workbook = application.Workbooks.Add();
// 在名为data的Sheet表中填充数据
Excel.Worksheet dataWorksheet = workbook.Sheets[1] as Excel.Worksheet;
dataWorksheet.Name = AppValues.EXCEL_DATA_SHEET_NAME.Replace("$", "");
// 设置表格中所有单元格均为文本格式
dataWorksheet.Cells.NumberFormatLocal = "@";
// 写入待翻译的内容，列自左向右分别为Key、新版主语言译文、旧版主语言译文、旧版各语种译文（注意Excel中左上角单元格下标为[1,1]）
// 定义各功能列在Excel的列号（从1开始计）
const int EXCEL_KEY_COLUMN_INDEX = 1;
const int EXCEL_DEFAULT_LANGUAGE_COLUMN_INDEX = 2;
const int EXCEL_OLD_DEFAULT_LANGUAGE_COLUMN_INDEX = 3;
const int EXCEL_OLD_OTHER_LANGUAGE_START_COLUMN_INDEX = 4;
// 写入语种描述信息、名称
dataWorksheet.Cells[AppValues.EXCEL_DESC_ROW_INDEX, EXCEL_KEY_COLUMN_INDEX] = "新增或主语言翻译变动的Key";
dataWorksheet.Cells[AppValues.EXCEL_DESC_ROW_INDEX, EXCEL_DEFAULT_LANGUAGE_COLUMN_INDEX] = "新版母表中主语言译文";
dataWorksheet.Cells[AppValues.EXCEL_NAME_ROW_INDEX, EXCEL_DEFAULT_LANGUAGE_COLUMN_INDEX] = languageInfoList[0].Name;
dataWorksheet.Cells[AppValues.EXCEL_DESC_ROW_INDEX, EXCEL_OLD_DEFAULT_LANGUAGE_COLUMN_INDEX] = "旧版母表中主语言译文";
int languageCount = languageInfoList.Count;
int otherLanguageCount = languageCount - 1;
for (int i = 1; i < languageCount; ++i)
{
string languageName = oldLanguageInfoList[i].Name;
dataWorksheet.Cells[AppValues.EXCEL_DESC_ROW_INDEX, i + EXCEL_OLD_DEFAULT_LANGUAGE_COLUMN_INDEX] = string.Format("旧版{0}语种的译文", languageName);
dataWorksheet.Cells[AppValues.EXCEL_NAME_ROW_INDEX, i + EXCEL_OLD_DEFAULT_LANGUAGE_COLUMN_INDEX] = languageName;
}
// 先将新版中主语言翻译变动内容写入新建的Excel文件
int translationChangedCount = translationChangedIndex.Count;
for (int i = 0; i < translationChangedCount; ++i)
{
int rowIndex = i + AppValues.EXCEL_DATA_START_INDEX;
int excelDataIndex = translationChangedIndex[i];
string key = keys[excelDataIndex];
int oldExcelDataIndex = AppValues.OldLangExcelInfo.KeyToDataIndex[key];
// Key
dataWorksheet.Cells[rowIndex, EXCEL_KEY_COLUMN_INDEX] = key;
// 新版主语言译文
dataWorksheet.Cells[rowIndex, EXCEL_DEFAULT_LANGUAGE_COLUMN_INDEX] = defaultLanguageDataList[excelDataIndex];
// 旧版主语言译文
dataWorksheet.Cells[rowIndex, EXCEL_OLD_DEFAULT_LANGUAGE_COLUMN_INDEX] = oldDefaultLanguageDataList[oldExcelDataIndex];
// 旧版各语种的译文
for (int j = 1; j < otherLanguageCount + 1; ++j)
{
int columnIndex = EXCEL_OLD_OTHER_LANGUAGE_START_COLUMN_INDEX + j - 1;
string data = oldLanguageInfoList[j].Data[oldExcelDataIndex];
dataWorksheet.Cells[rowIndex, columnIndex] = data;
}
}
// 空出3行后，将新版母表中新增Key内容写入新建的Excel文件，只需写入Key和新版母表中的主语言译文
const int SPACE_LINE_COUNT = 3;
int newKeyDataStartRowIndex = AppValues.EXCEL_DATA_START_INDEX + translationChangedCount + SPACE_LINE_COUNT;
if (translationChangedCount == 0)
{
newKeyDataStartRowIndex = AppValues.EXCEL_DATA_START_INDEX;
}
int newKeyCount = newKeyIndex.Count;
for (int i = 0; i < newKeyCount; ++i)
{
int excelDataIndex = newKeyIndex[i];
int rowIndex = i + newKeyDataStartRowIndex;
// Key
dataWorksheet.Cells[rowIndex, EXCEL_KEY_COLUMN_INDEX] = keys[excelDataIndex];
// 新版主语言译文
dataWorksheet.Cells[rowIndex, EXCEL_DEFAULT_LANGUAGE_COLUMN_INDEX] = defaultLanguageDataList[excelDataIndex];
}
// 对前2行配置行执行窗口冻结
Excel.Range excelRange = dataWorksheet.get_Range(dataWorksheet.Cells[AppValues.EXCEL_DATA_START_INDEX, 1], dataWorksheet.Cells[AppValues.EXCEL_DATA_START_INDEX + 1, 1]);
excelRange.Select();
application.ActiveWindow.FreezePanes = true;
// 美化生成的Excel文件
int lastColumnIndex = EXCEL_OLD_OTHER_LANGUAGE_START_COLUMN_INDEX + otherLanguageCount - 1;
_BeautifyExcelWorksheet(dataWorksheet, 30, lastColumnIndex);
// 保存Excel
dataWorksheet.SaveAs(savePath);
workbook.SaveAs(savePath);
// 关闭Excel
workbook.Close(false);
application.Workbooks.Close();
application.Quit();
Utils.KillExcelProcess(application);
return(true);
}
return(false);
}
public void exportarExcelDiametricClass(ProgressBar bar, DataGridView data, string Type, int numCLass)
{
try
{
SaveFileDialog fichero = new SaveFileDialog();
bar.Visible = true;
ProjectBL pyBl = new ProjectBL(Program.ContextData);
PROYECTO project = (PROYECTO)Program.Cache.Get("project");
if (project != null)
{
fichero.Filter = "Excel (*.xls)|*.xls";
fichero.FileName = "Reporte "+ Type+" clases diametricas "+ project.LUGAR + " "+ DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year;
if (fichero.ShowDialog() == DialogResult.OK)
{
//hacer visible la barra de progreso y fijar el valor maximo con el numero de registros a exportar
bar.Visible = true;
aplicacion = new excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
excel.Worksheet hoja_reporte;
hoja_reporte = (excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_reporte.Name = "Reporte Clases Diametricas";
hoja_reporte.Cells[1, 1] = "Informacion del proyecto";
hoja_reporte.Cells[1, 1].Font.Bold = true;
hoja_reporte.Range[hoja_reporte.Cells[1, 1], hoja_reporte.Cells[1, data.Columns.Count]].Merge();
hoja_reporte.Cells[2, 1] = "Lugar";
hoja_reporte.Cells[2, 1].Font.Bold = true;
hoja_reporte.Cells[2, 2] = project.LUGAR.ToString();
hoja_reporte.Cells[2, 2].HorizontalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
hoja_reporte.Cells[2, 2].WrapText = true;
hoja_reporte.Cells[3, 1] = "Descripcion";
hoja_reporte.Cells[3, 1].Font.Bold = true;
hoja_reporte.Cells[3, 2] = project.DESCRIPCION.ToString();
hoja_reporte.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
hoja_reporte.Cells[3, 2].WrapText = true;
hoja_reporte.Cells[4, 1] = "Responsable";
hoja_reporte.Cells[4, 1].Font.Bold = true;
hoja_reporte.Cells[4, 2] = project.USUARIO.NOMBRES.ToString() + " " + project.USUARIO.APELLIDOS.ToString();
hoja_reporte.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
hoja_reporte.Cells[4, 2].WrapText = true;
hoja_reporte.Cells[5, 1] = "Reporte " + Type;
hoja_reporte.Cells[5, 1].Font.Bold = true;
hoja_reporte.Range[hoja_reporte.Cells[5, 1], hoja_reporte.Cells[5, data.Columns.Count]].Merge();
hoja_reporte.get_Range("A1", "O" + data.Columns.Count).HorizontalAlignment = excel.XlVAlign.xlVAlignCenter;
hoja_reporte.get_Range("A" + data.Rows.Count + 6, "O" + data.Columns.Count).HorizontalAlignment = excel.XlVAlign.xlVAlignCenter;
int col = 1;
foreach (DataGridViewColumn column in data.Columns)
{
hoja_reporte.Cells[6, col].NumberFormat = "@";
hoja_reporte.Cells[6, col] = column.HeaderText.ToString();
hoja_reporte.Cells[6, col].WrapText = true;
hoja_reporte.Cells[6, col].Font.Bold = true;
col++;
}
int i = 7;
bar.Minimum = 0;
bar.Maximum = data.Rows.Count;
foreach (DataGridViewRow row in data.Rows)
{
//de formulario extraer coord x y Y el estrato la linea numero de parcela y el usuario se puede hay que revisar muy bien esta parte
int j = 1;
foreach (DataGridViewTextBoxCell cell in row.Cells)
{
hoja_reporte.Cells[i, j].WrapText = true;
hoja_reporte.Cells[i, j] = cell.Value.ToString();
if (j <= (data.Columns.Count - (numCLass + 3)))
{
hoja_reporte.Cells[i, j].Font.Bold = true;
}
j++;
}
bar.Increment(1);
i++;
}
libros_trabajo.Worksheets.Add(hoja_reporte);
MessageBox.Show("Los datos se exportaron correctamente.", "Operacion exitosa", MessageBoxButtons.OK, MessageBoxIcon.Information);
bar.Visible = false;
libros_trabajo.SaveAs(fichero.FileName, excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
System.Diagnostics.Process.Start(fichero.FileName);
}
}
else MessageBox.Show("No existe un proyecto abierto dentro del sistema.", "Operacion invalida", MessageBoxButtons.OK, MessageBoxIcon.Error);
bar.Visible = false;
}
catch (Exception ex)
{
bar.Visible = false;
Error_Form errorForm = new Error_Form(ex.Message);
errorForm.Show();
}
}
public static System.Data.DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable("EmployeeBilling");
bool hasHeaders = true;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
//Looping Total Sheet of Xl File
/*foreach (DataRow schemaRow in schemaTable.Rows)
* {
* }*/
//Looping a first Sheet of Xl File
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [EmployeeBilling$]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
conn.Close();
DataTable actualTable = new DataTable();
DataTable myTable = new DataTable();
myTable = dtexcel.Copy();
if (dtexcel.Columns.Count >= 5)
{
for (int i = 5; i < dtexcel.Columns.Count; i++)
{
// DataColumn dc = new DataColumn(((System.Data.DataColumn)dtexcel.Columns[i]).Caption);
// actualTable.Columns.Add(dc);
myTable.Columns.RemoveAt(5);
}
}
var projects = myTable.DefaultView.ToTable(true, ((DataColumn)dtexcel.Columns[2]).Caption);
List <Sheet> list = new List <Sheet>();
// var cols = dtexcel.Columns.Cast<System.Data.DataColumn>().Take(5);
foreach (DataRow dr in projects.Rows)
{
Sheet exSheet = new Sheet();
string columnProject = myTable.Columns[2].Caption;
string columnToSum = myTable.Columns[4].Caption;
string projectName = Convert.ToString(dr[0]);
string expression = columnProject + "='" + projectName + "'";
var rowArray = myTable.Select(expression);
DataTable sheetTable = myTable.Clone();
foreach (DataRow row in rowArray)
{
sheetTable.ImportRow(row);
}
object sum = sheetTable.Compute("Sum([" + columnToSum + "])", "");
exSheet.SheetName = projectName;
exSheet.SheetTable = sheetTable;
exSheet.SheetSum = sum;
list.Add(exSheet);
}
//--------------------- generte excel
ex.Application ExcelApp = new ex.Application();
ex.Workbook ExcelWorkBook = null;
ex.Worksheet ExcelWorkSheet = null;
ExcelApp.Visible = true;
ExcelWorkBook = ExcelApp.Workbooks.Add(ex.XlWBATemplate.xlWBATWorksheet);
try
{
for (int i = 1; i < list.Count; i++)
{
ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
}
for (int i = 0; i < list.Count; i++)
{
int r = 1; // Initialize Excel Row Start Position = 1
ExcelWorkSheet = ExcelWorkBook.Worksheets[i + 1];
//Writing Columns Name in Excel Sheet
for (int col = 1; col <= list[i].SheetTable.Columns.Count; col++)
{
ExcelWorkSheet.Cells[r, col] = list[i].SheetTable.Columns[col - 1].ColumnName;
}
r++;
//Writing Rows into Excel Sheet
for (int row = 0; row < list[i].SheetTable.Rows.Count; row++) //r stands for ExcelRow and col for ExcelColumn
{
// Excel row and column start positions for writing Row=1 and Col=1
for (int col = 1; col <= list[i].SheetTable.Columns.Count; col++)
{
ExcelWorkSheet.Cells[r, col] = list[i].SheetTable.Rows[row][col - 1].ToString();
}
r++;
}
ExcelWorkSheet.Name = "sheet" + i;//Renaming the ExcelSheets
}
ExcelWorkBook.SaveAs(@"C:\Users\Reyaz\Desktop\IPMS_test.xlsx");
ExcelWorkBook.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelWorkSheet);
Marshal.ReleaseComObject(ExcelWorkBook);
Marshal.ReleaseComObject(ExcelApp);
}
catch (Exception exHandle)
{
Console.WriteLine("Exception: " + exHandle.Message);
Console.ReadLine();
}
finally
{
foreach (Process process in Process.GetProcessesByName("Excel"))
{
process.Kill();
}
}
return(dtexcel);
}
public void exportarExcel(ToolStripProgressBar bar)
{
try
{
SaveFileDialog fichero = new SaveFileDialog();
bar.Visible = true;
ProjectBL pyBl = new ProjectBL(Program.ContextData);
FormBL formBl = new FormBL(Program.ContextData);
InventoryLineBL lineInvBl = new InventoryLineBL(Program.ContextData);
bool data = false;
PROYECTO project = (PROYECTO)Program.Cache.Get("project");
if (project != null)
{
try
{
fichero.Filter = "Excel (*.xls)|*.xls";
fichero.FileName = "proyecto " + project.LUGAR + " " + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year;
if (fichero.ShowDialog() == DialogResult.OK)
{
//hacer visible la barra de progreso y fijar el valor maximo con el numero de registros a exportar
bar.Visible = true;
aplicacion = new excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
PROYECTO py = pyBl.GetProject(project.NROPROY);
excel.Worksheet hoja_regeneracion;
hoja_regeneracion = (excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_regeneracion.Name = "Regeneracion";
hoja_regeneracion.Cells[1, 1] = "Lugar";
hoja_regeneracion.Cells[1, 2] = "Responsable";
hoja_regeneracion.Cells[1, 3] = "Coor X";
hoja_regeneracion.Cells[1, 4] = "Coor Y";
hoja_regeneracion.Cells[1, 5] = "Linea";
hoja_regeneracion.Cells[1, 6] = "Parcela";
hoja_regeneracion.Cells[1, 7] = "Estrato";
hoja_regeneracion.Cells[1, 8] = "Nombre Comun";
hoja_regeneracion.Cells[1, 9] = "Nombre Cientifico";
hoja_regeneracion.Cells[1, 10] = "Brinzal";
hoja_regeneracion.Cells[1, 11] = "Latizal";
hoja_regeneracion.get_Range("A1", "O1").Font.Bold = true;
hoja_regeneracion.get_Range("A1", "O1").VerticalAlignment =
excel.XlVAlign.xlVAlignCenter;
int j = 2;
foreach (FORMULARIO form in py.FORMULARIO)
{
//de formulario extraer coord x y Y el estrato la linea numero de parcela y el usuario se puede hay que revisar muy bien esta parte
bar.Maximum = form.LINEAREGENERACION.Count;
bar.Value = 0;
hoja_regeneracion.Cells[j, 1] = py.LUGAR.ToString();
hoja_regeneracion.Cells[j, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_regeneracion.Cells[j, 3] = form.COORDENADAX;
hoja_regeneracion.Cells[j, 4] = form.COORDENADAY;
hoja_regeneracion.Cells[j, 5] = form.LINEA.ToString();
hoja_regeneracion.Cells[j, 6] = form.PARCELA.ToString();
if(form.ESTRATO!=null) hoja_regeneracion.Cells[j, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
foreach (LINEAREGENERACION lineInv in form.LINEAREGENERACION)
{
hoja_regeneracion.Cells[j, 1] = py.LUGAR.ToString();
hoja_regeneracion.Cells[j, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_regeneracion.Cells[j, 3] = form.COORDENADAX;
hoja_regeneracion.Cells[j, 4] = form.COORDENADAY;
hoja_regeneracion.Cells[j, 5] = form.LINEA.ToString();
hoja_regeneracion.Cells[j, 6] = form.PARCELA.ToString();
if (form.ESTRATO != null) hoja_regeneracion.Cells[j, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
hoja_regeneracion.Cells[j, 8] = lineInv.ESPECIE.NOMCOMUN;
hoja_regeneracion.Cells[j, 9] = lineInv.ESPECIE.NOMCIENTIFICO;
hoja_regeneracion.Cells[j, 10] = lineInv.BRINZAL;
hoja_regeneracion.Cells[j, 11] = lineInv.LATIZAL;
bar.Increment(1);
data = true;
j++;
}
if (form.LINEAREGENERACION.Count == 0) j++;
}
if(data)libros_trabajo.Worksheets.Add(hoja_regeneracion);
data = false;
excel.Worksheet hoja_nomaderables;
hoja_nomaderables = (excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
hoja_nomaderables.Name = "No maderable";
hoja_nomaderables.Cells[1, 1] = "Lugar";
hoja_nomaderables.Cells[1, 2] = "Responsable";
hoja_nomaderables.Cells[1, 3] = "Coor X";
hoja_nomaderables.Cells[1, 4] = "Coor Y";
hoja_nomaderables.Cells[1, 5] = "Linea";
hoja_nomaderables.Cells[1, 6] = "Parcela";
hoja_nomaderables.Cells[1, 7] = "Estrato";
hoja_nomaderables.Cells[1, 8] = "Observaciones";
TypeUseBL typeUseBl = new TypeUseBL(Program.ContextData);
int cont = 9;
foreach (TIPODEUSO type in typeUseBl.GetTypeUse())
{
hoja_nomaderables.Cells[1, cont] = type.DESCRIPCION.ToString();
cont++;
}
hoja_nomaderables.get_Range("A1", "O1").Font.Bold = true;
hoja_nomaderables.get_Range("A1", "O1").VerticalAlignment = excel.XlVAlign.xlVAlignCenter;
int k = 2;
foreach (FORMULARIO form in py.FORMULARIO)
{
bar.Maximum = form.LINEANOMADERABLES.Count;
bar.Value = 0;
hoja_nomaderables.Cells[k, 1] = py.LUGAR.ToString();
hoja_nomaderables.Cells[k, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_nomaderables.Cells[k, 3] = form.COORDENADAX;
hoja_nomaderables.Cells[k, 4] = form.COORDENADAY;
hoja_nomaderables.Cells[k, 5] = form.LINEA.ToString();
hoja_nomaderables.Cells[k, 6] = form.PARCELA.ToString();
if (form.ESTRATO != null) hoja_nomaderables.Cells[k, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
foreach (LINEANOMADERABLES lineInv in form.LINEANOMADERABLES)
{
data = true;
hoja_nomaderables.Cells[k, 1] = py.LUGAR.ToString();
hoja_nomaderables.Cells[k, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_nomaderables.Cells[k, 3] = form.COORDENADAX;
hoja_nomaderables.Cells[k, 4] = form.COORDENADAY;
hoja_nomaderables.Cells[k, 5] = form.LINEA.ToString();
hoja_nomaderables.Cells[k, 6] = form.PARCELA.ToString();
if (form.ESTRATO != null) hoja_nomaderables.Cells[k, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
hoja_nomaderables.Cells[k, 8] = lineInv.OBSERVACIONES.ToString();
int n = 9;
foreach (TIPODEUSO t in typeUseBl.GetTypeUse())
{
foreach (TIPODEUSO use in lineInv.TIPODEUSO)
{
if (use.NOMBRETIPOUSO.Equals(t.NOMBRETIPOUSO))
{
hoja_nomaderables.Cells[k, n] = "1";
break;
}
else
{
hoja_nomaderables.Cells[k, n] = "0";
}
}
n++;
}
bar.Increment(1);
k++;
}
if (form.LINEANOMADERABLES.Count == 0) k++;
}
if(data)libros_trabajo.Worksheets.Add(hoja_nomaderables);
data = false;
excel.Worksheet hoja_maderable;
hoja_maderable = (excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
QualityBL qualityBl = new QualityBL(Program.ContextData);
hoja_maderable.Name = "Maderable";
hoja_maderable.Cells[1, 1] = "Convenciones calidad";
hoja_maderable.Cells[2, 1] = "Código";
hoja_maderable.Cells[2, 2] = "Significado";
int i = 3;
foreach (CALIDAD quality in qualityBl.GetQualities())
{
hoja_maderable.Cells[i, 1] = quality.CODCALIDAD;
hoja_maderable.Cells[i, 2] = quality.DESCRIPCALIDAD;
i++;
}
i += 2;
hoja_maderable.Cells[i, 1] = "Lugar";
hoja_maderable.Cells[i, 2] = "Responsable";
hoja_maderable.Cells[i, 3] = "Coor X";
hoja_maderable.Cells[i, 4] = "Coor Y";
hoja_maderable.Cells[i, 5] = "Linea";
hoja_maderable.Cells[i, 6] = "Parcela";
hoja_maderable.Cells[i, 7] = "Estrato";
hoja_maderable.Cells[i, 8] = "Numero de arbol";
hoja_maderable.Cells[i, 9] = "Nombre comun";
hoja_maderable.Cells[i, 10] = "Nombre cientifico";
hoja_maderable.Cells[i, 11] = "Calidad";
hoja_maderable.Cells[i, 12] = "DAP";
hoja_maderable.Cells[i, 13] = "CAP";
hoja_maderable.Cells[i, 14] = "Altura comercial";
hoja_maderable.Cells[i, 15] = "Altura total";
hoja_maderable.Cells[i, 16] = "Area basal";
hoja_maderable.Cells[i, 17] = "Volumen comercial";
hoja_maderable.Cells[i, 18] = "Volumen total";
hoja_maderable.get_Range("A1", "O1").Font.Bold = true;
hoja_maderable.get_Range("A1", "O1").VerticalAlignment =
excel.XlVAlign.xlVAlignCenter;
i++;
foreach (FORMULARIO form in py.FORMULARIO)
{
bar.Maximum = form.LINEAINVENTARIO.Count;
bar.Value = 0;
hoja_maderable.Cells[i, 1] = py.LUGAR.ToString();
hoja_maderable.Cells[i, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_maderable.Cells[i, 3] = form.COORDENADAX;
hoja_maderable.Cells[i, 4] = form.COORDENADAY;
hoja_maderable.Cells[i, 5] = form.LINEA.ToString();
hoja_maderable.Cells[i, 6] = form.PARCELA.ToString();
if (form.ESTRATO != null) hoja_maderable.Cells[i, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
foreach (LINEAINVENTARIO lineInv in form.LINEAINVENTARIO)
{
data = true;
hoja_maderable.Cells[i, 1] = py.LUGAR.ToString();
hoja_maderable.Cells[i, 2] = form.USUARIO.NOMBRES + form.USUARIO.APELLIDOS;
hoja_maderable.Cells[i, 3] = form.COORDENADAX.ToString();
hoja_maderable.Cells[i, 4] = form.COORDENADAY.ToString();
hoja_maderable.Cells[i, 5] = form.LINEA.ToString();
hoja_maderable.Cells[i, 6] = form.PARCELA.ToString();
if (form.ESTRATO != null) hoja_maderable.Cells[i, 7] = form.ESTRATO.DESCRIPESTRATO.ToString();
hoja_maderable.Cells[i, 8] = lineInv.NROARB.ToString();
hoja_maderable.Cells[i, 9] = lineInv.ESPECIE.NOMCOMUN.ToString();
hoja_maderable.Cells[i, 10] = lineInv.ESPECIE.NOMCIENTIFICO.ToString();
hoja_maderable.Cells[i, 11] = lineInv.CALIDAD.CODCALIDAD.ToString();
hoja_maderable.Cells[i, 12] = lineInv.DAP;
hoja_maderable.Cells[i, 13] = lineInv.CAP;
hoja_maderable.Cells[i, 14] = lineInv.ALTCOMER_M;
hoja_maderable.Cells[i, 15] = lineInv.ALTTOT_M;
hoja_maderable.Cells[i, 16] = lineInv.AREABASAL;
hoja_maderable.Cells[i, 17] = lineInv.VOLCOM;
hoja_maderable.Cells[i, 18] = lineInv.VOLTOT;
i++;
bar.Increment(1);
}
if (form.LINEAINVENTARIO.Count == 0) i++;
}
if(data)libros_trabajo.Worksheets.Add(hoja_maderable);
MessageBox.Show("Los datos se exportaron correctamente.", "Operacion exitosa", MessageBoxButtons.OK, MessageBoxIcon.Information);
bar.Visible = false;
libros_trabajo.SaveAs(fichero.FileName,
excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
System.Diagnostics.Process.Start(fichero.FileName);
}
bar.Visible = false;
}
catch (Exception ex)
{
bar.Visible = false;
throw ex;
}
}
else MessageBox.Show("No existe un proyecto abierto dentro del sistema.", "Operacion invalida", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
bar.Visible = false;
Error_Form errorForm = new Error_Form(ex.Message);
ToolStrip menu = bar.GetCurrentParent();
Principal_Form parent = (Principal_Form)menu.Parent;
errorForm.MdiParent = (Form)parent;
errorForm.Show();
}
}
private void GetTimetable_Click(object sender, EventArgs e)
{
String[] Days = new String[] { "Понедельник", "Вторник", "Среда", "Четверг", "Пятница", "Суббота" };
String[] Cols1 = new String[] { "Д. недели", "№ пары", "Начало", "Конец" };
String[] Cols2 = new String[] { "Предмет", "Тип", "Преподаватель", "Ауд." };
String[] ClassTime = new String[] { "9-00", "10-30", "10-40", "12-10", "13-00", "14-30", "14-40", "16-10", "16-20", "17-50", "18-00", "19-30" };
{
ExApp = new Excel.Application()
{
Visible = false
};
//Создание книги и листа
ResultBook = ExApp.Workbooks.Add();
ResultSheet = ResultBook.Worksheets.Add();
ResultSheet.Name = "TimeTable";
ResultSheet.Range[ResultSheet.Cells[1, 1], ResultSheet.Cells[39, 4 + CPG_GPanel.Controls.Count * 8]].Borders.Item[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
ResultSheet.Range[ResultSheet.Cells[1, 1], ResultSheet.Cells[39, 4 + CPG_GPanel.Controls.Count * 8]].Borders.Item[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
ResultSheet.Range[ResultSheet.Cells[1, 1], ResultSheet.Cells[39, 4 + CPG_GPanel.Controls.Count * 8]].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
ResultSheet.Range[ResultSheet.Cells[1, 1], ResultSheet.Cells[39, 4 + CPG_GPanel.Controls.Count * 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Подзаголовки
ResultSheet.Range["A1", "D1"].Interior.Color = ColorButtons[0].BackColor;
ResultSheet.Range["A1", "D1"].Orientation = 90;
FontSet(ResultSheet.Range["A1", "D1"], CPF_SubHead2.Tag as Font, CPFC_SubHead2.BackColor);
for (int i = 0; i < 4; i++)
{
ResultSheet.Range[ResultSheet.Cells[1, i + 1], ResultSheet.Cells[3, i + 1]].Merge();
ResultSheet.Cells[1, i + 1] = Cols1[i];
}
//Дни недели
FontSet(ResultSheet.Range["A4", "A39"], CPF_SubHead1.Tag as Font, CPFC_SubHead1.BackColor);
for (int i = 0; i < 6; i++)
{
ResultSheet.Range["A" + (6 * i + 4).ToString(), "A" + (6 * i + 9).ToString()].Merge();
ResultSheet.Cells[i * 6 + 4, 1].Interior.Color = ColorButtons[i + 4].BackColor;
ResultSheet.Cells[i * 6 + 4, 1] = Days[i];
ResultSheet.Cells[i * 6 + 4, 1].Orientation = 90;
}
//Номера и время пар
ResultSheet.Range[ResultSheet.Cells[4, 2], ResultSheet.Cells[39, 4]].Interior.Color = ColorButtons[10].BackColor;
FontSet(ResultSheet.Range["B4", "D39"], CPF_SubHead2.Tag as Font, CPFC_SubHead2.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 2], ResultSheet.Cells[39, 4]].NumberFormat = "@";
for (int i = 0; i < 6; i++)
{
for (int j = 0; j < 6; j++)
{
ResultSheet.Cells[i * 6 + 4 + j, 2] = j + 1;
ResultSheet.Cells[i * 6 + 4 + j, 3] = ClassTime[2 * j].ToString();
ResultSheet.Cells[i * 6 + 4 + j, 4] = ClassTime[2 * j + 1].ToString();
}
}
//Границы
BorderSet(ResultSheet.Range["A1", "D3"]);
BorderSet(ResultSheet.Range["A4", "D9"]);
BorderSet(ResultSheet.Range["A10", "D15"]);
BorderSet(ResultSheet.Range["A16", "D21"]);
BorderSet(ResultSheet.Range["A22", "D27"]);
BorderSet(ResultSheet.Range["A28", "D33"]);
BorderSet(ResultSheet.Range["A34", "D39"]);
}//Заготовка под расписание
if (TeacherCheckbox.Checked)
{
Teachers = new List <string>();
Excel.Workbook TeachBook = ExApp.Workbooks.Open(TeachPath);
Excel.Worksheet TeachSheet = TeachBook.Worksheets[1];
for (int i = 0; i < TeachSheet.UsedRange.Rows.Count; i++)
{
if (!Teachers.Contains(TeachSheet.Cells[i + 1, 1].Text))
{
Teachers.Add(TeachSheet.Cells[i + 1, 1].Text);
}
}
}
foreach (Control TLabel in CPG_TPanel.Controls)
{
Excel.Workbook SourceBook = ExApp.Workbooks.Open(TLabel.Tag.ToString());
Excel.Worksheet SourceSheet = SourceBook.Worksheets[1];
foreach (Control GLabel in CPG_GPanel.Controls)
{
try
{//FontSet(ResultSheet.Range[ResultSheet.Cells[], ResultSheet.Cells[]], CPF_Group.Tag as Font, CPFC_Group.BackColor);
Int32 GIndex = CPG_GPanel.Controls.GetChildIndex(GLabel);
Int32 GCol = SourceSheet.Cells.Find(GLabel.Text).Column;
//Название группы
BorderSet(ResultSheet.Range[ResultSheet.Cells[1, 5 + GIndex * 8], ResultSheet.Cells[1, 12 + GIndex * 8]]);
ResultSheet.Range[ResultSheet.Cells[1, 5 + GIndex * 8], ResultSheet.Cells[1, 12 + GIndex * 8]].Merge();
ResultSheet.Cells[1, 5 + GIndex * 8] = GLabel.Text;
ResultSheet.Cells[1, 5 + GIndex * 8].Interior.Color = ColorButtons[1].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[1, 5 + GIndex * 8], ResultSheet.Cells[1, 12 + GIndex * 8]], CPF_Group.Tag as Font, CPFC_Group.BackColor);
//Нечетные
BorderSet(ResultSheet.Range[ResultSheet.Cells[2, 5 + GIndex * 8], ResultSheet.Cells[3, 8 + GIndex * 8]]);
ResultSheet.Range[ResultSheet.Cells[2, 5 + GIndex * 8], ResultSheet.Cells[2, 8 + GIndex * 8]].Merge();
ResultSheet.Cells[2, 5 + GIndex * 8] = "Нечетные";
FontSet(ResultSheet.Range[ResultSheet.Cells[2, 5 + GIndex * 8], ResultSheet.Cells[2, 8 + GIndex * 8]], CPF_SubHead1.Tag as Font, CPFC_SubHead1.BackColor);
ResultSheet.Cells[2, 5 + GIndex * 8].Interior.Color = ColorButtons[2].BackColor;
//Четные
BorderSet(ResultSheet.Range[ResultSheet.Cells[2, 9 + GIndex * 8], ResultSheet.Cells[3, 12 + GIndex * 8]]);
ResultSheet.Range[ResultSheet.Cells[2, 9 + GIndex * 8], ResultSheet.Cells[2, 12 + GIndex * 8]].Merge();
ResultSheet.Cells[2, 9 + GIndex * 8] = "Четные";
FontSet(ResultSheet.Range[ResultSheet.Cells[2, 9 + GIndex * 8], ResultSheet.Cells[2, 12 + GIndex * 8]], CPF_SubHead1.Tag as Font, CPFC_SubHead1.BackColor);
ResultSheet.Cells[2, 9 + GIndex * 8].Interior.Color = ColorButtons[3].BackColor;
//Подзаголовки пар
ResultSheet.Range[ResultSheet.Cells[3, 5 + GIndex * 8], ResultSheet.Cells[3, 8 + GIndex * 8]].Interior.Color = ColorButtons[2].BackColor;
ResultSheet.Range[ResultSheet.Cells[3, 9 + GIndex * 8], ResultSheet.Cells[3, 12 + GIndex * 8]].Interior.Color = ColorButtons[3].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[3, 5 + GIndex * 8], ResultSheet.Cells[3, 12 + GIndex * 8]], CPF_SubHead2.Tag as Font, CPFC_SubHead2.BackColor);
for (int i = 0; i < 4; i++)
{
ResultSheet.Cells[3, 5 + GIndex * 8 + i] = Cols2[i];
ResultSheet.Cells[3, 9 + GIndex * 8 + i] = Cols2[i];
}
//Расписание пар
ResultSheet.Range[ResultSheet.Cells[4, 5 + GIndex * 8], ResultSheet.Cells[39, 5 + GIndex * 8]].Interior.Color = ColorButtons[11].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 5 + GIndex * 8], ResultSheet.Cells[39, 5 + GIndex * 8]], CPF_Table.Tag as Font, CPFC_Table.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 6 + GIndex * 8], ResultSheet.Cells[39, 6 + GIndex * 8]].Interior.Color = ColorButtons[12].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 6 + GIndex * 8], ResultSheet.Cells[39, 6 + GIndex * 8]], CPF_TypeNumber.Tag as Font, CPFC_TypeNumber.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 7 + GIndex * 8], ResultSheet.Cells[39, 7 + GIndex * 8]].Interior.Color = ColorButtons[13].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 7 + GIndex * 8], ResultSheet.Cells[39, 7 + GIndex * 8]], CPF_Table.Tag as Font, CPFC_Table.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 8 + GIndex * 8], ResultSheet.Cells[39, 8 + GIndex * 8]].Interior.Color = ColorButtons[14].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 8 + GIndex * 8], ResultSheet.Cells[39, 8 + GIndex * 8]], CPF_TypeNumber.Tag as Font, CPFC_TypeNumber.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 9 + GIndex * 8], ResultSheet.Cells[39, 9 + GIndex * 8]].Interior.Color = ColorButtons[15].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 9 + GIndex * 8], ResultSheet.Cells[39, 9 + GIndex * 8]], CPF_Table.Tag as Font, CPFC_Table.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 10 + GIndex * 8], ResultSheet.Cells[39, 10 + GIndex * 8]].Interior.Color = ColorButtons[16].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 10 + GIndex * 8], ResultSheet.Cells[39, 10 + GIndex * 8]], CPF_TypeNumber.Tag as Font, CPFC_TypeNumber.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 11 + GIndex * 8], ResultSheet.Cells[39, 11 + GIndex * 8]].Interior.Color = ColorButtons[17].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 11 + GIndex * 8], ResultSheet.Cells[39, 11 + GIndex * 8]], CPF_Table.Tag as Font, CPFC_Table.BackColor);
ResultSheet.Range[ResultSheet.Cells[4, 12 + GIndex * 8], ResultSheet.Cells[39, 12 + GIndex * 8]].Interior.Color = ColorButtons[18].BackColor;
FontSet(ResultSheet.Range[ResultSheet.Cells[4, 12 + GIndex * 8], ResultSheet.Cells[39, 12 + GIndex * 8]], CPF_TypeNumber.Tag as Font, CPFC_TypeNumber.BackColor);
for (int i = 0; i < 6; i++)
{
BorderSet(ResultSheet.Range[ResultSheet.Cells[4 + 6 * i, 5 + GIndex * 8], ResultSheet.Cells[9 + 6 * i, 8 + GIndex * 8]]);
BorderSet(ResultSheet.Range[ResultSheet.Cells[4 + 6 * i, 9 + GIndex * 8], ResultSheet.Cells[9 + 6 * i, 12 + GIndex * 8]]);
for (int j = 0; j < 12; j++)
{
for (int k = 0; k < 4; k++)
{
if (k == 1)
{
ResultSheet.Cells[6 * i + 4 + j / 2, j % 2 * 4 + k + 5 + GIndex * 8] = String.Concat(SourceSheet.Cells[12 * i + 4 + j, k + GCol].Text.Split('\n'));
}
else if (k == 2 && TeacherCheckbox.Checked)
{
ResultSheet.Cells[6 * i + 4 + j / 2, j % 2 * 4 + k + 5 + GIndex * 8] = ReplaceTeacher(SourceSheet.Cells[12 * i + 4 + j, k + GCol].Text);
}
else
{
ResultSheet.Cells[6 * i + 4 + j / 2, j % 2 * 4 + k + 5 + GIndex * 8] = SourceSheet.Cells[12 * i + 4 + j, k + GCol];
}
}
}
}
}
catch (Exception) { }
}
}
//Общие параметры листа
ResultSheet.UsedRange.Columns.AutoFit();
ResultSheet.UsedRange.Rows.AutoFit();
ResultBook.SaveAs(Path.GetFullPath("ResultBook.xlsx"));
ExApp.Quit();
if (MessageBox.Show("Создание файла завершено.\nВыйти?", "Готово!", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Close();
}
/*
* {
* List<String> fullprepods = new List<string>();
* List<String> shortprepods = new List<string>();
* Excel.Application PrepApp = new Excel.Application()
* {
* Visible = false
* };
* Excel.Workbook PrepBook = PrepApp.Workbooks.Open(Path.GetFullPath("prep.xlsx"));
* Excel.Worksheet PrepS1 = PrepBook.Sheets[1];
* for (int i = 0; i < PrepS1.UsedRange.Rows.Count; i++)
* {
* fullprepods.Add(PrepS1.Cells[i + 1, 1].Text);
* }
* PrepApp.Quit();
* }//Редактируем преподавателей
* //ExApp.Visible = true;*/
}
public void SaveAsDocument(string name)
{
xlWorkBook.SaveAs(name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
public void Run()
{
int year = 13;
// get data
for (int i = 1; i <= 12; i++)
{
Summary summary = new Summary();
summary.GetInfo(year, i);
sumList.Add(summary);
}
// write to excel
Excel.Application excel = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
Excel.Workbook book = excel.Workbooks.Add(misValue);
Excel.Worksheet sheet = book.Worksheets[1];
// fill sheet
int column = 1;
int row = 1;
sheet.Cells[row, column] = "Production report for Markham year " + year.ToString();
sheet.Cells.get_Range("A1").Font.Bold = true;
sheet.Cells.get_Range("A1").Font.Size = 20;
sheet.Cells.get_Range("A1").Font.ColorIndex = 3;
sheet.Cells.get_Range("A1", "L1").Merge();
row = 2;
sheet.Cells[row++, 1] = "Period";
sheet.Cells[row++, 1] = "Backer Count";
sheet.Cells[row++, 1] = "Mandrel Count";
sheet.Cells[row++, 1] = "Plate Count";
sheet.Cells[row++, 1] = "Hollow Total";
row++;
sheet.Cells[row++, 1] = "Backer Count";
sheet.Cells[row++, 1] = "Plate Count";
sheet.Cells[row++, 1] = "Bolster Count";
sheet.Cells[row++, 1] = "Feeder Count";
sheet.Cells[row++, 1] = "Sub Bolster Count";
sheet.Cells[row++, 1] = "Die Ring Count";
sheet.Cells[row++, 1] = "Solid Total";
row++;
sheet.Cells[row++, 1] = "Piece Total";
row++;
sheet.Cells[row++, 1] = "Nitride Weight (lb)";
sheet.Cells[row++, 1] = "Heat Treat Weight (lb)";
sheet.Cells[row++, 1] = "Mill Time (hour)";
sheet.Cells[row++, 1] = "Lathe Time (hour)";
sheet.Cells[row++, 1] = "Spark Time (hour)";
sheet.Cells[row++, 1] = "Wire Time (hour)";
sheet.Cells.get_Range("A2", "A" + row.ToString()).Font.Bold = true;
// write data
for (int i = 0; i < sumList.Count; i++)
{
Summary summary = sumList[i];
row = 2;
column++;
sheet.Cells[row, column].NumberFormat = "@";
sheet.Cells[row++, column] = (i + 1).ToString("D2");
sheet.Cells[row++, column] = summary.hbCount.ToString();
sheet.Cells[row++, column] = summary.maCount.ToString();
sheet.Cells[row++, column] = summary.hpCount.ToString();
sheet.Cells[row++, column] = summary.hTotal.ToString();
row++;
sheet.Cells[row++, column] = summary.sbCount.ToString();
sheet.Cells[row++, column] = summary.spCount.ToString();
sheet.Cells[row++, column] = summary.boCount.ToString();
sheet.Cells[row++, column] = summary.feCount.ToString();
sheet.Cells[row++, column] = summary.suCount.ToString();
sheet.Cells[row++, column] = summary.drCount.ToString();
sheet.Cells[row++, column] = summary.sTotal.ToString();
row++;
sheet.Cells[row++, column] = (summary.sTotal + summary.hTotal).ToString();
row++;
sheet.Cells[row++, column] = summary.niPound.ToString("F2");
sheet.Cells[row++, column] = summary.hePound.ToString("F2");
sheet.Cells[row++, column] = summary.miTime.ToString("F2");
sheet.Cells[row++, column] = summary.laTime.ToString("F2");
sheet.Cells[row++, column] = summary.spTime.ToString("F2");
sheet.Cells[row++, column] = summary.wiTime.ToString("F2");
}
sheet.Cells.get_Range("A6", "M6").Font.Bold = true;
sheet.Cells.get_Range("A14", "M14").Font.Bold = true;
sheet.Cells.get_Range("A16", "M16").Font.Bold = true;
sheet.Cells.get_Range("B2", "M2").Font.Bold = true;
sheet.Cells.get_Range("A2", "M2").Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
// adjust style
sheet.Cells.Columns.AutoFit();
sheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// write to file
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Production Summary Markham " + DateTime.Today.ToString("MM-dd-yyyy") + ".xlsx");
File.Delete(path);
book.SaveAs(path, Excel.XlFileFormat.xlOpenXMLWorkbook);
excel.Quit();
System.Diagnostics.Process.Start(path);
}
private void SaveToFile()
{
readerfil = new StreamReader("workfil.txt");
string workfil = readerfil.ReadLine();
readerfil.Close();
readerset = new StreamReader("Set.txt");
for (int i = 0; i < Convert.ToInt32(workfil); i++)
{
string NameFil = readerset.ReadLine();
string RukFil = readerset.ReadLine();
string TelFil = readerset.ReadLine();
string Per = readerset.ReadLine();
//label1.Text = "Филиал №" + workfil + " - " + NameFil;
// label2.Text = "Руководитель филиала - " + RukFil;
// label3.Text = "Телефон - " + TelFil;
}
readerset = new StreamReader("Set.txt");
string NameFil1 = readerset.ReadLine();
string RukFil1 = readerset.ReadLine();
string TelFil1 = readerset.ReadLine();
readerset.ReadLine();
string NameFil2 = readerset.ReadLine();
string RukFil2 = readerset.ReadLine();
string TelFil2 = readerset.ReadLine();
readerset.ReadLine();
string NameFil3 = readerset.ReadLine();
string RukFil3 = readerset.ReadLine();
string TelFil3 = readerset.ReadLine();
readerset.ReadLine();
readerset.Close();
Stream fs = new FileStream(@"Data" + workfil + ".txt", FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs);
Excel.Application excel = new Excel.Application();
excel.Visible = true;
excel.SheetsInNewWorkbook = 2;
excel.Workbooks.Add(Type.Missing);
Excel.Workbook workbook = excel.Workbooks[1];
Excel.Worksheet sheet = workbook.Worksheets.get_Item(1);
string s62 = DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
sheet.get_Range("A1", "D1").MergeCells = true;
sheet.get_Range("A2", "D2").MergeCells = true;
sheet.Cells[1, 1].Value = "Товары на доставку от " + s62;
//string nf = String.Concat(NameFil, workfil);
//string tf = String.Concat(TelFil, workfil);
sheet.Cells[2, 1].Value = "Филиал № " + workfil;
sheet.Cells[4, 1].Value = "№";
sheet.Cells[4, 2].Value = "Название";
sheet.Cells[4, 3].Value = "Штрих-Код";
sheet.Cells[4, 4].Value = "Кол-во";
int k = 1;
while (sr.Peek() != -1)
{
string s1 = sr.ReadLine();
string s2 = sr.ReadLine();
string s3 = sr.ReadLine();
string s4 = sr.ReadLine();
string s5 = sr.ReadLine();
string s6 = sr.ReadLine();
if (Convert.ToInt32(s3) < Convert.ToInt32(s4))
{
sheet.get_Range("C1", "D1000").NumberFormat = "0";
var cells = sheet.get_Range("B2", "F5");
sheet.Cells[k + 4, 1].Value = k;
sheet.Cells[k + 4, 2].Value = " " + s1 + " ";
sheet.Cells[k + 4, 3].Value = s2;
sheet.Cells[k + 4, 4].Value = Convert.ToInt32(s5) - Convert.ToInt32(s3);
sheet.Columns.AutoFit();
k++;
}
}
sr.Close();
if (k == 1)
{
workbook.Close(0);
excel.Visible = false;
MessageBox.Show("Нет товаров для доставки.", "Нет товаров", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
sheet.Cells.get_Range("A4", "D" + (k + 3)).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
string dat = DateTime.Now.ToString("dd.MM.yyyy HH.mm");
workbook.SaveAs(@"C:\TDC\Доставка\Доставка для филиала " + workfil + " от " + dat + ".xlsx");
}
}
static void Main(string[] args)
{
var list = new List <Protocol>();
var index = 1;
var isRight = false;
Console.WriteLine("Введите путь к файлу с протоколами ответов студентов.");
var pathToFile = Console.ReadLine();
Excel.Application exlApp = new Excel.Application();
Excel.Workbook exlWb = exlApp.Workbooks.Open(pathToFile);
var countSheets = exlWb.Sheets.Count;
for (int i = 1; i <= countSheets; i++)
{
Console.WriteLine("Обработка протокола №{0}", i);
Excel._Worksheet exlWs = exlWb.Sheets[i];
Excel.Range exlRange = exlWs.UsedRange;
for (int j = 1; j < exlRange.Rows.Count; j++)
{
if (exlRange.Cells[j, 1].Value != null)
{
if (exlRange.Cells[j, 1].Value.ToString().Trim() == "Вопрос:")
{
if (index < 10)
{
isRight = (exlRange.Cells[j, 2].Value.ToString().Substring(3, (exlRange.Cells[j, 2].Value.ToString().Trim().Length - 4)) == "Правильный ответ") ? true : false;
}
else
{
isRight = (exlRange.Cells[j, 2].Value.ToString().Substring(4, (exlRange.Cells[j, 2].Value.ToString().Trim().Length - 5)) == "Правильный ответ") ? true : false;
}
list.Add(new Protocol()
{
UID = exlRange.Cells[j + 1, 2].Value.ToString().Trim(),
IsRight = isRight
});
index++;
}
}
}
Marshal.ReleaseComObject(exlRange);
Marshal.ReleaseComObject(exlWs);
}
exlWb.Close();
exlApp.Quit();
Marshal.ReleaseComObject(exlWb);
Marshal.ReleaseComObject(exlApp);
var uniqueUID = list.Select(x => x.UID).Distinct().ToArray();
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Add();
Excel._Worksheet worksheet = workbook.Sheets[1];
Excel.Range range = worksheet.UsedRange;
range.Cells[1, 1].Value = "УИК";
range.Cells[1, 2].Value = "Правильный ответ";
range.Cells[1, 2].Style.WrapText = true;
range.Cells[1, 3].Value = "Неправильный ответ";
range.Cells[1, 3].Style.WrapText = true;
worksheet.Columns[1].ColumnWidth = 20;
worksheet.Columns[2].ColumnWidth = 25;
worksheet.Columns[3].ColumnWidth = 25;
int row = 2;
int success = 0;
int fail = 0;
bool isSuccess = false;
bool isFail = false;
foreach (var uid in uniqueUID)
{
var search = list.FindAll(x => x.UID == uid);
range.Cells[row, 1].Value = uid;
foreach (var item in search)
{
range.Cells[row, (item.IsRight) ? 2 : 3].Value = "x";
switch (item.IsRight)
{
case true:
if (!isSuccess)
{
success++;
isSuccess = true;
}
break;
case false:
if (!isFail)
{
fail++;
isFail = true;
}
break;
}
}
isSuccess = false;
isFail = false;
row++;
}
range.Cells[row, 1].Value = "ИТОГО";
range.Cells[row, 2].Value = success;
range.Cells[row, 3].Value = fail;
Console.WriteLine("Введите путь для сохранения файла с результатами обработки.");
var pathToSaveFile = Console.ReadLine();
workbook.SaveAs(string.Concat(pathToSaveFile, "NewBook.xlsx"));
workbook.Close();
excel.Quit();
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();
Console.WriteLine("Обработка протоколов завершена.");
Console.WriteLine("Для выхода из приложения нажмите любую клавишу.");
Console.ReadKey();
}
private void kurstlisten_schreiben()
{
try
{
kurslisten = myExcel.Workbooks.Add(1);
for (int kurs = 0; kurs < kurse_id.Count; kurs++)
{
add_item_to_log("Kursliste für " + kurse_name[kurs] + " wird erstellt");
Worksheet worksheet = (Worksheet)kurslisten.Worksheets.Add();
worksheet.Name = kurse_name[kurs];
createHeaders(worksheet, 1, 1, "Name", "A1", "A1", 0, true, 16);
createHeaders(worksheet, 1, 2, "Vorname", "B1", "B1", 0, true, 16);
createHeaders(worksheet, 1, 3, "Klasse", "C1", "C1", 0, true, 6);
createHeaders(worksheet, 1, 4, "Klassenlehrer", "D1", "D1", 0, true, 13);
int row = 2;
for (int schueler = 0; schueler < schueler_namen.Count; schueler++)
{
if (schueler_zuordnung[schueler] == kurse_id[kurs])
{
addData(worksheet, row, 1, schueler_namen[schueler], "A" + row, "A" + row, "");
addData(worksheet, row, 2, schueler_vornamen[schueler], "B" + row, "B" + row, "");
addData(worksheet, row, 3, schueler_klasse[schueler], "C" + row, "C" + row, "");
addData(worksheet, row, 4, schueler_klassenlehrer[schueler], "D" + row, "D" + row, "");
row++;
}
}
}
add_item_to_log("Alle Kurslisten wurden erstellt");
DateTime currentDate = DateTime.Now;
String date = withzero(currentDate.Hour) + withzero(currentDate.Minute) + withzero(currentDate.Second) + "_" + withzero(currentDate.Day) + withzero(currentDate.Month) + currentDate.Year;
document_path = new FileInfo(@tbx_path.Text).DirectoryName + "\\Wu-Einteilung_Kurslisten_" + date;
add_item_to_log("Speichere Kurslisten unter " + document_path);
kurslisten.SaveAs(@document_path);
kurslisten.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, e.Source);
}
}
/// <summary>
/// Updates Excel sheet and closes the application
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ProcessButton_Click(object sender, EventArgs e)
{
addProductButton.PerformClick();
try
{
// Opens Excel Sheet
xlApp = new Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlWorkbook = xlApp.Workbooks.Open(GlobalUtilities.getMasterFilePath(), 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
// Edits Excel Sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
for (int i = 1; i <= xlRange.Rows.Count + 10; i++)
{
if (((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, 5]).Value != null)
{
object cellValue = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, 5]).Value;
string cellValueString = cellValue.ToString();
if (GlobalUtilities.getMasterListDictionary().ContainsKey(cellValueString.TrimStart(new Char[] { '0' })) && !Regex.IsMatch(cellValueString.Replace(" ", ""), @"^[a-zA-Z]+$"))
{
List <string> tempMasterListDictionaryProduct = new List <string>(GlobalUtilities.getMasterListDictionary()[cellValueString.TrimStart(new Char[] { '0' })]);
object quantityCellValue = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, 6]).Value;
string quantityCellValueString;
if (quantityCellValue != null)
{
quantityCellValueString = quantityCellValue.ToString();
}
else
{
quantityCellValueString = "0";
}
if (!string.IsNullOrWhiteSpace(quantityCellValueString))
{
xlWorksheet.Cells[i, 6] = Double.Parse(quantityCellValueString) + Double.Parse(tempMasterListDictionaryProduct[4].ToString());
}
else
{
xlWorksheet.Cells[i, 6] = Double.Parse(tempMasterListDictionaryProduct[4].ToString());
}
}
}
}
// Saves Excel sheet
xlWorkbook.SaveAs(GlobalUtilities.getMasterFilePath());
xlWorkbook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
if (xlWorkbook != null)
{
Marshal.ReleaseComObject(xlWorkbook);
} //release each workbook like this
if (xlWorksheet != null)
{
Marshal.ReleaseComObject(xlWorksheet);
} //release each worksheet like this
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
} //release the Excel application
xlWorkbook = null; //set each memory reference to null.
xlWorksheet = null;
xlApp = null;
GC.Collect();
}
catch (Exception ex)
{
// Saves Excel sheet
xlApp.Quit();
//release all memory - stop EXCEL.exe from hanging around.
if (xlWorkbook != null)
{
Marshal.ReleaseComObject(xlWorkbook);
} //release each workbook like this
if (xlWorksheet != null)
{
Marshal.ReleaseComObject(xlWorksheet);
} //release each worksheet like this
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
} //release the Excel application
xlWorkbook = null; //set each memory reference to null.
xlWorksheet = null;
xlApp = null;
GC.Collect();
MessageBox.Show(ex.Message);
}
this.Close();
Application.Exit();
}
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);
String[] Month = new String[] { "ม.ค.", "ก.พ.", "มี.ค", "เม.ย", "พ.ค.", "มิ.ย.", "ก.ค.", "ส.ค.", "ก.ย.", "ต.ค.", "พ.ย.", "ธ.ค.", "มูลค่ารวมแต่ละปี" };
String[] Year = GetItemYear();
int count = Year.Length;
for (int i = 0; i < 13 * count; i++)
{
int mod = i % count;
if (mod == 0)
{
xlWorkSheet.Cells[1, i + 3] = Month[i / count];
}
xlWorkSheet.Range[xlWorkSheet.Cells[2, i + 3], xlWorkSheet.Cells[2, i + 3]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[2, i + 3], xlWorkSheet.Cells[2, i + 3]].EntireColumn.ColumnWidth = 12;
xlWorkSheet.Range[xlWorkSheet.Cells[2, i + 3], xlWorkSheet.Cells[2, i + 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, i + 3] = Year[mod];
}
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, (Month.Length * count)+2]].Font.Bold = true;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].EntireColumn.ColumnWidth = 15;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 1] = "รหัสสินค้า";
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[2, 2]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[2, 2]].EntireColumn.ColumnWidth = 50;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[2, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[2, 2]].VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 2] = "ชื่อสินค้า";
for (int j = 3; j < (Month.Length * count) + 2; j+=count )
{
xlWorkSheet.Range[xlWorkSheet.Cells[1, j], xlWorkSheet.Cells[1, j + count-1]].Merge();
xlWorkSheet.Range[xlWorkSheet.Cells[1, j], xlWorkSheet.Cells[1, j + count-1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
object[,] oo = new object[dt_value.Rows.Count, dt_value.Columns.Count];
for (int i = 0; i < dt_value.Rows.Count; i++)
{
xlWorkSheet.Range[xlWorkSheet.Cells[i + 3, 3], xlWorkSheet.Cells[i + 3, dt_value.Columns.Count]].NumberFormat = "#,##0.00";
}
for (int i = 0; i < dt_value.Rows.Count; i++)
{
for (int j = 0; j < dt_value.Columns.Count; j++)
{
oo[i, j] = dt_value.Rows[i][j].ToString();
}
}
string excelRange = string.Format("A3:{0}{1}",
findColumnLetter(dt_value.Columns.Count), dt_value.Rows.Count + 2);
xlWorkSheet.get_Range(excelRange, Type.Missing).Value2 = oo;
string fileName = String.Empty;
SaveFileDialog saveFileExcel = new SaveFileDialog();
saveFileExcel.FileName = "" + DBConnString.sDb + " สรุปมูลค่าการขายแต่ละปี " + TxtYear.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("สำเร็จ");
}
else
{
return;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldcul;
this.Cursor = Cursors.Default;
}
}
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);
}
public bool exportTable()
{
try
{
//Подготовка
excel = new InteropExcel.ApplicationClass();
if (excel == null)
{
return(false);
}
excel.Visible = false;
InteropExcel.Workbook workbook = excel.Workbooks.Add();
if (workbook == null)
{
return(false);
}
InteropExcel.Worksheet sheet = (InteropExcel.Worksheet)workbook.Worksheets [1];
sheet.Name = "Таблица1";
//Попълване на таблицата
int i = 1;
addRow(new DataRow("Първо име", "Фамилия", "Години"), i++, true, 50); ++i;
foreach (DataRow row in _data.table)
{
addRow(row, i++, false, -1);
}
++i; addRow(new DataRow("Брой редове", "", _data.table.Count.ToString()), i++, true, -1);
//Запаметяване и затваряне
workbook.SaveAs(getPath());
excel.DisplayAlerts = false; //Изключваме всички съобщения на Excel
workbook.Close();
excel.Quit();
//Освобождаване на паметта от Excel
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
if (excel != null)
{
Marshal.ReleaseComObject(excel);
}
workbook = null;
sheet = null;
excel = null;
GC.Collect();
return(true);
}catch {
}
return(false);
}
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;
}
}
public async Task <object> GenerateReoprtAsync(dynamic input)
{
try
{
string strFilePath = (string)input.filename;
dynamic sheets = (dynamic)input.sheets;
excelapp = new Excel.Application();
Excel.Workbooks wbs = excelapp.Workbooks;
Excel.Workbook wbResultReoprt = CreateWorkbook(strFilePath, excelapp);
if (null != wbResultReoprt)
{
//if (sheets.Length > 0)
if (sheets.Count > 0)
{
foreach (dynamic sheet in sheets)
{
string sheetname = (string)sheet.name;
dynamic content = (dynamic)sheet.content;
HandleWorksheet(sheetname, wbResultReoprt, content);
}
}
Excel.Worksheet wsStab = wbResultReoprt.Worksheets[1];
Excel.Shape shape = wsStab.Shapes.AddChart(XlChartType.xl3DArea, 25, 25, 400, 300);
Excel.Shape shape1 = wsStab.Shapes.AddChart(XlChartType.xlBarStacked, 450, 25, 400, 300);
Excel.Chart chart1 = shape.Chart;
chart1.HasTitle = true;
chart1.ChartTitle.Text = "test";
chart1.ChartTitle.Interior.Color = "Red";
wbResultReoprt.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
CloseWorkbook(wbResultReoprt);
excelapp.Quit();
GC.Collect();
KillExcelProcess.Kill(excelapp);
return(true);
}
return(false);
}
catch (Exception e)
{
throw e;
if (null != excelapp)
{
excelapp.Quit();
GC.Collect();
KillExcelProcess.Kill(excelapp);
}
//File.AppendAllText(@"C:\Users\97901\Project\ZD Data Logger", e.Message);
return(false);
}
}
private void BtnExcel_Click(object sender, EventArgs e)
{
if (dgvHienThi.Rows.Count > 0) //TH co du lieu de ghi
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Dinh dang chung
Excel.Range tenExcel = (Excel.Range)exSheet.Cells[1, 1];
tenExcel.Font.Size = 14;
tenExcel.Font.Bold = true;
tenExcel.Font.Color = Color.Blue;
tenExcel.Value = "Form Nhân Viên";
Excel.Range header = (Excel.Range)exSheet.Cells[3, 1];
exSheet.get_Range("A3:F3").Merge(true);
header.Font.Size = 16;
header.Font.Bold = true;
header.Font.Color = Color.Red;
exSheet.get_Range("A3:F3").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
header.Value = "DANH SÁCH NHÂN VIÊN";
//Dinh dang tieu de excel
exSheet.get_Range("A4:F4").Font.Bold = true;
exSheet.get_Range("A4:F4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A4").Value = "STT";
exSheet.get_Range("A4").BorderAround2();
char ascii = (char)65; //A=65
for (int i = 0; i < dgvHienThi.Columns.Count; i++)
{
ascii++;
exSheet.get_Range(ascii + "4").Value = dgvHienThi.Columns[i].HeaderText.ToString();
exSheet.get_Range(ascii + "4").ColumnWidth = 15;
exSheet.get_Range(ascii + "4").BorderAround2();
}
//Nhap thong so trong datagridview
for (int i = 0; i < dgvHienThi.Rows.Count - 1; i++)
{
exSheet.get_Range("A" + (i + 5).ToString()).Font.Bold = false;
exSheet.get_Range("A" + (i + 5).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A" + (i + 5).ToString()).Value = (i + 1).ToString();
exSheet.get_Range("A" + (i + 5).ToString()).BorderAround2();
ascii = (char)66;
for (int j = 0; j < dgvHienThi.Columns.Count; j++)
{
exSheet.get_Range(ascii + (i + 5).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
exSheet.get_Range(ascii + (i + 5).ToString()).Value = dgvHienThi.Rows[i].Cells[j].Value;
exSheet.get_Range(ascii + (i + 5).ToString()).BorderAround2();
ascii++;
}
}
//exSheet.get_Range((char)65 + "4:" + (char)(65 + dgvHienThi.Columns.Count) + (dgvHienThi.Rows.Count + 3)).BorderAround2();
exSheet.Name = "Nhân Viên";
exBook.Activate(); //kich hoat file excel
//Thiet lap thuoc tinh savefiledialog
//saveExcel.Filter = "Excel Document(*.xls)";
//saveExcel.FilterIndex = 0;
//saveExcel.AddExtension = true;
saveExcel.DefaultExt = ".xls";
if (saveExcel.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(saveExcel.FileName.ToString()); //Luu file Excel
exApp.Visible = true;
}
else
{
exApp.Quit();
}
}
else
{
MessageBox.Show("Không có danh sách để in");
}
}
private void btExcel_Click(object sender, EventArgs e)
{
try
{
int Count_ColumnGridView = dgvStockCard.Columns.Count;
int Count_RowsGridView = dgvStockCard.Rows.Count;
int numRows = dgvStockCard.Rows.Count;
int j = 0;
int i = 0;
var Oldcul = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat = Oldcul.DateTimeFormat;
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.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).Font.Bold = true;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, Count_ColumnGridView]).VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).Font.Name = "Tahoma";
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).Font.Size = 11;
xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[Count_RowsGridView + 1, Count_ColumnGridView]).EntireColumn.AutoFit();
if (numRows == 0)
{
MessageBox.Show("ไม่สามารถ Export ได้ เนื่องจากไม่มีข้อมูล", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
object[,] oo = new object[Dt.Rows.Count, Dt.Columns.Count];
for (j = 0; j <= dgvStockCard.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[1, j + 1] = dgvStockCard.Columns[j].HeaderText;
}
for (i = 0; i < Dt.Rows.Count; i++)
{
for (j = 0; j < Dt.Columns.Count; j++)
{
oo[i, j] = Dt.Rows[i][j].ToString();
}
}
string excelRange = string.Format("A2:{0}{1}",
findColumnLetter(Dt.Columns.Count), Dt.Rows.Count + 1);
xlWorkSheet.get_Range(excelRange, Type.Missing).Value2 = oo;
SaveFileDialog sfd = new SaveFileDialog();
string fileName = String.Empty;
sfd.Filter = "Excel files (*.xls,*.xlsx)|*.xls*";
sfd.FileName = pid + "_" + DateTime.Now.ToString("dd-MM-yyyy", new CultureInfo("en-US"));
sfd.FilterIndex = 2;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.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 " + sfd.FileName + " Complete.");
}
}
}
catch
{
MessageBox.Show("บันทึกไฟล์ Excel ไม่สำเร็จ");
}
}
/// <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 static void Average(string input, string output)
{
if (File.Exists(output + ".xlsx"))
{
File.Delete(output + ".xlsx");
}
Console.WriteLine("Average");
Excel.Application excel = new Excel.Application();
Excel.Workbook sheet = excel.Workbooks.Open(input);
Excel.Worksheet x = excel.ActiveSheet as Excel.Worksheet;
x.Cells[1, 5] = "KPI_1_Average";
x.Cells[1, 7] = "0.5";
x.Range["F1"].Formula = "=AVERAGEIF(B:BT,\"KPI_1_PageTransition_results\",D:D)";
x.Cells[2, 5] = "KPI_2_ZapTime";
x.Cells[2, 7] = "3.5";
x.Range["F2"].Formula = "=AVERAGEIF(B:BT,\"KPI_2_ZapTime_results\",D:D)";
x.Cells[3, 5] = "KPI_3_LinearEPGDetailPage";
x.Cells[3, 7] = "1.0";
x.Range["F3"].Formula = "=AVERAGEIF(B:BT,\"KPI_3_LinearEPGDetailPage_results\",D:D)";
x.Cells[4, 5] = "KPI_8_MenuDisplayTime";
x.Cells[4, 7] = "0.6";
x.Range["F4"].Formula = "=AVERAGEIF(B:BT,\"KPI_8_MenuDisplayTime_results\",D:D)";
x.Cells[5, 5] = "KPI_14_Average";
x.Cells[5, 7] = "2.0";
x.Range["F5"].Formula = "=AVERAGEIF(B:BT,\"KPI_14_AverageEPGNavigation_results\",D:D)";
x.Cells[6, 5] = "KPI_15_Average";
x.Cells[6, 7] = "1.5";
x.Range["F6"].Formula = "=AVERAGEIF(B:BT,\"KPI_15_AverageEPGPageChange_results\",D:D)";
x.Cells[7, 5] = "KPI_16_Average";
x.Cells[7, 7] = "1.0";
x.Range["F7"].Formula = "=AVERAGEIF(B:BT,\"KPI_16_AverageEPGMenu_results\",D:D)";
x.Cells[8, 5] = "KPI_19_AverageSearchTime";
x.Cells[8, 7] = "0.1";
x.Range["F8"].Formula = "=AVERAGEIF(B:BT,\"KPI_19_AverageSearchTime_results\",D:D)";
x.Cells[9, 5] = "KPI_20_AverageSearchNavigation";
x.Cells[9, 7] = "1.0";
x.Range["F9"].Formula = "=AVERAGEIF(B:BT,\"KPI_20_AverageSearchNavigation_results\",D:D)";
x.Cells[10, 5] = "KPI_25_ColdBoot_UntilVisualFeedback";
x.Cells[10, 7] = "70.0";
x.Range["F10"].Formula = "=AVERAGEIF(B:BT,\"KPI_25_ColdBoot_UntilVisualFeedback_results\",D:D)";
x.Cells[11, 5] = "KPI_26_ColdBoot_UntilStream";
x.Cells[11, 7] = "85.0";
x.Range["F11"].Formula = "=AVERAGEIF(B:BT,\"KPI_26_ColdBoot_UntilStream_results\",D:D)";
x.Cells[12, 5] = "KPI_27_ColdBoot_UntilHomepage";
x.Cells[12, 7] = "80.0";
x.Range["F12"].Formula = "=AVERAGEIF(B:BT,\"KPI_27_ColdBoot_UntilHomepage_results\",D:D)";
x.Cells[13, 5] = "KPI_28_Average";
x.Cells[13, 7] = "2.0";
x.Range["F13"].Formula = "=AVERAGEIF(B:BT,\"KPI_28_Standby_UntilVisualFeedback_results\",D:D)";
x.Cells[14, 5] = "KPI_29_Standby";
x.Cells[14, 7] = "10.0";
x.Range["F14"].Formula = "=AVERAGEIF(B:BT,\"KPI_29_Standby_UntilHome_results\",D:D)";
x.Cells[15, 5] = "KPI_30_Standby";
x.Cells[15, 7] = "15.0";
x.Range["F15"].Formula = "=AVERAGEIF(B:BT,\"KPI_30_Standby_UntilStream_results\",D:D)";
x.Cells[16, 5] = "ZAP_Video";
x.Cells[16, 7] = "1.2";
x.Range["F16"].Formula = "=AVERAGEIF(B:BT,\"KPI_2_ZapTimeNew_video_results\",D:D)";
x.Cells[17, 5] = "ZAP_Audio";
x.Cells[17, 7] = "3.0";
x.Range["F17"].Formula = "=AVERAGEIF(B:BT,\"KPI_2_ZapTimeNew_audio_results\",D:D)";
sheet.SaveAs(output + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Local: Type.Missing);
File.Delete(input);
excel.Quit();
}
