/// <summary>
/// 打开一个Excel文件
/// </summary>
/// <param name="FileName"></param>
public static void Open( string fileName )//
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add( fileName );
ws = wb.Worksheets[ 1 ] as Worksheet;
mFilename = fileName;
}
public Export()
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
//for writing
public ExcelOperator()
{
ExcelApp = new Excel.Application();
workbooks = ExcelApp.Workbooks;
workbook = workbooks.Add();
sheets = workbook.Sheets;
worksheet = sheets[1];
worksheet.Select(Type.Missing);
directoryPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
fileName = "shift.xlsx";
}
public ExcelWriter(string newSaveFile, string newExam)
{
saveFile = newSaveFile;
exam = newExam;
row = 2;
questionNumber = 1;
xlApp = new Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true; // Turn this to true if you want to see the program in the foreground
wbs = xlApp.Workbooks;
wb = wbs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
ws = (Excel.Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
ws.Cells[1, 1].EntireRow.Font.Bold = true;
// Setup the labels
ws.Cells[1, 1].Value2 = "Code";
ws.Cells[1, 2].Value2 = "Label";
ws.Cells[1, 3].Value2 = "ParentItemRef";
ws.Cells[1, 4].Value2 = "ItemType";
ws.Cells[1, 5].Value2 = "ItemLevelScore";
ws.Cells[1, 6].Value2 = "ItemCorrectMarks";
ws.Cells[1, 7].Value2 = "ItemWrongMarks";
ws.Cells[1, 8].Value2 = "Difficulty";
ws.Cells[1, 9].Value2 = "Classification";
ws.Cells[1, 10].Value2 = "Experience";
ws.Cells[1, 11].Value2 = "Language";
ws.Cells[1, 12].Value2 = "Shuffle";
ws.Cells[1, 13].Value2 = "NoOfOptions";
ws.Cells[1, 14].Value2 = "CorrectOption";
ws.Cells[1, 15].Value2 = "ItemText";
ws.Cells[1, 16].Value2 = "ItemImage";
ws.Cells[1, 17].Value2 = "ItemRationale";
int col = 18;
for (int o = 1; o <= 10; o++, col += 3)
{
ws.Cells[1, col].Value2 = "Option" + o;
ws.Cells[1, col + 1].Value2 = "Option" + o + "_Image";
ws.Cells[1, col + 2].Value2 = "Option" + o + "_Rationale";
}
}
/// <summary>
/// 创建一个Excel对象
/// </summary>
public static void Create()
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add( true );
}
public void OutputAsExcelFile(DataGridView dataGridView)
{
if (dataGridView.Rows.Count <= 0)
{
MessageBox.Show("无数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
string filePath = "";
SaveFileDialog s = new SaveFileDialog();
s.Title = "保存Excel文件";
s.Filter = "Excel文件(*.xls)|*.xls";
s.FilterIndex = 1;
if (s.ShowDialog() == DialogResult.OK)
{
filePath = s.FileName;
}
else
{
return;
}
//第一步：将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列
DataTable tmpDataTable = new DataTable("tmpDataTable");
DataTable modelTable = new DataTable("ModelTable");
for (int column = 0; column < dataGridView.Columns.Count; column++)
{
if (dataGridView.Columns[column].Visible == true)
{
DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string));
tmpDataTable.Columns.Add(tempColumn);
DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string));
modelTable.Columns.Add(modelColumn);
}
}
for (int row = 0; row < dataGridView.Rows.Count; row++)
{
if (dataGridView.Rows[row].Visible == false)
{
continue;
}
DataRow tempRow = tmpDataTable.NewRow();
for (int i = 0; i < tmpDataTable.Columns.Count; i++)
{
tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value;
}
tmpDataTable.Rows.Add(tempRow);
}
if (tmpDataTable == null)
{
return;
}
//第二步：导出dataTable到Excel
long rowNum = tmpDataTable.Rows.Count; //行数
int columnNum = tmpDataTable.Columns.Count; //列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 10;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
//Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
//worksheet.Name = "dd";
//m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
m_xlApp.Visible = false;
// = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
workbook.Saved = true;
workbook.SaveCopyAs(filePath);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
//EndReport();
}
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void WritingInExcMethod <T>(Dictionary <T, int>[] arrInp, Dictionary <T, int>[] arrOut)
{
Excel.Application ObjExcel = new Excel.Application();
ObjExcel.Visible = true;
Excel.Workbooks ObjWorkBooks = null;
Excel.Workbook ObjWorkBook = null;
Excel.Worksheet ObjWorkSheet1 = null;
Excel.Worksheet ObjWorkSheet2 = null;
try
{
ObjWorkBooks = ObjExcel.Workbooks;
ObjWorkBook = ObjWorkBooks.Add(System.Reflection.Missing.Value);
ObjWorkSheet1 = (Excel.Worksheet)ObjWorkBook.Sheets[1];
int k = ObjWorkBook.Sheets.Count;
ObjWorkBook.Sheets.Add(After: ObjWorkBook.Sheets.Add(After: (Excel.Worksheet)ObjWorkBook.Sheets[k]), Count: (7 - k));
ObjWorkSheet1.Name = "Общее";
{
int colShift = 0;
int vShift = 2;
for (int i = 0; i < arrInp.Length; i++)//проход по массиву dictionary
{
var dict1 = arrInp[i];
int row = 1, col = 3 * i + 1 + colShift;
var data = new object[arrInp[i].Count + vShift, 2];
foreach (T key in dict1.Keys)
{
data[row - 1 + vShift, 0] = dict1[key].ToString();
data[row - 1 + vShift, 1] = key.ToString();
row++;
}
//заголовки
if (i % 2 == 0)
{
data[0, 0] = "Анализируемый список елементов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[2];
ObjWorkSheet2.Name = "\"Где\"";
}
else
{
data[0, 0] = "Список разыскиваемых елементов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[3];
ObjWorkSheet2.Name = "\"Что\"";
}
data[1, 0] = "кол-во";
data[1, 1] = "елемент";
Excel.Range range1 = ObjWorkSheet1.Range[ObjWorkSheet1.Cells[1, col], ObjWorkSheet1.Cells[1, col + 1]];
Excel.Range range2 = ObjWorkSheet2.Range[ObjWorkSheet2.Cells[1, 1], ObjWorkSheet2.Cells[1, 2]];
range1.Merge(); //объеденение 2 ячеек
range2.Merge();
//создать диапазон(Range)
var startCell1 = ObjWorkSheet1.Cells[1, col];
var endCell1 = ObjWorkSheet1.Cells[arrInp[i].Count + vShift, col + 1];
var writeRange1 = ObjWorkSheet1.Range[startCell1, endCell1];
var startCell2 = ObjWorkSheet2.Cells[1, 1];
var endCell2 = ObjWorkSheet2.Cells[arrInp[i].Count + vShift, 2];
var writeRange2 = ObjWorkSheet2.Range[startCell2, endCell2];
//запись данных в диапазон
writeRange1.Value2 = data;
writeRange2.Value2 = data;
startCell1 = null;
endCell1 = null;
writeRange1 = null;
range1 = null;
startCell2 = null;
endCell2 = null;
writeRange2 = null;
range2 = null;
}
}
{//записали результат-4 новые таблицы
int colShift = arrInp.Length * 3;
int vShift = 2;
for (int i = 0; i < arrOut.Length; i++)//проход по массиву dictionary
{
var dict1 = arrOut[i];
int row = 1, col = 3 * i + 1 + colShift;
var data = new object[arrOut[i].Count + vShift, 2];
foreach (T key in dict1.Keys)
{
data[row - 1 + vShift, 0] = dict1[key].ToString();
data[row - 1 + vShift, 1] = key.ToString();
row++;
}
//заголовки
string str;
switch (i)
{
case 0:
str = "Аргументы из списка разыскиваемых аргументов найденые в анализируемом списке аргументов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[4];
ObjWorkSheet2.Name = "\"Где\" совпад";
break;
case 1:
str = "Не запрошенные аргументы из анализируемого списка аргументов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[5];
ObjWorkSheet2.Name = "\"Где\" не совпад";
break;
case 2:
str = "Аргументы из списка разыскиваемых аргументов НАЙДЕНЫЕ в анализируемом списке аргументов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[6];
ObjWorkSheet2.Name = "\"Что\" совпад";
break;
case 3:
str = "Аргументы из списка разыскиваемых аргументов НЕ найденые в анализируемом списке аргументов";
ObjWorkSheet2 = (Excel.Worksheet)ObjWorkBook.Sheets[7];
ObjWorkSheet2.Name = "\"Что\" не совпад";
break;
default:
str = "";
break;
}
data[0, 0] = str;
data[1, 0] = "кол-во";
data[1, 1] = "елемент";
//объеденение 2 ячеек
Excel.Range range1 = ObjWorkSheet1.Range[ObjWorkSheet1.Cells[1, col], ObjWorkSheet1.Cells[1, col + 1]];
Excel.Range range2 = ObjWorkSheet2.Range[ObjWorkSheet2.Cells[1, 1], ObjWorkSheet2.Cells[1, 2]];
range1.Merge(); //объеденение 2 ячеек
//создать диапазон(Range)
var startCell1 = ObjWorkSheet1.Cells[1, col];
var endCell1 = ObjWorkSheet1.Cells[arrOut[i].Count + vShift, col + 1];
var writeRange1 = ObjWorkSheet1.Range[startCell1, endCell1];
var startCell2 = ObjWorkSheet2.Cells[1, 1];
var endCell2 = ObjWorkSheet2.Cells[arrOut[i].Count + vShift, 2];
var writeRange2 = ObjWorkSheet2.Range[startCell2, endCell2];
//запись данных в диапазон
writeRange1.Value2 = data;
writeRange2.Value2 = data;
startCell1 = null;
endCell1 = null;
writeRange1 = null;
range1 = null;
startCell2 = null;
endCell2 = null;
writeRange2 = null;
range2 = null;
}
}
ObjWorkBook.SaveAs(path + @"\rezult.xlsx"); //сохранить файл excel
AddToTextBox(textBox1, "Created file:\r\n" + path + @"\rezult.xlsx"); //записать в лог
}
catch (Exception exc)
{
MessageBox.Show("Ошибка при составлении лога\n" + exc.Message);
}
finally
{
//ObjExcel.Quit();
ObjWorkBook.Sheets[1].Activate();
ObjExcel = null;
ObjWorkBooks = null;
ObjWorkBook = null;
ObjWorkSheet1 = null;
GC.Collect();
}
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
object m_objOpt = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbooks ExcelBooks = (Microsoft.Office.Interop.Excel.Workbooks)ExcelApp.Workbooks;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)(ExcelBooks.Add(m_objOpt));
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
ExcelApp.Caption = "Test"; //设置标题
for (int i = 1; i <= listView1.Columns.Count; i++)
{
ExcelSheet.Cells[2, i] = listView1.Columns[i - 1].Text;
}
for (int i = 3; i < listView1.Items.Count + 3; i++)
{
ExcelSheet.Cells[i, 1] = listView1.Items[i - 3].Text;
for (int j = 2; j <= listView1.Columns.Count; j++)
{
ExcelSheet.Cells[i, j] = listView1.Items[i - 3].SubItems[j - 1].Text;
}
}
ExcelApp.Visible = true;
}
catch (SystemException sysE) { MessageBox.Show(sysE.ToString()); }
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
private void PrepareRecord()
{
objRecordExcel = new Excel.Application();
objRecordBooks = objRecordExcel.Workbooks;
if (File.Exists(txtStorage.Text))
objRecordBook = objRecordBooks.Open(txtStorage.Text);
else
objRecordBook = objRecordBooks.Add(true);
objRecordSheets = objRecordBook.Sheets;
objRecordSheet1 = objRecordSheets[1]; // 收件箱
objRecordSheet2 = objRecordSheets[1]; // 已发短信
bool bSheet1Exists = false, bSheet2Exists = false;
foreach (Excel.Worksheet objSheet in objRecordSheets)
{
if (objSheet.Name == "收件箱")
{
objRecordSheet1 = objSheet;
bSheet1Exists = true;
}
else if (objSheet.Name == "已发短信")
{
objRecordSheet2 = objSheet;
bSheet2Exists = true;
}
}
if (!bSheet1Exists)
{
objRecordSheet1 = objRecordSheets.Add();
objRecordSheet1.Name = "收件箱";
objRecordSheet1.Cells[1, 1] = "发件人";
objRecordSheet1.Cells[1, 2] = "短信内容";
objRecordSheet1.Cells[1, 3] = "发送时间";
}
if (!bSheet2Exists)
{
objRecordSheet2 = objRecordSheets.Add();
objRecordSheet2.Name = "已发短信";
objRecordSheet2.Cells[1, 1] = "收件人";
objRecordSheet2.Cells[1, 2] = "短信内容";
objRecordSheet2.Cells[1, 3] = "发送时间";
objRecordSheet2.Cells[1, 4] = "结果";
objRecordSheet2.Cells[1, 5] = "余额";
}
}
private void excel表格EToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
string fileName = "";
string saveFileName = "";
saveFileDialog1.Filter = "Excel文件(*.xls) | *.xls |Microsoft Excel文件(*.xlsx) |*.xlsx";
saveFileDialog1.Title = "保存学生信息";
saveFileDialog1.FileName = fileName;
saveFileDialog1.ShowDialog();
saveFileName = saveFileDialog1.FileName;
#region 利用Excel组件导出数据
if (saveFileName.IndexOf(":") < 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("请安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//写标题
for (int i = 0; i < dGVBooksInfo.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dGVBooksInfo.Columns[i].HeaderText;
}
//写数值
for (int r = 0; r < dGVBooksInfo.Rows.Count; r++)
{
for (int i = 0; i < dGVBooksInfo.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dGVBooksInfo.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
//列宽自适应
worksheet.Columns.EntireColumn.AutoFit();
MessageBox.Show(fileName + "学生信息保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件出错，请再次尝试\n", ex.Message);
}
}
//xlApp.Quit();
//强行销毁
//GC.Collect();
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Create()//创建一个Excel对象
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
public static void makexls(string dir)
{
Dictionary <string, string> gjgtype = new Dictionary <string, string>();
gjgtype.Add("101", "柱梁节点（框架）");
gjgtype.Add("102", "柱梁节点（门钢）");
gjgtype.Add("103", "主次梁节点");
//gjgtype.Add("104", "系杆与柱梁节点");
gjgtype.Add("105", "托柱、托梁节点");
gjgtype.Add("106", "支撑与支撑节点");
gjgtype.Add("107", "H型钢桁架节点");
gjgtype.Add("108", "柱与支撑节点");
gjgtype.Add("109", "梁与支撑节点");
gjgtype.Add("110", "柱与柱对接节点");
gjgtype.Add("111", "系杆节点");
gjgtype.Add("112", "檩条节点");
gjgtype.Add("120", "其他节点");
gjgtype.Add("201", "柱脚");
gjgtype.Add("202", "柱帽、柱顶");
gjgtype.Add("203", "牛腿、肩梁细部");
gjgtype.Add("204", "吊车梁车挡");
gjgtype.Add("205", "加劲肋、隔板");
gjgtype.Add("206", "柱身环板");
gjgtype.Add("207", "拼接段");
gjgtype.Add("208", "过渡段");
gjgtype.Add("209", "人孔、管道孔");
gjgtype.Add("210", "补强板");
gjgtype.Add("211", "加工工艺版");
gjgtype.Add("212", "吊装工艺板");
gjgtype.Add("213", "预埋件节点");
gjgtype.Add("299", "其他细部");
gjgtype.Add("301", "格构柱");
gjgtype.Add("302", "转换桁架");
gjgtype.Add("303", "巨型柱");
gjgtype.Add("304", "钢楼梯");
gjgtype.Add("305", "钢爬梯");
gjgtype.Add("306", "电梯井");
gjgtype.Add("307", "型钢桁架");
gjgtype.Add("308", "组合吊车梁");
gjgtype.Add("309", "钢板剪力墙");
gjgtype.Add("310", "天窗架");
gjgtype.Add("311", "异形构件");
gjgtype.Add("312", "其他");
gjgtype.Add("313", "柱间支撑");
gjgtype.Add("314", "组合门架");
gjgtype.Add("315", "女儿墙柱");
gjgtype.Add("316", "抗风柱");
int wrong = 0;
Excel.Application xls = new Excel.Application();
xls.Caption = "节点、细部、参数化构件统计表";
Excel.Workbooks wbs = xls.Workbooks;
Excel.Workbook wb = wbs.Add();
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws.Name = "节点";
xls.Visible = true;
DirectoryInfo dirinfo = new DirectoryInfo(dir);
FileInfo[] png1 = dirinfo.GetFiles("1.png", SearchOption.AllDirectories);
ws.Cells[1, 1] = "编号";
ws.Cells[1, 2] = "名称";
ws.Cells[1, 3] = "配图";
ws.Cells[1, 4] = "分类";
ws.Cells[1, 5] = "状态";
ws.Cells[1, 6] = "备注";
int rownum = 2;
Excel.Range r2 = ws.Cells[1, 3];
Excel.Range r3 = ws.Cells[1, 2];
r3.EntireColumn.ColumnWidth = 50;
r2.EntireColumn.ColumnWidth = 145 * 0.035267 / 0.214975;
Excel.Range r4 = ws.Cells[1, 4];
r4.EntireColumn.ColumnWidth = 30;
foreach (FileInfo png in png1)
{
//编号：文件夹名称
//名称：gjgsn名称
//作者：已添加
//分类，由编号获取，允许未定义
//入库，未测试手动添加
try
{
DirectoryInfo jd = png.Directory;
ws.Cells[rownum, 1] = jd.Name;
ws.Cells[rownum, 2] = Path.GetFileNameWithoutExtension((jd.GetFiles("*.gjgsn"))[0].Name);
Excel.Range ran = ws.Cells[rownum, 3];
Excel.Shape tu = ws.Shapes.AddPicture((jd.GetFiles("1.png"))[0].FullName, MsoTriState.msoTrue, MsoTriState.msoTrue, (float)(ran.Left + 3), (float)(ran.Top + 2), 140, 160);
ran.EntireRow.RowHeight = 165;
try
{ ws.Cells[rownum, 4] = gjgtype[jd.Name.Substring(0, 3)]; }
catch
{ ws.Cells[rownum, 4] = "未识别的分类"; }
StreamReader gjg = new StreamReader((jd.GetFiles("*.gjgsn"))[0].FullName, System.Text.Encoding.UTF8);
string gjgread = gjg.ReadToEnd();
try
{
JObject jo = JObject.Parse(gjgread);
ws.Cells[rownum, 5] = jo["model"]["Attachs"]["Author"].ToString() + ",";
}
catch
{
ws.Cells[rownum, 5] = "未署名,";
}
gjg.Close();
//ws.Cells[rownum, 6] ="已入库";
}
catch
{
wrong++;
}
rownum++;
}
if (wrong != 0)
{
MessageBox.Show(wrong.ToString());
}
}
//private void logFichasAbertasExcel(DateTime pDate, string pTime, int pFichas)
//{
// Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// Excel.Workbooks oWBs = oXL.Workbooks;
// Excel._Workbook oWB;
// Excel._Worksheet oSheet;
// string a = "1";
// if (File.Exists(@"LogFichasAbertas.xlsx"))
// oWB = oWBs.Open(@"LogFichasAbertas.xlsx");
// else
// oWB = oWBs.Add(Type.Missing);
// a = "2";
// oSheet = oWB.ActiveSheet;
// a = "3";
// Excel.Range r = oSheet.Range["A:A"];
// Excel.Range found = null;
// Excel.Range endRange = null;
// Excel.Range rowRange = null;
// Excel.Range cells = oSheet.Cells;
// Excel.Range cellDate = null;
// Excel.Range cellFicha = null;
// Excel.Font font = null;
// a = "4";
// try
// {
// int col = 2;
// a = "5";
// found = r.Find(pDate.ToString("yyMMdd"), SearchOrder: Excel.XlSearchOrder.xlByRows, LookIn: Excel.XlFindLookIn.xlValues);
// if (found == null)
// {
// a = "6";
// endRange = r.End[Excel.XlDirection.xlToRight];
// col = endRange.Column > 16000 ? 2 : endRange.Column + 1;
// a = "7";
// cellDate = cells[1, col];
// cellDate.Value = pDate.ToString("yyMMdd");
// font = cellDate.Font;
// font.Bold = true;
// cellDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// a = "8";
// }
// else
// col = found.Column;
// a = "9";
// rowRange = r.Find(pTime, SearchOrder: Excel.XlSearchOrder.xlByColumns);
// int row = 14;// rowRange.Row;
// a = "10";
// cellFicha = cells[row, col];
// cellFicha.Value = pFichas;
// a = "11";
// oXL.DisplayAlerts = false;
// oWB.SaveAs(@"LogFichasAbertas.xlsx", ConflictResolution: Excel.XlSaveConflictResolution.xlLocalSessionChanges);
// eventLog1.WriteEntry("SUCCESS! logFichasAbertasExcel - " + a, EventLogEntryType.Error, 10);
// }
// catch (Exception ex)
// {
// eventLog1.WriteEntry("ERROR: logFichasAbertasExcel - " + a + " -- " +ex.Message, EventLogEntryType.Error, 10);
// }
// finally
// {
// oWB.Close();
// oWBs.Close();
// oXL.Quit();
// releaseObject(font);
// releaseObject(found);
// releaseObject(r);
// releaseObject(cells);
// releaseObject(cellFicha);
// releaseObject(cellDate);
// releaseObject(endRange);
// releaseObject(rowRange);
// releaseObject(oSheet);
// releaseObject(oWB);
// releaseObject(oWBs);
// releaseObject(oXL);
// }
//}
//private void releaseObject(object obj)
//{
// if (obj == null) return;
// try
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
// obj = null;
// }
// catch (Exception ex)
// {
// obj = null;
// eventLog1.WriteEntry("ERROR: releaseObject - " + ex.Message, EventLogEntryType.Error, 10);
// }
// finally
// {
// GC.Collect();
// }
//}
private void logFichasAbertasExcel(DateTime pDate, string pTime, int pFichas)
{
Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
if (File.Exists(@"D:\Avenca\LogFichasAbertas.xlsx"))
{
oWB = oWBs.Open(@"D:\Avenca\LogFichasAbertas.xlsx");
}
else
{
oWB = oWBs.Add(Type.Missing);
}
oSheet = oWB.ActiveSheet;
Excel.Range r = oSheet.Range["A1"];
Excel.Range found = null;
Excel.Range endRange = null;
Excel.Range rowRange = null;
Excel.Range cells = oSheet.Cells;
Excel.Range cellDate = null;
Excel.Range cellFicha = null;
Excel.Font font = null;
try
{
int col = 2;
found = r.Find(pDate.ToString("yyMMdd"), SearchOrder: Excel.XlSearchOrder.xlByRows, LookIn: Excel.XlFindLookIn.xlValues);
if (found == null)
{
endRange = r.End[Excel.XlDirection.xlToRight];
col = endRange.Column > 16000 ? 2 : endRange.Column + 1;
cellDate = cells[1, col];
cellDate.Value = pDate.ToString("yyMMdd");
font = cellDate.Font;
font.Bold = true;
cellDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
else
{
col = found.Column;
}
rowRange = r.Find(pTime, SearchOrder: Excel.XlSearchOrder.xlByColumns);
int row = rowRange.Row;
cellFicha = cells[row, col];
cellFicha.Value = pFichas;
oXL.DisplayAlerts = false;
oWB.SaveAs(@"D:\Avenca\LogFichasAbertas.xlsx", ConflictResolution: Excel.XlSaveConflictResolution.xlLocalSessionChanges);
}
catch (Exception ex)
{
eventLog1.WriteEntry("ERROR: logFichasAbertasExcel - " + ex.Message, EventLogEntryType.Error, 10);
}
finally
{
oWB.Close();
oWBs.Close();
oXL.Quit();
releaseObject(font);
releaseObject(found);
releaseObject(r);
releaseObject(cells);
releaseObject(cellFicha);
releaseObject(cellDate);
releaseObject(endRange);
releaseObject(rowRange);
releaseObject(oSheet);
releaseObject(oWB);
releaseObject(oWBs);
releaseObject(oXL);
}
}
public bool ExportToExcel()
{
if (File.Exists(_filename))
{
File.Delete(_filename);
}
Excel.Application m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
Excel.Workbooks m_objBooks = m_objExcel.Workbooks;
Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.Add(Type.Missing);
Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets;
Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(1);
int colindex = 0;
for (int j = 0; j < _exportGrid.ColumnCount; j++)
{
if (_exportGrid.Columns[j].Visible)
{
colindex++;
m_objSheet.Cells[1, colindex] = _exportGrid.Columns[j].HeaderText;
}
}
if (_exportGrid.SelectedRows.Count == 0)
{
for (int i = 0; i < _exportGrid.RowCount; i++)
{
colindex = 0;
for (int j = 0; j < _exportGrid.ColumnCount; j++)
{
if (_exportGrid.Columns[j].Visible)
{
colindex++;
if (_exportGrid.Rows[i].Cells[j].Value != null)
{
m_objSheet.Cells[i + 2, colindex] = _exportGrid.Rows[i].Cells[j].Value.ToString();
}
}
}
}
}
else
{
for (int i = 0; i < _exportGrid.SelectedRows.Count; i++)
{
colindex = 0;
for (int j = 0; j < _exportGrid.ColumnCount; j++)
{
if (_exportGrid.Columns[j].Visible)
{
colindex++;
if (_exportGrid.Rows[i].Cells[j].Value != null)
{
m_objSheet.Cells[i + 2, colindex] = _exportGrid.SelectedRows[i].Cells[j].Value.ToString();
}
}
}
}
}
m_objSheet.Activate();
System.Windows.Forms.Application.DoEvents();
m_objExcel.DisplayAlerts = false;
m_objExcel.AlertBeforeOverwriting = false;
//保存工作簿
m_objBook.SaveAs(_filename);
m_objBook.Close();
return(true);
}
/// <summary>
/// Converts Расписашка (*.db) database file with schedule to Microsoft Office Excel 2010 (*.xlsx) workbook file
/// </summary>
/// <param name="path">full path to Расписашка (*.db) database file with schedule</param>
/// <returns>full path to converted Microsoft Office Excel 2010 (*.xlsx) workbook file</returns>
private string convertDatabase(string path)
{
SQLiteConnection dbConnection = new SQLiteConnection("Data Source=" + path + ";Version=3");
dbConnection.Open();
//SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter("SELECT lessons.* FROM lessons", dbConnection);
const string command = "SELECT names_1.name AS teacher_short, names_1.full_name AS teacher, lessons._id, lessons.day, lessons.number, names.name AS subject, names_2.name AS place, names_3.name AS type, lessons.weeks" +
" FROM lessons, names, names names_1, names names_2, names names_3" +
" WHERE lessons.name_id = names._id AND lessons.teacher_id = names_1._id AND lessons.place_id = names_2._id AND lessons.kind_id = names_3._id";
SQLiteCommand dbCommand = new SQLiteCommand(command, dbConnection);
SQLiteDataReader dbReader = dbCommand.ExecuteReader();
Excel.Application excelApp = new Excel.Application();
excelApp.SheetsInNewWorkbook = 1;
//excelApp.Visible = true;
Excel.Workbooks workbooks = excelApp.Workbooks;
Excel.Workbook excelBook = workbooks.Add();
Excel._Worksheet worksheet = excelApp.ActiveSheet;
worksheet.Name = "Schedule";
//int i = 1;
//worksheet.Cells[i, "A"] = "teacher_short";
//worksheet.Cells[i, "B"] = "teacher";
//worksheet.Cells[i, "C"] = "_id";
//worksheet.Cells[i, "D"] = "day";
//worksheet.Cells[i, "E"] = "number";
//worksheet.Cells[i, "F"] = "subject";
//worksheet.Cells[i, "G"] = "place";
//worksheet.Cells[i, "H"] = "type";
//worksheet.Cells[i, "I"] = "weeks";
//while (dbReader.Read())
//{
// i++;
// worksheet.Cells[i, "A"] = dbReader["teacher_short"];
// worksheet.Cells[i, "B"] = dbReader["teacher"];
// worksheet.Cells[i, "C"] = dbReader["_id"];
// worksheet.Cells[i, "D"] = dbReader["day"];
// worksheet.Cells[i, "E"] = dbReader["number"];
// worksheet.Cells[i, "F"] = dbReader["subject"];
// worksheet.Cells[i, "G"] = dbReader["place"];
// worksheet.Cells[i, "H"] = dbReader["type"];
// worksheet.Cells[i, "I"] = dbReader["weeks"];
//}
string[] weeks = { "Верхняя неделя", "Нижняя неделя" };
string[] days = { "Понедельник", "Вторник", "Среда", "Четверг", "Пятница" };
for (int week = 1; week <= weeks.Length; week++)
{
worksheet.Cells[(week - 1) * days.Length * 5 + 1, "A"] = weeks[week - 1];
for (int dayNum = 1; dayNum <= days.Length; dayNum++)
{
worksheet.Cells[(week - 1) * days.Length * 5 + (dayNum - 1) * 5 + 1, "B"] = days[dayNum - 1];
}
}
long row;
long day, number;
//StringBuilder data = new StringBuilder();
while (dbReader.Read())
{
string week = (string)dbReader["weeks"];
if (week == "a") // all: 1,2,3,...
{
day = (long)dbReader["day"];
number = (long)dbReader["number"];
row = 1 + 5 * 5 + (day - 1) * 5 + (number - 1);
worksheet.Cells[row, "C"] = number;
worksheet.Cells[row, "D"] = dbReader["subject"];
worksheet.Cells[row, "E"] = dbReader["place"];
worksheet.Cells[row, "F"] = dbReader["type"];
worksheet.Cells[row, "G"] = dbReader["teacher"];
row = 1 + (day - 1) * 5 + (number - 1);
worksheet.Cells[row, "C"] = number;
worksheet.Cells[row, "D"] = dbReader["subject"];
worksheet.Cells[row, "E"] = dbReader["place"];
worksheet.Cells[row, "F"] = dbReader["type"];
worksheet.Cells[row, "G"] = dbReader["teacher"];
}
else if (week == "e") // even: 2,4,6,...
{
day = (long)dbReader["day"];
number = (long)dbReader["number"];
row = 1 + 5 * 5 + (day - 1) * 5 + (number - 1);
worksheet.Cells[row, "C"] = number;
worksheet.Cells[row, "D"] = dbReader["subject"];
worksheet.Cells[row, "E"] = dbReader["place"];
worksheet.Cells[row, "F"] = dbReader["type"];
worksheet.Cells[row, "G"] = dbReader["teacher"];
}
else if (week == "o") // odd: 1,3,5,...
{
day = (long)dbReader["day"];
number = (long)dbReader["number"];
row = 1 + (day - 1) * 5 + (number - 1);
worksheet.Cells[row, "C"] = number;
worksheet.Cells[row, "D"] = dbReader["subject"];
worksheet.Cells[row, "E"] = dbReader["place"];
worksheet.Cells[row, "F"] = dbReader["type"];
worksheet.Cells[row, "G"] = dbReader["teacher"];
}
}
worksheet.Columns.AutoFit();
worksheet.Columns.NumberFormat = "@";
string savePath = path.Substring(0, path.Length - 3) + ".xlsx";
//worksheet.SaveAs(savePath);
//worksheet.SaveAs("C:\\Programs\\schedule.xlsx");
excelBook.Close(true, savePath);
excelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(excelBook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excelApp);
return(savePath);
}
/// <summary>
/// 将DataTable导出Excel
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="saveFilePath">保存路径</param>
/// <param name="reportName">报表名称</param>
/// <returns>是否成功</returns>
public bool DataTableToExecl(DataTable dt, string saveFileName, string reportName)
{
//判断是否安装Excel
bool fileSaved=false;
if (status == -1)
return fileSaved;
//判断数据集是否为null
if (dt == null)
{
returnMessage="无引出数据！";
return false;
}
//判断保存路径是否有效
if (!saveFileName.Contains(":"))
{
returnMessage="引出路径有误！请选择正确路径！";
return false;
}
//创建excel对象
workbooks=xlApp.Workbooks;
workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
worksheet=workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size=10;
worksheet.Cells.NumberFormat="@";
long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0;
int rowIndex=0;
//第一行为报表名称，如果为null则不保存该行
++rowIndex;
worksheet.Cells[rowIndex, 1]=reportName;
range=worksheet.Cells[rowIndex, 1];
range.Font.Bold=true;
//写入字段(标题)
++rowIndex;
for (int i=0; i < dt.Columns.Count; i++)
{
worksheet.Cells[rowIndex, i + 1]=dt.Columns[i].ColumnName;
range=worksheet.Cells[rowIndex, i + 1];
range.Font.Color=ColorTranslator.ToOle(Color.Blue);
range.Interior.Color=dt.Columns[i].Caption == "表体" ? ColorTranslator.ToOle(Color.SkyBlue) : ColorTranslator.ToOle(Color.Yellow);
}
//写入数据
++rowIndex;
for (int r=0; r < dt.Rows.Count; r++)
{
for (int i=0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + rowIndex, i + 1]=dt.Rows[r][i].ToString();
}
rowRead++;
percent=((float)(100 * rowRead)) / totalCount;
}
//画单元格边框
range=worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
this.SetCellsBorderAround();
//列宽自适应
range.EntireColumn.AutoFit();
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved=true;
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch (Exception ex)
{
fileSaved=false;
returnMessage="导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved=false;
}
//释放Excel对应的对象（除xlApp,因为创建xlApp很花时间，所以等析构时才删除)
//Dispose(false);
Dispose();
return fileSaved;
}
private string convert(string filePath)
{
m_path = filePath;
var missing = Type.Missing;
objApp = new Excel.Application();
objWorkbooks = objApp.Workbooks;
Cross_TopTag topTag;
Cross_Data[] data;
Cross_Paragraphs[][] paragraphs;
Cross_Qas[][][] qas;
Cross_Answers[][][][] answers;
object[,] sheet1ValueArray;
object[,] sheet2ValueArray;
int countParagraphs = 0;
int countQas = 0;
int currentRow = 0;
bool excelOpen = false;
try
{
if (m_currentConvertingMode == convertingMode.CJSONToCExcel)
{
#region JSON -> Excel 변환
// ** name1 영역 파싱
topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path));
// name2 영역 파싱
data = new Cross_Data[topTag.data.Count];
for (int i = 0; i < data.Length; i++)
{
data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString());
}
// ** name3 영역 파싱
paragraphs = new Cross_Paragraphs[data.Length][];
for (int i = 0; i < data.Length; i++)
{
paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count];
for (int j = 0; j < data[i].paragraphs.Count; j++)
{
paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString());
countParagraphs++;
}
}
// ** name4 영역 파싱
qas = new Cross_Qas[data.Length][][];
for (int i = 0; i < data.Length; i++)
{
qas[i] = new Cross_Qas[paragraphs[i].Length][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count];
for (int k = 0; k < paragraphs[i][j].qas.Count; k++)
{
qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString());
countQas++;
}
}
}
// ** name5 영역 파싱
answers = new Cross_Answers[data.Length][][][];
for (int i = 0; i < data.Length; i++)
{
answers[i] = new Cross_Answers[paragraphs[i].Length][][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
answers[i][j] = new Cross_Answers[qas[i][j].Length][];
for (int k = 0; k < qas[i][j].Length; k++)
{
answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count];
for (int m = 0; m < qas[i][j][k].answers.Count; m++)
{
answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString());
}
}
}
}
// ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정
sheet1RowCount = countQas;
sheet2RowCount = countParagraphs;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];
// ** sheet1ValueArray & sheet2ValueArray에 데이터 입력
// * paragraph 순번 & name1 영역
for (int row = 0; row < sheet2RowCount; row++)
{
sheet2ValueArray[row, 0] = row + 1;
sheet2ValueArray[row, 1] = topTag.version;
sheet2ValueArray[row, 2] = topTag.creator;
sheet2ValueArray[row, 3] = topTag.progress;
sheet2ValueArray[row, 4] = topTag.formatt;
sheet2ValueArray[row, 5] = topTag.time;
sheet2ValueArray[row, 6] = topTag.check;
sheet2ValueArray[row, 7] = topTag.firstfile;
sheet2ValueArray[row, 8] = topTag.secondfile;
}
// * name2 & name3 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
sheet2ValueArray[currentRow, 9] = data[d].title;
sheet2ValueArray[currentRow, 10] = paragraphs[d][p].context;
sheet2ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
sheet2ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged;
currentRow++;
}
}
// * name4 영역
currentRow = 0;
int currentParaNum = 1;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
sheet1ValueArray[currentRow, 0] = currentRow + 1;
sheet1ValueArray[currentRow, 1] = qas[d][p][q].id;
sheet1ValueArray[currentRow, 2] = qas[d][p][q].confuseQt1;
sheet1ValueArray[currentRow, 3] = qas[d][p][q].confuseQf1;
sheet1ValueArray[currentRow, 4] = qas[d][p][q].confuseSat1;
sheet1ValueArray[currentRow, 5] = qas[d][p][q].confuseLat1;
sheet1ValueArray[currentRow, 6] = qas[d][p][q].question;
sheet1ValueArray[currentRow, 7] = qas[d][p][q].question_en;
sheet1ValueArray[currentRow, 8] = qas[d][p][q].question_tagged1;
sheet1ValueArray[currentRow, 9] = qas[d][p][q].questionType1;
sheet1ValueArray[currentRow, 10] = qas[d][p][q].questionFocus1;
sheet1ValueArray[currentRow, 11] = qas[d][p][q].questionSAT1;
sheet1ValueArray[currentRow, 12] = qas[d][p][q].questionLAT1;
sheet1ValueArray[currentRow, 13] = qas[d][p][q].confuseQt2;
sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQf2;
sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseSat2;
sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseLat2;
sheet1ValueArray[currentRow, 17] = qas[d][p][q].question_tagged2; //
sheet1ValueArray[currentRow, 18] = qas[d][p][q].questionType2; //
sheet1ValueArray[currentRow, 19] = qas[d][p][q].questionFocus2; //
sheet1ValueArray[currentRow, 20] = qas[d][p][q].questionSAT2; //
sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionLAT2;
sheet1ValueArray[currentRow, 22] = qas[d][p][q].confuseQt3;
sheet1ValueArray[currentRow, 23] = qas[d][p][q].confuseQf3;
sheet1ValueArray[currentRow, 24] = qas[d][p][q].confuseSat3;
sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseLat3;
sheet1ValueArray[currentRow, 26] = qas[d][p][q].question_tagged3;
sheet1ValueArray[currentRow, 27] = qas[d][p][q].questionType3;
sheet1ValueArray[currentRow, 28] = qas[d][p][q].questionFocus3;
sheet1ValueArray[currentRow, 29] = qas[d][p][q].questionSAT3;
sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionLAT3;
sheet1ValueArray[currentRow, 37] = currentParaNum;
currentRow++;
}
currentParaNum++;
}
}
// * name5 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
if (qas[d][p][q].answers.Count > 3)
{
return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + filePath);
}
int answerStartColNum = 31;
for (int a = 0; a < answers[d][p][q].Length; a++)
{
sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text;
sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en;
sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged;
sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn;
sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start;
sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end;
answerStartColNum += 6;
}
currentRow++;
}
}
}
// ** 엑셀로 출력
excelOpen = true;
objWorkbook = objWorkbooks.Add(missing);
objWorksheets = objWorkbook.Worksheets;
// * sheet2 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1);
objWorksheet.Name = "Paragraphs";
range = objWorksheet.get_Range("A1", "M1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet2ColHeader;
Marshal.ReleaseComObject(range);
Excel.Range c1 = objWorksheet.Cells[2, 1];
Excel.Range c2 = objWorksheet.Cells[sheet2RowCount + 1, sheet2ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet2ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
// * sheet1 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
objWorksheet.Name = "Qas";
range = objWorksheet.get_Range("A1", "AL1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet1ColHeader;
Marshal.ReleaseComObject(range);
c1 = objWorksheet.Cells[2, 1];
c2 = objWorksheet.Cells[sheet1RowCount + 1, sheet1ColCount];
range = objWorksheet.get_Range(c1, c2);
range.Value = sheet1ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(c2);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
m_savePath = Path.ChangeExtension(m_path, "xlsx");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists)
{
fi.Delete();
}
objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
#endregion
}
else
{
#region Excel -> JSON 변환
// ** Excel 파일 불러와서 object 이중배열에 데이터 입력
excelOpen = true;
objWorkbook = objWorkbooks.Open(m_path);
objWorksheets = objWorkbook.Worksheets;
objWorksheet = (Excel.Worksheet)objWorksheets[1];
range = objWorksheet.UsedRange;
sheet1ValueArray = (object[, ])range.get_Value(missing);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
objWorksheet = (Excel.Worksheet)objWorksheets[2];
range = objWorksheet.UsedRange;
sheet2ValueArray = (object[, ])range.get_Value(missing);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
// ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력
// * topTag 객체 데이터 입력
topTag = new Cross_TopTag();
topTag.version = sheet2ValueArray[2, 2] == null ? null : sheet2ValueArray[2, 2].ToString();
topTag.creator = sheet2ValueArray[2, 3] == null ? null : sheet2ValueArray[2, 3].ToString();
topTag.progress = Convert.ToInt32(sheet2ValueArray[2, 4]);
topTag.formatt = sheet2ValueArray[2, 5] == null ? null : sheet2ValueArray[2, 5].ToString();
topTag.time = Convert.ToDouble(sheet2ValueArray[2, 6]);
topTag.data = new List <object>();
// * topTag 객체 내의 Data 객체 리스트 입력
IList <object> titleList = new List <object>();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
object tempTitle = sheet2ValueArray[r, 7];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
Cross_Data tempData = new Cross_Data();
tempData.title = tempTitle == null ? "" : tempTitle.ToString();
tempData.paragraphs = new List <object>();
topTag.data.Add(tempData);
}
// * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
int dataCount = 0;
object currentTitle = sheet2ValueArray[2, 7];
List <Cross_Data> tempDataList = topTag.data.Cast <Cross_Data>().ToList();
for (int r = 2; r <= sheet2ValueArray.GetLength(0); r++)
{
Cross_Paragraphs tempParagraphs = new Cross_Paragraphs();
tempParagraphs.context = sheet2ValueArray[r, 8] == null ? null : sheet2ValueArray[r, 8].ToString();
//tempParagraphs.context_original = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_en = sheet2ValueArray[r, 9] == null ? null : sheet2ValueArray[r, 9].ToString();
tempParagraphs.context_tagged = sheet2ValueArray[r, 10] == null ? null : sheet2ValueArray[r, 10].ToString();
//if (sheet2ValueArray[r, 11] == null)
//{
// tempParagraphs.context_tagged = null;
//}
//else
//{
// //tempParagraphs.context_tagged = new List<string>();
// string[] tempTagged = sheet2ValueArray[r, 11].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempParagraphs.context_tagged.Add(item);
// }
//}
tempParagraphs.qas = new List <object>();
if (sheet2ValueArray[r, 7] == null || sheet2ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7] == null ? null : sheet2ValueArray[r, 7].ToString();
}
else if (sheet2ValueArray[r, 7] == currentTitle)
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet2ValueArray[r, 7].ToString();
}
}
topTag.data = tempDataList.Cast <object>().ToList();
// * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
dataCount = 0;
int paragraphCount = 0;
int currentParagraph = 1;
tempDataList = topTag.data.Cast <Cross_Data>().ToList();
List <Cross_Qas> tempQasList = new List <Cross_Qas>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
Cross_Qas tempQas = new Cross_Qas();
tempQas.id = sheet1ValueArray[r, 2] == null ? null : sheet1ValueArray[r, 2].ToString();
tempQas.confuseQt1 = Convert.ToBoolean(sheet1ValueArray[r, 3] == null ? null : sheet1ValueArray[r, 3]);
tempQas.confuseQf1 = Convert.ToBoolean(sheet1ValueArray[r, 4] == null ? null : sheet1ValueArray[r, 4]);
tempQas.confuseSat1 = Convert.ToBoolean(sheet1ValueArray[r, 5] == null ? null : sheet1ValueArray[r, 5]);
tempQas.confuseLat1 = Convert.ToBoolean(sheet1ValueArray[r, 6] == null ? null : sheet1ValueArray[r, 6]);
tempQas.question = sheet1ValueArray[r, 7] == null ? null : sheet1ValueArray[r, 7].ToString();
tempQas.question_en = sheet1ValueArray[r, 8] == null ? null : sheet1ValueArray[r, 8].ToString();
tempQas.question_tagged1 = sheet1ValueArray[r, 9] == null ? null : sheet1ValueArray[r, 9].ToString();
tempQas.questionType1 = sheet1ValueArray[r, 10] == null ? null : sheet1ValueArray[r, 10].ToString();
tempQas.questionFocus1 = sheet1ValueArray[r, 11] == null ? null : sheet1ValueArray[r, 11].ToString();
tempQas.questionSAT1 = sheet1ValueArray[r, 12] == null ? null : sheet1ValueArray[r, 12].ToString();
tempQas.questionLAT1 = sheet1ValueArray[r, 13] == null ? null : sheet1ValueArray[r, 13].ToString();
tempQas.confuseQt2 = Convert.ToBoolean(sheet1ValueArray[r, 14] == null ? null : sheet1ValueArray[r, 14]);
tempQas.confuseQf2 = Convert.ToBoolean(sheet1ValueArray[r, 15] == null ? null : sheet1ValueArray[r, 15]);
tempQas.confuseSat2 = Convert.ToBoolean(sheet1ValueArray[r, 16] == null ? null : sheet1ValueArray[r, 16]);
tempQas.confuseLat2 = Convert.ToBoolean(sheet1ValueArray[r, 17] == null ? null : sheet1ValueArray[r, 17]);
tempQas.questionType2 = sheet1ValueArray[r, 18] == null ? null : sheet1ValueArray[r, 18].ToString();
tempQas.questionFocus2 = sheet1ValueArray[r, 19] == null ? null : sheet1ValueArray[r, 19].ToString();
tempQas.questionSAT2 = sheet1ValueArray[r, 20] == null ? null : sheet1ValueArray[r, 20].ToString();
tempQas.questionLAT2 = sheet1ValueArray[r, 21] == null ? null : sheet1ValueArray[r, 21].ToString();
tempQas.confuseQt3 = Convert.ToBoolean(sheet1ValueArray[r, 22] == null ? null : sheet1ValueArray[r, 22]);
tempQas.confuseQf3 = Convert.ToBoolean(sheet1ValueArray[r, 23] == null ? null : sheet1ValueArray[r, 23]);
tempQas.confuseSat3 = Convert.ToBoolean(sheet1ValueArray[r, 24] == null ? null : sheet1ValueArray[r, 24]);
tempQas.confuseLat3 = Convert.ToBoolean(sheet1ValueArray[r, 25] == null ? null : sheet1ValueArray[r, 25]);
tempQas.questionType3 = sheet1ValueArray[r, 26] == null ? null : sheet1ValueArray[r, 26].ToString();
tempQas.questionFocus3 = sheet1ValueArray[r, 27] == null ? null : sheet1ValueArray[r, 27].ToString();
tempQas.questionSAT3 = sheet1ValueArray[r, 28] == null ? null : sheet1ValueArray[r, 28].ToString();
tempQas.questionLAT3 = sheet1ValueArray[r, 29] == null ? null : sheet1ValueArray[r, 29].ToString();
List <Cross_Answers> tempAnswersList = new List <Cross_Answers>();
// * topTag->Data->Paragraphs->Qas 객체 리스트 내의 Answers 객체 리스트 입력
for (int i = 0; i < 3; i++)
{
int ansStartColNum = 22 + (i * 6);//18
if (sheet1ValueArray[r, ansStartColNum] == null)
{
break; // 정답의 text 공백이면 없음 처리
}
Cross_Answers tempAnswers = new Cross_Answers();
tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? null : sheet1ValueArray[r, ansStartColNum].ToString();
//tempAnswers.text_original = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? null : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? null : sheet1ValueArray[r, ansStartColNum + 2].ToString();
tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? null : sheet1ValueArray[r, ansStartColNum + 3].ToString();
//if (sheet1ValueArray[r, ansStartColNum + 3] == null)
//{
// tempAnswers.text_tagged = null;
//}
//else
//{
// tempAnswers.text_tagged = new List<string>();
// string[] tempTagged = sheet1ValueArray[r, ansStartColNum + 3].ToString().Split(':');
// foreach (var item in tempTagged)
// {
// tempAnswers.text_tagged.Add(item);
// }
//}
//if (sheet1ValueArray[r, ansStartColNum + 4] == null)
//{
// tempAnswers.text_syn = null;
//}
//else
//{
// tempAnswers.text_syn = new List<string>();
// string[] tempSyn = sheet1ValueArray[r, ansStartColNum + 4].ToString().Split(':');
// foreach (var item in tempSyn)
// {
// tempAnswers.text_syn.Add(item);
// }
//}
tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);
tempAnswersList.Add(tempAnswers);
}
tempQas.answers = tempAnswersList.Cast <object>().ToList();
tempQasList.Add(tempQas);
currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 40]); //36
if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
{
if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 40])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
tempQasList = new List <Cross_Qas>();
if (paragraphCount < topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
{
paragraphCount++;
}
else // 도달하고 난 후에는 이렇게 처리
{
dataCount++;
paragraphCount = 0;
}
}
}
if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때
{
topTag.data.Cast <Cross_Data>().ToList()[dataCount].paragraphs.Cast <Cross_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList();
}
}
// ** JSON 파일로 저장
m_savePath = Path.ChangeExtension(m_path, "json");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists) // 파일이 이미 존재하면 삭제
{
fi.Delete();
}
string saveJSONText;
bool m_EtoJNullRemoveCheck = false;
if (m_EtoJNullRemoveCheck)
{
saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거
}
);
}
else
{
saveJSONText = JsonConvert.SerializeObject(topTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Include // Null값 객체 포함
}
);
}
using (StreamWriter sw = new StreamWriter(m_savePath))
{
sw.Write(saveJSONText);
}
#endregion
}
return(STR_CONVERTING_SUCCESS);
}
catch (Exception e)
{
if (excelOpen)
{
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
}
return("예외처리 된 오류 발생.\r\n파일: " + filePath);
}
}
private static void ActionExcelWrite(string filename, Action <Workbook> action)
{
//引用Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
// if equal null means EXCEL is not installed.
Console.WriteLine("Excel is not properly installed!");
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excel.Visible = false;
//打开时设置为全屏显式
//excel.DisplayFullScreen = true;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
//新增加一个 Excel 表(sheet)
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets[1];
try
{
action(workbook);
//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excel.DisplayAlerts = false;
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//workBook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
if (File.Exists(filename))
{
File.Delete(filename);
}
workbook.SaveAs(filename.Replace("/", "\\"));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
KillExcel.Kill(new IntPtr(excel.Hwnd));
//释放 COM 对象
//Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
//worksheet = null;
workbook = null;
workbooks = null;
excel = null;
GC.Collect();
}
}
public void Create()//Creat an Excel objecy.
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
private void timerHandler(object source, ElapsedEventArgs e)
{
DateTime oDateNow = DateTime.Now;
int nMinute = oDateNow.Minute;
lock (oThreadLockResource) {
Microsoft.Office.Interop.Excel.Application oExcelApplication = null;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkBook = null;
HashSet <TagItem> oHashSetTagsValues = null;
try {
try {
if (nMinute == 0)
{
string sLogFile = Program.FOLDER_FILE_LOG + "EmissionsExcel_log_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + ".txt";
if (!File.Exists(sLogFile))
{
if (Program.bShowLog)
{
if (Program.oLogFile != null)
{
Program.oLogFile.Flush();
Program.oLogFile.Close();
}
Program.oLogFile = File.AppendText(sLogFile);
DateTime oDateTimeBefore = oDateNow.AddDays(-1);
string sLogFileBefore = Program.FOLDER_FILE_LOG + "EmissionsExcel_log_" + oDateTimeBefore.Year.ToString() + oDateTimeBefore.Month.ToString().PadLeft(2, '0') + oDateTimeBefore.Day.ToString().PadLeft(2, '0') + ".txt";
if (File.Exists(sLogFileBefore))
{
File.Move(sLogFileBefore, Program.FOLDER_FILE_LOG_HISTORY + "EmissionsExcel_log_" + oDateTimeBefore.Year.ToString() + oDateTimeBefore.Month.ToString().PadLeft(2, '0') + oDateTimeBefore.Day.ToString().PadLeft(2, '0') + ".txt");
}
}
}
}
} catch (Exception) { }
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{TIMERHANDLER_START:OK} Llamada al evento {timerHandler} al segundo " + oDateNow.Second, true);
}
if (nMinute != Program.nLastMinute)
{
Program.nLastMinute = nMinute;
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Creando valores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
if ((!File.Exists(sPathFilename)) || ((File.Exists(sPathFilename)) && (nMinute == 0)))
{
if ((File.Exists(sPathFilename)) && (nMinute == 0))
{
File.Delete(sPathFilename);
}
oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
oExcelWorkBook = oExcelWorkBooks.Add();
createSheets(oExcelApplication, oDateNow);
removeDefaultSheets(oExcelApplication);
oExcelWorkBook.SaveAs(sPathFilename, XlFileFormat.xlExcel5);
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
}
}
oHashSetTagsValues = TryExecuteSyncRead(Program.nTimeoutOPCRead);
if ((oHashSetTagsValues != null) && (oHashSetTagsValues.Count == (((Program.nNumElements * 9) + 2) * Program.nNumLines)))
{
DateTime oDateNowCurrent = DateTime.Now;
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Se han devuelto todos los valores del OPC de forma correcta sobre el minuto " + oDateNow.Minute + " a la hora: " + oDateNowCurrent.Hour + "h " + oDateNowCurrent.Minute + "m " + oDateNowCurrent.Second + "s", false);
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Creando valores de los contaminantes de la Línea " + nLine + " sobre el minuto " + oDateNow.Minute, false);
}
Array oArrayTagsValues = oHashSetTagsValues.ToArray <TagItem>();
string sPathFilename = Program.FOLDER_DESTINY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
string sPathFilenameCopy = Program.FOLDER_DESTINY_COPY + Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls";
if (File.Exists(sPathFilename))
{
oExcelApplication = new Microsoft.Office.Interop.Excel.Application();
oExcelApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks oExcelWorkBooks = oExcelApplication.Workbooks;
oExcelWorkBook = oExcelWorkBooks.Open(sPathFilename);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel de la línea " + nLine + " abierto correctamente", false);
}
TagItem oTagItem1, oTagItem2, oTagItem3, oTagItem4, oTagItem5, oTagItem6, oTagItem7, oTagItem8, oTagItem9, oTagItem10, oTagItem11;
for (int i = 1; i <= Program.nNumElements; i++)
{
string sStatus = "V";
int nIndexBase = (((Program.nNumElements * 9) + 2) * (nLine - 1)) + ((i - 1) * 9) + 2;
oTagItem1 = (TagItem)oArrayTagsValues.GetValue((((Program.nNumElements * 9) + 2) * (nLine - 1)));
oTagItem2 = (TagItem)oArrayTagsValues.GetValue((((Program.nNumElements * 9) + 2) * (nLine - 1)) + 1);
oTagItem3 = (TagItem)oArrayTagsValues.GetValue(nIndexBase);
oTagItem4 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 1);
oTagItem5 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 2);
oTagItem6 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 3);
oTagItem7 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 4);
oTagItem8 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 5);
oTagItem9 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 6);
oTagItem10 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 7);
oTagItem11 = (TagItem)oArrayTagsValues.GetValue(nIndexBase + 8);
if (oTagItem1.getValue() == "1")
{
sStatus = "A";
}
else if (oTagItem2.getValue() == "1")
{
sStatus = "P";
}
else if (oTagItem5.getValue() == "1")
{
sStatus = "V";
}
else if (oTagItem6.getValue() == "1")
{
sStatus = "C";
}
else if (oTagItem7.getValue() == "1")
{
sStatus = "N";
}
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Volcando datos al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " {" + oTagItem3.getName().ToString() + ":" + oTagItem3.getValue().ToString() + ", " + oTagItem4.getName().ToString() + ":" + oTagItem4.getValue().ToString() + ", Status: " + sStatus + "}", false);
}
ElementValue oElementValue = new ElementValue(oTagItem3.getValue(), oTagItem4.getValue(), sStatus, oTagItem8.getValue(), oTagItem9.getValue(), oTagItem10.getValue(), oTagItem11.getValue());
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet = oExcelWorkBook.Worksheets[(i + 1)];
flushMinuteValue(oWorkSheet, oDateNow, nMinute, oElementValue, (i - 1));
oElementValue = null;
oTagItem1 = null; oTagItem2 = null; oTagItem3 = null; oTagItem4 = null; oTagItem5 = null;
oTagItem6 = null; oTagItem7 = null; oTagItem8 = null; oTagItem9 = null; oTagItem10 = null;
oTagItem11 = null;
nullExcelObject(oWorkSheet);
if ((Program.bShowLog) && (Program.bShowVerboseLog))
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos volcados al Fichero Excel del contaminante " + i.ToString() + " de la línea " + nLine + " de forma correcta", false);
}
}
try {
oExcelWorkBook.Save();
Program.writeLineInLogFile(Program.oLogFile, "{OK} Datos salvados al Fichero Excel de la línea " + nLine + " de forma correcta", false);
if (nMinute == 59)
{
if (File.Exists(sPathFilenameCopy))
{
File.Delete(sPathFilenameCopy);
}
oExcelWorkBook.SaveAs(sPathFilenameCopy, XlFileFormat.xlExcel5);
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Copia del Fichero Excel de la línea " + nLine + " realizada correctamente", false);
}
}
} catch (Exception) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} Error salvando los datos del Fichero Excel de la línea " + nLine, false);
}
}
oExcelWorkBook.Close(false);
nullExcelObject(oExcelWorkBook);
oExcelWorkBooks.Close();
nullExcelObject(oExcelWorkBooks);
oExcelApplication.Application.Quit();
nullExcelObject(oExcelApplication);
killExcelProcesses();
FileFormatXEAC oFileFormatXEAC = new FileFormatXEAC();
if (oFileFormatXEAC.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC", false);
}
}
oFileFormatXEAC = null;
FileFormatXEACReports oFileFormatXEACReports = new FileFormatXEACReports();
if (oFileFormatXEACReports.convertExcelFile(Program.FILE_EXCEL_TOKEN_NAME + "DayExcelL" + nLine + "_" + oDateNow.Year.ToString() + oDateNow.Month.ToString().PadLeft(2, '0') + oDateNow.Day.ToString().PadLeft(2, '0') + "_" + oDateNow.Hour.ToString().PadLeft(2, '0') + "00_A_Hour_Hourly.xls", (nMinute + 1)))
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{OK} Fichero Excel convertido al formato de la XEAC - Reports", false);
}
}
oFileFormatXEACReports = null;
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{ERROR} No existe el Fichero Excel de la Línea " + nLine, false);
}
}
oArrayTagsValues = null;
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} No se han devuelto todos los valores del OPC sobre el evento {timerHandler}", false);
}
killExcelProcesses();
if (Program.sMethod == "BEST_EFFORT")
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Creando valores anteriores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Creando líneas vacias de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.sMethod == "BEST_EFFORT")
{
cleanOPCReaderValues(oDateNow, nMinute, nLine);
}
else
{
flushEmptyMinute(oDateNow, nMinute, nLine);
}
}
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{WARNING} Fin de la creación de valores anteriores de los contaminantes", false);
}
}
}
} catch (Exception err) {
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Se ha producido una excepción sobre el evento {timerHandler:" + err.ToString() + "}", false);
}
killExcelProcesses();
if (Program.sMethod == "BEST_EFFORT")
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Creando valores anteriores de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
else
{
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Creando líneas vacias de los contaminantes sobre el minuto " + oDateNow.Minute, false);
}
}
for (int nLine = 1; nLine <= Program.nNumLines; nLine++)
{
if (Program.sMethod == "BEST_EFFORT")
{
cleanOPCReaderValues(oDateNow, nMinute, nLine);
}
else
{
flushEmptyMinute(oDateNow, nMinute, nLine);
}
}
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{EXCEPTION} Fin de la creación de valores anteriores de los contaminantes", false);
}
}
// Clean memory
oHashSetTagsValues = null;
GC.Collect();
GC.WaitForPendingFinalizers();
if (Program.bShowLog)
{
Program.writeLineInLogFile(Program.oLogFile, "{TIMERHANDLER_END:OK} Fin de la llamada al evento {timerHandler}", false);
}
}
}
/// <summary>
/// Extension method to write list data to excel.
/// </summary>
/// <typeparam name="T">Ganeric list</typeparam>
/// <param name="list"></param>
/// <param name="PathToSave">Path to save file.</param>
public static void ToExcel <T>(this List <T> list, string PathToSave)
{
#region Declarations
if (string.IsNullOrEmpty(PathToSave))
{
throw new Exception("Invalid file path.");
}
else if (PathToSave.ToLower().Contains("") == false)
{
throw new Exception("Invalid file path.");
}
if (list == null)
{
throw new Exception("No data to export.");
}
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbooks books = null;
Microsoft.Office.Interop.Excel._Workbook book = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel._Worksheet sheet = null;
Microsoft.Office.Interop.Excel.Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
// Optional argument variable
object optionalValue = Missing.Value;
string strHeaderStart = "A1";
string strDataStart = "A2";
#endregion
#region Processing
try
{
#region Init Excel app.
excelApp = new Microsoft.Office.Interop.Excel.Application();
books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks;
book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue));
sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets;
sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1));
#endregion
#region Creating Header
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ?
property.Name : attribute.DisplayName);
}
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
// range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
// range.Interior.Color = Color.LightGray.ToArgb();
#endregion
#region Writing data to cell
int count = list.Count;
object[,] objData = new object[count, objHeaders.Count];
for (int j = 0; j < count; j++)
{
var item = list[j];
int i = 0;
foreach (KeyValuePair <string, string> entry in objHeaders)
{
var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i++] = (y == null) ? "" : y.ToString();
}
}
range = sheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
//range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
#endregion
#region Saving data and Opening Excel file.
if (string.IsNullOrEmpty(PathToSave) == false)
{
book.SaveAs(PathToSave);
}
excelApp.Visible = true;
#endregion
#region Release objects
try
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
sheet = null;
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
sheets = null;
if (book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
book = null;
if (books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
}
books = null;
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
}
catch (Exception ex)
{
sheet = null;
sheets = null;
book = null;
books = null;
excelApp = null;
}
finally
{
excelApp.Quit();
GC.Collect();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
//public void ExportExcel(System.Data.DataTable p_dt, bool p_Multimode)
//{
// try
// {
// }
// catch (Exception)
// {
// throw;
// }
//}
//關於困難度 要把多從匯出寫成元件function有困難 擇日再戰
public void MiltiExportExcel(List <System.Data.DataTable> p_dtL, string p_Sheetname)
{
try
{
//System.Data.DataTable dt1 = this.dt;
//bool fileSaved = false;
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
saveFileName = saveFileName.Insert(saveFileName.LastIndexOf("."), "__0");
//對List中的每個Datatable做匯出
for (int i = 0; i < p_dtL.Count; i++)
{
//變更檔案名稱(跳號)
saveFileName = saveFileName.Replace("__" + i, "__" + (i + 1));
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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int n = 0; n < CNames.Length; n++)
{
worksheet.Cells[1, n + 1] = CNames[n];
}
worksheet.Name = p_Sheetname;
Microsoft.Office.Interop.Excel.Range range = worksheet.Range["C1", "C" + p_dtL[i].Rows.Count + 1];
Microsoft.Office.Interop.Excel.Range range1 = worksheet.Range["D1", "D" + p_dtL[i].Rows.Count + 1];
range.NumberFormat = "0000";
range1.NumberFormat = "00";
////塞入數字
for (int r = 0; r < p_dtL[i].Rows.Count; r++)
{
for (int c = 0; c < p_dtL[i].Columns.Count; c++)
{
worksheet.Cells[r + 2, c + 1] = p_dtL[i].Rows[r][c].ToString();
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//自動調整欄位
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("匯出文件時出錯,EXCEL文件可能正在使用中！\n" + ex.Message);
}
}
else
{
//fileSaved = false;
}
xlApp.Quit();
GC.Collect(); //回收釋放建議是額外放在外面
//判斷檔案條件是否都成立，沒問題就開啟EXCEL
//if (fileSaved && System.IO.File.Exists(saveFileName))
// System.Diagnostics.Process.Start(saveFileName); //開啟EXCEL
}
MessageBox.Show(saveFileName + "的資料匯出成功", "提示", MessageBoxButtons.OK);
}
catch (Exception ex)
{
throw ex;
}
}
public void Creat()
{
app = new Excel.Application();
wbooks = app.Workbooks;
wbook = wbooks.Add(true);
}
private void test_regex(object sender, RoutedEventArgs e)
{
Debug.WriteLine("start query");
MySqlConnection myconn = null;
MySqlCommand mycom = null;
//MySqlDataAdapter myrec = null;
myconn = new MySqlConnection("Host =192.168.56.2;Database=MEPurchase;Username=root;Password=123456;Charset=utf8;");
myconn.Open();
mycom = myconn.CreateCommand();
//打开excel
//if (true) {
Excel.Application oXL = new Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel.Worksheet sht;
Excel.Workbook oSummary = oWBs.Add(System.IO.Directory.GetCurrentDirectory() + @"\Purchaselist.xlsm");
sht = oSummary.Worksheets[1];
Debug.WriteLine(sht.Name);
int columns = 29;
string[] field = new string[columns];
//MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
for (int i = 1; i < field.Count(); i++)
{
field[i] = ((Excel.Range)sht.Cells[1, i]).Value.ToString();
// Debug.WriteLine(field[i]);
}
int irow = 1;
int iprint = 1;
string[] ctent = new string[columns];
while (true)
{
if ((((Excel.Range)sht.Cells[irow, 1]).Value.ToString()) == "")
{
break;
}
iprint++;
if (iprint == 30)
{
Debug.WriteLine(irow);
iprint = 1;
}
string sql = string.Format(@"insert into purchaselist ({0}) values ({1});", "行编号", irow);
mycom.CommandText = sql;
//mycom.CommandType = CommandType.Text;
MySqlDataReader sdr;
sdr = mycom.ExecuteReader();
sdr.Close();
for (int i = 2; i < field.Count(); i++)
{
var c = ((Excel.Range)sht.Cells[irow, i]).Value;
if (c != null)
{
try {
if (i != 17 && i != 18 && i != 19 && i != 22 && i != 26 && i != 27 && i != 28)
{
ctent[i] = "'" + c.ToString() + "'";
}
else
{
ctent[i] = c.ToString("yyyyMMdd");
}
sql = string.Format(@"UPDATE purchaselist SET {0}={1} WHERE 行编号={2};", field[i], ctent[i], irow);
mycom.CommandText = sql;
sdr = mycom.ExecuteReader();
sdr.Close();
}
catch (Exception ee) {
Debug.WriteLine("irow: " + irow.ToString());
Debug.WriteLine("exception: " + ee);
Debug.WriteLine("field[i]: " + field[i].ToString());
ctent[i] = "'" + c.ToString() + "'";
Debug.WriteLine("ctent[i]: " + ctent[i].ToString());
sql = string.Format(@"UPDATE purchaselist SET {0}_c={1} WHERE 行编号={2};", field[i], ctent[i], irow);
mycom.CommandText = sql;
sdr = mycom.ExecuteReader();
sdr.Close();
}
}
}
irow++;
}
// string s = @"insert into purchaselist (物料申请单号,项目号) values (20181023,s18004)";
// string sql = string.Format(@"insert into purchaselist
//({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27}) values
//({28},{29},{30},{31},{32},{33},{34},{35},{36},{37},{38},{39},{40},{41},{42},{43},{44},{45},{46},{47},{48},{49},{50},{51},{52},{53},{54},{55});",
//field[1], field[2], field[3], field[4], field[5], field[6], field[7], field[8], field[9], field[10], field[11], field[12], field[13], field[14],
//field[15], field[16], field[17], field[18], field[19], field[20], field[21], field[22], field[23], field[24], field[25], field[26], field[27], field[28],
//ctent[1], ctent[2], ctent[3], ctent[4], ctent[5], ctent[6], ctent[7], ctent[8], ctent[9], ctent[10], ctent[11], ctent[12], ctent[13], ctent[14],
//ctent[15], ctent[16], ctent[17], ctent[18], ctent[19], ctent[20], ctent[21], ctent[22], ctent[23], ctent[24], ctent[25], ctent[26], ctent[27], ctent[28]
// );
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
oXL = null;
}
protected void linkExport_OnClick(object sender, EventArgs e)
{
if ((!string.IsNullOrEmpty(txt_FromDate.Text.Trim())) && (!string.IsNullOrEmpty(txt_ToDate.Text.Trim())))
{
HPCBusinessLogic.UltilFunc _ultil = new UltilFunc();
System.Globalization.CultureInfo vi = new System.Globalization.CultureInfo("vi-VN");
string str_Thongbao = null;
string strfilename = "/TruycapWebsite_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //+ DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlt";
object template = Server.MapPath("../Template") + "/TruycapWebsite.XLT";
string dir_filename = Server.MapPath("../DataExport") + strfilename;
object Missing = System.Reflection.Missing.Value;
//Excel.Range oRange;
//Start Excel and get Application object.
oExcel = new Excel1.Application();
oWorkBooks = oExcel.Workbooks;
//oWorkBook = oWorkBooks.Add(Missing);
//Excel.XlWBATemplate.xlWBATWorksheet;
oWorkBook = oWorkBooks.Add(template);
oSheet = (Excel1.Worksheet)oWorkBook.ActiveSheet;
oExcel.Visible = false;
oExcel.UserControl = true;
try
{
if ((txt_FromDate.Text != "") && (txt_ToDate.Text != ""))
{
oSheet.get_Range("A2", "A2").Value2 = "Từ ngày " + txt_FromDate.Text.Trim() + " đến ngày " + txt_ToDate.Text.Trim();
}
else
{
oSheet.get_Range("A2", "A2").Value2 = "";
}
//if (ddlLang.SelectedIndex > 0)
// oSheet.get_Range("B8", "B8").Value2 = "Kênh PS: " + ddlLang.SelectedItem.Text;
//else
// oSheet.get_Range("B8", "B8").Value2 = " ";
//string where = GetWhere();
// CategoryDAL _cateDAL = new CategoryDAL();
DataSet _ds;
_ds = GetDataByCallSP("CMS_List_HitCounter", txt_FromDate.Text.Trim(), txt_ToDate.Text.Trim(), Int32.Parse(ddlCate.SelectedValue));
//if (Convert.ToInt32(ddlCate.SelectedValue.ToString()) > 0)
// _ds = _ultil.GetStoreDataSet("[CMS_List_HitCounter]", new string[] { "@FROM_DATE", "@TO_DATE", "@cate" }, new object[] { txt_FromDate.Text.Trim(), txt_ToDate.Text.Trim(), Convert.ToInt32(ddlCate.SelectedValue.ToString()) });
//else
// _ds = _ultil.GetStoreDataSet("[CMS_List_HitCounter]", new string[] { "@FROM_DATE", "@TO_DATE", "@cate" }, new object[] { txt_FromDate.Text.Trim(), txt_ToDate.Text.Trim(), 0 });
int _row = 4;
for (int i = 0; i <= _ds.Tables[0].Rows.Count - 1; i++)
{
//try
//{
// Total = Total + int.Parse(_ds.Tables[0].Rows[i]["sl"].ToString());
//}
//catch { ;}
oSheet.get_Range("A" + _row.ToString().Trim(), "A" + _row.ToString().Trim()).Value2 = (i + 1).ToString();
oSheet.get_Range("B" + _row.ToString().Trim(), "B" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["Ten_ChuyenMuc"].ToString();
oSheet.get_Range("C" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["sl"].ToString();
//oSheet.get_Range("D" + _row.ToString().Trim(), "D" + _row.ToString().Trim()).Value2 = _ds.Tables[0].Rows[i]["SL"].ToString();
_row++;
}
// _row--;
//oSheet.get_Range("C" + _row.ToString().Trim(), "C" + _row.ToString().Trim()).Value2 = "Tổng số: " + Total.ToString();
oSheet.get_Range("A3", "C" + _row.ToString().Trim()).Borders.LineStyle = Excel1.XlLineStyle.xlContinuous;
}
catch (Exception ex)
{
str_Thongbao = ex.ToString();
}
finally
{
object filename = @dir_filename;
oWorkBook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing, Missing, Missing, Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing, Missing, Missing, Missing, Missing);
if (oSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
oSheet = null;
}
if (oWorkBook != null)
{
//object filename = @dir_filename;
oWorkBook.Close(Missing, @dir_filename, Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook);
oWorkBook = null;
}
if (oExcel != null)
{
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
//if (oWorkBooks != null)
//{
// oWorkBooks.Close();
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBooks);
// oWorkBooks = null;
//}
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL.exe"))
{
proc.Kill();
}
Response.Redirect("~/DataExport/" + strfilename);
}
}
else
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, typeof(string), "Message", "alert('Hãy nhập khoảng thời gian tìm kiếm!');", true);
}
}
public void ExportToExcelFun(System.Data.DataTable dt)
{
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";
saveDia.Title = "导出为Excel文件";
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
!string.Empty.Equals(saveDia.FileName))
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9; //字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit(); //自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
MessageBox.Show("导出成功!");
}
}
// Returns true if the creation succeed, if not returns false
public static bool CreateExcelFile(string name, string customUI = null)
{
// Validate overriding
if (File.Exists(name))
{
Console.Write("There is an excel file already created with this name and path. \nWould you like to override? (y/n) --> ");
ConsoleKeyInfo key;
int x = 0x0;
int pos = Console.CursorLeft;
bool canceled = true;
while (x != 0x1B)
{
key = Console.ReadKey();
x = key.KeyChar;
if (!(x == 0x59 || x == 0x79 || x == 0x4E || x == 0x6E))
{
if (x != 0x1B)
{
if (x != 0x8)
{
Console.Write("\b \b");
}
else
{
Console.Write(" ");
}
Console.CursorLeft = pos;
}
else
{
Console.Write("xn");
}
}
else
{
canceled = x == 0x4E || x == 0x6E;
break;
}
}
if (canceled)
{
Console.WriteLine("\nProccess Canceled");
return(false);
}
else
{
File.Delete(name);
Console.WriteLine("\nOverriding file...");
}
}
// Gets the current office version
Excel.Application xlApp;
if (version == null)
{
xlApp = new Excel.Application();
version = xlApp.Version;
xlApp.Quit();
while (Marshal.ReleaseComObject(xlApp) != 0)
{
}
}
DisableTrustCenterSecurity();
xlApp = new Excel.Application();
Excel.Workbooks xlWbks = xlApp.Workbooks;
Excel.Workbook xlWbk = xlWbks.Add();
Excel.XlFileFormat xlFileFormat = Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
// Creating file
AddModule(xlWbk, Executable.Files.VBE.Modules.Callbacks);
name = Regex.Replace(name, "[/]", "\\");
xlWbk.SaveAs(name, xlFileFormat);
xlWbk.Close(true);
while (Marshal.ReleaseComObject(xlWbk) != 0)
{
}
xlWbks.Close();
while (Marshal.ReleaseComObject(xlWbks) != 0)
{
}
xlApp.Quit();
while (Marshal.ReleaseComObject(xlApp) != 0)
{
}
Console.WriteLine(@$ "Excel file successfully created: '{name}'");
bool added = CustomUI.AddCustomUI(name, customUI);
EnableTrustCenterSecurity();
return(added);
}
public Boolean toExcel(List <ApplyItem> list, String path)
{
try
{
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add(Missing.Value);
xlsheets = xlBook.Worksheets;
xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
xlApp.DisplayAlerts = false;
string[] keys = new String[] {
"企业名称",
"所在省份",
"所在城市",
"企业地址",
"法人代表",
"企业成立日期",
"企业联系人",
"联系人职务",
"联系电话",
"手机号码",
"职工人员",
"所属行业",
"主业从业年限",
"去年销售收入",
"主导产品、品牌及生产能力",
"申请融资金额",
"申请融资产品",
"申请融资期限",
"申请融资原因",
"可提供担保方式"
};
xlSheet.Cells[1, 1] = "申请人";
xlSheet.Cells[1, 2] = "申请邮箱";
xlSheet.Cells[1, 3] = "标题";
xlSheet.Cells[1, 4] = "时间";
int pos = 5;
for (int i = 0; i < keys.Length; i++)
{
xlSheet.Cells[1, i + pos] = keys[i];
}
for (int i_item = 0; i_item < list.Count; i_item++)
{
ApplyItem item = list[i_item];
xlSheet.Cells[i_item + 2, 1] = item.From;
xlSheet.Cells[i_item + 2, 2] = item.FromEmail;
xlSheet.Cells[i_item + 2, 3] = item.Title;
xlSheet.Cells[i_item + 2, 4] = item.Time;
for (int i_key = 0; i_key < keys.Length; i_key++)
{
try
{
xlSheet.Cells[i_item + 2, i_key + pos] = item.getValue(keys[i_key]);
}
catch (Exception e)
{
}
}
}
xlBook.Saved = true;
xlBook.SaveAs(path, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
xlBook.Close(false, Missing.Value, Missing.Value);
}
catch (Exception e)
{
}
return(true);
}
private void ExportExcel(DataSet ds, string saveFileName)
{
try
{
if (ds == null)
{
MessageBox.Show("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application elApp = new Microsoft.Office.Interop.Excel.Application();
if (elApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = elApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//写入字段名称
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数据
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件出错，文件可能正在被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
elApp.Quit();
GC.Collect(); //强制销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName);
MessageBox.Show("成功保存到Excel");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected void Excel_Click(object sender, ImageClickEventArgs e)
{
List <Data> data = GetData();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
//создаем и инициализируем объекты Excel
Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
//добавляем в файл Excel книгу. Параметр в данной функции - используемый для создания книги шаблон.
//если нас устраивает вид по умолчанию, то можно спокойно передавать пустой параметр.
Excel.Workbooks xlsWBs = App.Workbooks;
Excel.Workbook xlsWB = xlsWBs.Add(missingValue);
//и использует из нее
Excel.Sheets xlsSheets = xlsWB.Worksheets;
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsSheets.get_Item(1);
string[] cols = { " №", "ОТПРАВИЛ", "ДАТА", "ВРЕМЯ", "ЛИФТ", "СОБЫТИЕ", "ОПИСАНИЕ", "ПРИНЯЛ", "ДАТА/ВРЕМЯ", "ВЫПОЛНИЛ",
"КОММЕНТАРИЙ", "ДАТА", "ВРЕМЯ", "ПРОСТОЙ" };
for (int i = 0; i < cols.Length; i++)
{
xlsSheet.Cells[1, i + 1] = cols[i];
}
for (int i = 0; i < data.Count; i++)
{
xlsSheet.Cells[i + 2, 1] = data[i].Id;
xlsSheet.Cells[i + 2, 2] = data[i].From;
xlsSheet.Cells[i + 2, 3] = data[i].Date1;
xlsSheet.Cells[i + 2, 4] = data[i].Time1;
xlsSheet.Cells[i + 2, 5] = data[i].LiftId;
xlsSheet.Cells[i + 2, 6] = data[i].Category;
xlsSheet.Cells[i + 2, 7] = data[i].Text;
xlsSheet.Cells[i + 2, 8] = data[i].Prinyal;
xlsSheet.Cells[i + 2, 9] = data[i].StartPrinyal; //время принятия заявки
xlsSheet.Cells[i + 2, 10] = data[i].Vypolnil;
xlsSheet.Cells[i + 2, 11] = data[i].Couse;
xlsSheet.Cells[i + 2, 12] = data[i].Date2;
xlsSheet.Cells[i + 2, 13] = data[i].Time2;
xlsSheet.Cells[i + 2, 14] = data[i].Prostoy;
}
string name = DateTime.Now.ToString("ddMMyyyy-hhmm") + User.Identity.Name + ".xls";
xlsWB.SaveAs(Request.PhysicalApplicationPath + KOS.App_Code.ClearTemp._folder + "\\" + name,
Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
missingValue,
missingValue,
missingValue,
missingValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue);
//закрываем книгу
xlsWB.Close(false, missingValue, missingValue);
xlsWB = null;
xlsWBs = null;
xlsSheet = null;
xlsSheets = null;
//закрываем приложение
App.Quit();
//уменьшаем счетчики ссылок на COM объекты, что, по идее должно их освободить.
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheets);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWBs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
Download.NavigateUrl = "~/" + KOS.App_Code.ClearTemp._folder + "\\" + name;
Download.Text = "Скачать документ";
Download.Visible = true;
}
/// <summary>
/// If set fileName, the excel will save and quit
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Data"></param>
/// <param name="sheetName"></param>
/// <param name="otherAction"></param>
/// <param name="fileName"></param>
public static void ExportToExcel <T>(this IEnumerable <T> Data, string sheetName, Action <object> otherAction, string fileName = null) where T : class
{
List <PropertyInfo> props = typeof(T).GetProperties().Where(p => (p.PropertyType == typeof(string) || p.PropertyType.IsPrimitive) && p.GetCustomAttribute <IgnoreAttribute>() == null).ToList();
if (exApp == null)
{
exApp = new Ex.Application();
}
exApp.Visible = true;
Ex.Workbooks wbs = exApp.Workbooks;
if (exWb == null)
{
exWb = wbs.Add();
}
var sheet = exWb.Worksheets.Add() as Ex.Worksheet;
sheet.Name = sheetName;
Ex.Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1 + Data.Count(), props.Count]];
object[,] datas = new object[Data.Count() + 1, props.Count];
List <Tuple <string, int> > formualList = new List <Tuple <string, int> >();
for (int i = 0; i < props.Count; i++)
{
var attr = props[i].GetCustomAttribute <DisplayAttribute>();
if (attr != null)
{
datas[0, i] = attr.Name;
}
else
{
datas[0, i] = props[i].Name;
}
var formulaAttr = props[i].GetCustomAttribute <ExcelFormulaAttribute>();
if (formulaAttr != null)
{
Tuple <string, int> item = new Tuple <string, int>(formulaAttr.Formula, i + 1);
formualList.Add(item);
}
}
for (int i = 0; i < Data.Count(); i++)
{
for (int j = 0; j < props.Count; j++)
{
var val = props[j].GetValue(Data.ElementAt(i));
datas[i + 1, j] = val;
}
}
range.Value = datas;
for (int i = 0; i < props.Count; i++)
{
range = sheet.Cells[1, i + 1];
var attr = props[i].GetCustomAttribute <ExcelHeaderStyleAttribute>();
if (attr != null)
{
range.Interior.Color = attr.BackgroundColor;
range.EntireColumn.NumberFormat = attr.NumberFormat;
range.ColumnWidth = attr.Width;
range.Font.Bold = attr.IsFontBold;
range.Font.Size = attr.FontSize;
range.WrapText = attr.IsTextWrap;
range.HorizontalAlignment = attr.HAlign;
range.VerticalAlignment = attr.VAlign;
}
}
if (formualList.Count > 0)
{
foreach (var item in formualList)
{
range = sheet.Cells[2, item.Item2];
range.Formula = item.Item1;
range.AutoFill(sheet.Range[sheet.Cells[2, item.Item2], sheet.Cells[Data.Count() + 1, item.Item2]], Ex.XlAutoFillType.xlFillDefault);
}
}
if (otherAction != null)
{
otherAction(sheet);
}
range = sheet.UsedRange;
range.Borders[Ex.XlBordersIndex.xlEdgeLeft].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeTop].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeRight].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeBottom].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders.Color = ConsoleColor.Black;
range = sheet.Cells[1, 1] as Ex.Range;
range.Select();
if (!string.IsNullOrEmpty(fileName))
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
exWb.SaveAs(fileName);
exWb.Close();
exApp.Quit();
Marshal.ReleaseComObject(range);
range = null;
Marshal.ReleaseComObject(sheet);
sheet = null;
Marshal.ReleaseComObject(exWb);
exWb = null;
Marshal.ReleaseComObject(wbs);
wbs = null;
Marshal.ReleaseComObject(exApp);
exApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void coutExcel(DataGridView dataGridView1)
{
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
//无数据则不再执行
if (dataGridView1.Rows.Count == 0)
{
return;
}
//实例化Excel.Application对象
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;//为TRUE在导出时显示EXCEL界面
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
//int m = 0, n = 0;
//生成EXCEL列名
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
}
//存储数据
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
public void Create(string strFilePath)
{
_strFilePath = strFilePath;
_application = new Microsoft.Office.Interop.Excel.Application();
_workbooks = _application.Workbooks;
_workbook = _workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];
}
//创建一个Excel对象
public void Create()
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
private void ExcelCreate()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //设置环境变量为en，防止Excel错误0x80028018
app = new Excel.Application();
wbs = app.Workbooks;
Book1 = wbs.Add(true);
Sheet2 = (Excel.Worksheet)Book1.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Sheet2.Name = "Output";
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
//打开一个Excel文件
public void Open(string FileName)
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
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成功！");
}
//读取数据 显示到 datagridviewEmo
void runDisplayEmotion()
{
BlogReader breader;
//if (Datas.sinaJSONList.Count > 0)
// Datas.sinaJSONList.Clear();
if (Datas.sinaJSONList.Count == 0)
{
breader = new BlogReader("temp5.dat");
Datas.sinaJSONList = breader.ReadFromFile();
}
Datas.ProgressBarState = "读取完成,正在分析...";
Datas.ProgresBarStyle = System.Windows.Forms.ProgressBarStyle.Marquee;
string emo = "";
//表情键
List<string> emoKey = new List<string>();
List<EmoValue> emoValue = new List<EmoValue>();
using (FileStream fs = new FileStream("datas/emo.txt", FileMode.Open))
{
StreamReader sr = new StreamReader(fs);
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.Length > 0)
{
emoKey.Add(line);
emoValue.Add(new EmoValue());
}
}
sr.Close();
fs.Close();
}
MatchCollection mCollection;
foreach (SinaJSON item in Datas.sinaJSONList)
{
mCollection = Regex.Matches(item.Text, @"\[\w+\]");
for (int i = 0; i < mCollection.Count; i++)
{
emo = mCollection[i].Value;
//emo = emo.Substring(1, emo.Length - 2);
int index = emoKey.IndexOf(emo);
int male = 0, female = 0;
if (item.SinaUser.Gender.Equals("m"))
male++;
else
female++;
if (index != -1)
emoValue[index] = new EmoValue(emoValue[index].male + male, emoValue[index].female + female);
}
}
app = new Excel.Application();
workbooks = app.Workbooks as Excel.Workbooks;
workbook = workbooks.Add(Type.Missing);
app.DisplayAlerts = false;
//删除 多余Sheet
foreach (Worksheet ws in workbook.Worksheets)
if (ws != app.ActiveSheet)
{
ws.Delete();
}
foreach (Chart cht in workbook.Charts)
cht.Delete();
//创建一个Sheet,存数据
//worksheet = (Worksheet)workbook.
// Worksheets.Add(Type.Missing, workbook.ActiveSheet,
// Type.Missing, Type.Missing);
worksheet = workbook.Worksheets[1];
worksheet.Name = "数据";
int worksheetIndex = 0;
for (int i = 0; i < emoKey.Count; i++)
{
if (emoValue[i].male > 0 || emoValue[i].female > 0)
{
worksheet.Cells[i + 1, 1] = emoKey[i];
worksheet.Cells[i + 1, 2] = emoValue[i].male;
worksheet.Cells[i + 1, 3] = emoValue[i].female;
worksheetIndex++;
}
}
// TODO: 生成一个统计图对象：
Chart xlChart = (Chart)workbook.Charts.
Add(Type.Missing, worksheet, Type.Missing, Type.Missing);
// TODO: 设定数据来源
Range cellRange = (Range)worksheet.Cells[1, 1];
// TODO: 通过向导生成Chart
xlChart.ChartWizard(cellRange.CurrentRegion,
XlChartType.xl3DColumn, Type.Missing,
XlRowCol.xlColumns, 1, 0, true,
"表情比较", "表情", "数量");
// TODO: 设置统计图Sheet的名称
xlChart.Name = "统计";
// TODO: 让12个Bar都显示不同的颜色
ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
// TODO: 让Chart的条目的显示形状变成圆柱形，并给它们显示加上数据标签
Series s1 = (Series)grp.SeriesCollection(1);
s1.Name = "男";
s1.BarShape = XlBarShape.xlCylinder;
s1.HasDataLabels = true;
Series s = (Series)grp.SeriesCollection(2);
s.BarShape = XlBarShape.xlCylinder;
s.HasDataLabels = true;
s.Name = "女";
// TODO: 设置统计图的标题和图例的显示
xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
xlChart.ChartTitle.Font.Size = 24;
xlChart.ChartTitle.Shadow = false;
xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;
// TODO: 设置两个轴的属性，Excel.XlAxisType.xlValue对应的是Y轴，Excel.XlAxisType.xlCategory对应的是X轴
Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
valueAxis.AxisTitle.Orientation = -90;
Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
categoryAxis.AxisTitle.Font.Name = "宋体";
//--------------------------------------------------
//workbook.SaveAs(sPath, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing);
xlChart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture);
IntPtr hwnd = (IntPtr)app.Hwnd;
Bitmap b = null;
try
{
if (OpenClipboard(hwnd))
{
IntPtr data = GetClipboardData(14); // CF_ENHMETAFILE 14
if (data != IntPtr.Zero)
{
using (Metafile mf = new Metafile(data, true))
{
b = new Bitmap(mf);
}
}
}
}
finally
{
CloseClipboard();
//clear
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workbook = null;
app = null;
xlChart = null;
GC.Collect();
}
this.Invoke(new updateDataGridViewEmo(doUpdateDataGridViewEmo), new object[] { emoKey, emoValue, b });
}
public string convertFiles(IList <string> filePaths)
{
m_filePaths = filePaths;
m_fileCount = m_filePaths.Count;
int fileIndex = 0;
ArrayList sheet1ValueList = new ArrayList();
ArrayList sheet2ValueList = new ArrayList();
Cross_TopTag topTag;
Cross_Data[] data;
Cross_Paragraphs[][] paragraphs;
Cross_Qas[][][] qas;
Cross_Answers[][][][] answers;
ETRI_TopTag EtopTag;
object[,] sheet1ValueArray;
int totalCountQas = 0;
int totalCountParagraphs = 0;
int sheet1TotalRowCount = 0;
ArrayList splitedFileName = new ArrayList();
foreach (var item in m_filePaths)
{
string[] temp;
m_path = item;
var missing = Type.Missing;
temp = m_path.Split('_');
splitedFileName.Add(temp);
objApp = new Excel.Application();
objWorkbooks = objApp.Workbooks;
int countParagraphs = 0;
int countQas = 0;
int currentRow = 0;
bool excelOpen = false;
try
{
if (m_currentConvertingMode == convertingMode.CJSONToCExcel)
{
#region JSON -> Excel 변환
// ** name1 영역 파싱
topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path));
// name2 영역 파싱
data = new Cross_Data[topTag.data.Count];
for (int i = 0; i < data.Length; i++)
{
data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString());
}
// ** name3 영역 파싱
paragraphs = new Cross_Paragraphs[data.Length][];
for (int i = 0; i < data.Length; i++)
{
paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count];
for (int j = 0; j < data[i].paragraphs.Count; j++)
{
paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString());
countParagraphs++;
totalCountParagraphs++;
}
}
// ** name4 영역 파싱
qas = new Cross_Qas[data.Length][][];
for (int i = 0; i < data.Length; i++)
{
qas[i] = new Cross_Qas[paragraphs[i].Length][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count];
for (int k = 0; k < paragraphs[i][j].qas.Count; k++)
{
qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString());
countQas++;
totalCountQas++;
}
}
}
// ** name5 영역 파싱
answers = new Cross_Answers[data.Length][][][];
for (int i = 0; i < data.Length; i++)
{
answers[i] = new Cross_Answers[paragraphs[i].Length][][];
for (int j = 0; j < paragraphs[i].Length; j++)
{
answers[i][j] = new Cross_Answers[qas[i][j].Length][];
for (int k = 0; k < qas[i][j].Length; k++)
{
answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count];
for (int m = 0; m < qas[i][j][k].answers.Count; m++)
{
answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString());
}
}
}
}
// ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정
sheet1RowCount = countQas;
//sheet2RowCount = countParagraphs;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
//sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];
// ** sheet1ValueArray & sheet2ValueArray에 데이터 입력
// * paragraph 순번 & name1 영역
for (int row = 0; row < sheet1RowCount; row++)
{
sheet1ValueArray[row, 0] = row + 1;
sheet1ValueArray[row, 1] = topTag.version;
sheet1ValueArray[row, 2] = topTag.creator;
sheet1ValueArray[row, 3] = topTag.progress;
sheet1ValueArray[row, 4] = topTag.formatt;
sheet1ValueArray[row, 5] = topTag.time;
sheet1ValueArray[row, 6] = topTag.check;
sheet1ValueArray[row, 7] = topTag.firstfile;
sheet1ValueArray[row, 8] = topTag.secondfile;
}
// * name2 & name3 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
sheet1ValueArray[currentRow, 9] = data[d].title;
sheet1ValueArray[currentRow, 10] = paragraphs[d][p].context;
sheet1ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
sheet1ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged;
currentRow++;
}
}
// * name4 영역
currentRow = 0;
int currentParaNum = 1;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
sheet1ValueArray[currentRow, 13] = qas[d][p][q].id;
sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQt1;
sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseQf1;
sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseSat1;
sheet1ValueArray[currentRow, 17] = qas[d][p][q].confuseLat1;
sheet1ValueArray[currentRow, 18] = qas[d][p][q].question;
sheet1ValueArray[currentRow, 19] = qas[d][p][q].question_en;
sheet1ValueArray[currentRow, 20] = qas[d][p][q].question_tagged1;
sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionType1;
sheet1ValueArray[currentRow, 22] = qas[d][p][q].questionFocus1;
sheet1ValueArray[currentRow, 23] = qas[d][p][q].questionSAT1;
sheet1ValueArray[currentRow, 24] = qas[d][p][q].questionLAT1;
sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseQt2;
sheet1ValueArray[currentRow, 26] = qas[d][p][q].confuseQf2;
sheet1ValueArray[currentRow, 27] = qas[d][p][q].confuseSat2;
sheet1ValueArray[currentRow, 28] = qas[d][p][q].confuseLat2;
sheet1ValueArray[currentRow, 29] = qas[d][p][q].question_tagged2; //
sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionType2; //
sheet1ValueArray[currentRow, 31] = qas[d][p][q].questionFocus2; //
sheet1ValueArray[currentRow, 32] = qas[d][p][q].questionSAT2; //
sheet1ValueArray[currentRow, 33] = qas[d][p][q].questionLAT2;
sheet1ValueArray[currentRow, 34] = qas[d][p][q].confuseQt3;
sheet1ValueArray[currentRow, 35] = qas[d][p][q].confuseQf3;
sheet1ValueArray[currentRow, 36] = qas[d][p][q].confuseSat3;
sheet1ValueArray[currentRow, 37] = qas[d][p][q].confuseLat3;
sheet1ValueArray[currentRow, 38] = qas[d][p][q].question_tagged3;
sheet1ValueArray[currentRow, 39] = qas[d][p][q].questionType3;
sheet1ValueArray[currentRow, 40] = qas[d][p][q].questionFocus3;
sheet1ValueArray[currentRow, 41] = qas[d][p][q].questionSAT3;
sheet1ValueArray[currentRow, 42] = qas[d][p][q].questionLAT3;
sheet1ValueArray[currentRow, 55] = currentParaNum;
currentRow++;
}
currentParaNum++;
}
}
// * name5 영역
currentRow = 0;
for (int d = 0; d < data.Length; d++)
{
for (int p = 0; p < paragraphs[d].Length; p++)
{
for (int q = 0; q < qas[d][p].Length; q++)
{
if (qas[d][p][q].answers.Count > 3)
{
return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + m_path);
}
int answerStartColNum = 43;
for (int a = 0; a < answers[d][p][q].Length; a++)
{
sheet1ValueArray[currentRow, answerStartColNum] = answers[d][p][q][a].text;
sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en;
sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged;
sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn;
sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start;
sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end;
answerStartColNum += 6;
}
currentRow++;
}
}
}
if ((++fileIndex) < m_fileCount)
{
sheet1ValueList.Add(sheet1ValueArray);
continue;
}
//마지막 파일 ADD
sheet1ValueList.Add(sheet1ValueArray);
// 여러 sheetValueArray들을 각 작업량의 따라 나눠 하나로 통합
string[] separator = { "(", ")", "-", " " }; //제외할 요소들
int totalRowCount_sheet1 = 0;
int totalRowCount_sheet2 = 0;
for (int i = 0; i < fileIndex; i++)
{
string[] _temp = (string[])splitedFileName[i];
string[] splited = _temp[2].Split(separator, StringSplitOptions.RemoveEmptyEntries);
//sheet1 작업
int startIndex = Convert.ToInt32(splited[0]);
int endIndex = Convert.ToInt32(splited[1]);
int length = endIndex - startIndex + 1;
totalRowCount_sheet1 += length;
int rowIndex_sheet1 = 0;
int rowIndex_sheet2 = 0;
object[,] temp_arrList = (object[, ])sheet1ValueList[i];
object[,] tempSheet1Value = new object[length, sheet1ColCount];
for (int j = startIndex - 1; j < endIndex; j++)
{
for (int k = 0; k < sheet1ColCount; k++)
{
tempSheet1Value[rowIndex_sheet1, k] = temp_arrList[j, k];
}
rowIndex_sheet1++;
}
/*
* //sheet2 작업
* startIndex = (int)tempSheet1Value[0, 37];
* endIndex = (int)tempSheet1Value[rowIndex_sheet1 - 1, 37];
* length = endIndex - startIndex + 1;
*
* totalRowCount_sheet2 += length;
*
* temp_arrList = (object[,])sheet2ValueList[i];
* object[,] tempSheet2Value = new object[length, sheet2ColCount];
*
* for (int j = startIndex - 1; j < endIndex; j++)
* {
* for (int k = 0; k < sheet2ColCount; k++)
* tempSheet2Value[rowIndex_sheet2, k] = temp_arrList[j, k];
* rowIndex_sheet2++;
* }
* sheet1ValueList.RemoveAt(i);
* sheet2ValueList.RemoveAt(i);
* sheet1ValueList.Insert(i, tempSheet1Value);
* sheet2ValueList.Insert(i, tempSheet2Value);
*/
}
sheet1RowCount = totalRowCount_sheet1;
sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
int sheet1RowIndex = 0;
int sheet2RowIndex = 0;
int _sheet1RowCount;
int _sheet2RowCount;
for (int i = 0; i < sheet1ValueList.Count; i++)
{
object[,] tempSheet1Value = (object[, ])sheet1ValueList[i];
object[,] tempSheet2Value = (object[, ])sheet2ValueList[i];
_sheet1RowCount = (int)(tempSheet1Value.Length / sheet1ColCount);
for (int j = 0; j < _sheet1RowCount; j++)
{
for (int k = 0; k < sheet1ColCount; k++)
{
sheet1ValueArray[sheet1RowIndex, k] = tempSheet1Value[j, k];
}
sheet1RowIndex++;
}
}
//엑셀파일에 writting
excelOpen = true;
objWorkbook = objWorkbooks.Add(missing);
objWorksheets = objWorkbook.Worksheets;
// * sheet2 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.get_Item(1);
objWorksheet.Name = "Paragraphs";
range = objWorksheet.get_Range("A1", "M1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
Marshal.ReleaseComObject(range);
Excel.Range c1 = objWorksheet.Cells[2, 1];
range = objWorksheet.get_Range(c1);
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
// * sheet1 부분 적용
objWorksheet = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
objWorksheet.Name = "CrossToEtri";
range = objWorksheet.get_Range("A1", "AL1");
range.HorizontalAlignment = HCENTER;
range.Interior.Color = Color.FromArgb(142, 169, 219);
range.Value2 = sheet1ColHeader;
Marshal.ReleaseComObject(range);
c1 = objWorksheet.Cells[2, 1];
range = objWorksheet.get_Range(c1);
range.Value = sheet1ValueArray;
Marshal.FinalReleaseComObject(c1);
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
m_savePath = Path.ChangeExtension(m_path, "xlsx");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists)
{
fi.Delete();
}
objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
#endregion
}
else
{
#region Excel -> JSON 변환
// ** Excel 파일 불러와서 object 이중배열에 데이터 입력
excelOpen = true;
objWorkbook = objWorkbooks.Open(m_path);
objWorksheets = objWorkbook.Worksheets;
objWorksheet = (Excel.Worksheet)objWorksheets[1];
range = objWorksheet.UsedRange;
sheet1ValueArray = (object[, ])range.get_Value(missing);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
excelOpen = false;
// ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력
// * topTag 객체 데이터 입력
EtopTag = new ETRI_TopTag();
EtopTag.version = sheet1ValueArray[2, 2] == null ? "" : sheet1ValueArray[2, 2].ToString();
EtopTag.creator = sheet1ValueArray[2, 3] == null ? "" : sheet1ValueArray[2, 3].ToString();
EtopTag.data = new List <object>();
// * topTag 객체 내의 Data 객체 리스트 입력
IList <object> titleList = new List <object>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
object tempTitle = sheet1ValueArray[r, 10];
if (!titleList.Any()) // 리스트에 아무것도 없을때 (=맨처음)
{
titleList.Add(tempTitle);
}
else if (tempTitle == null) // null 이거나 "" 일 때 tempTitle == ""
{
titleList.Add(tempTitle);
}
else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
{
continue;
}
if (!titleList.Contains(tempTitle))
{
titleList.Clear();
titleList.Add(tempTitle);
}
ETRI_Data tempData = new ETRI_Data();
tempData.title = tempTitle == null ? "" : tempTitle.ToString();
tempData.paragraphs = new List <object>();
EtopTag.data.Add(tempData);
}
// * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
int dataCount = 0;
object currentTitle = sheet1ValueArray[2, 10];
List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs();
tempParagraphs.context = sheet1ValueArray[r, 11] == null ? "" : sheet1ValueArray[r, 11].ToString();
tempParagraphs.context_en = sheet1ValueArray[r, 12] == null ? "" : sheet1ValueArray[r, 12].ToString();
tempParagraphs.context_tagged = sheet1ValueArray[r, 13] == null ? "" : sheet1ValueArray[r, 13].ToString();
tempParagraphs.qas = new List <object>();
if (sheet1ValueArray[r, 10] == null || sheet1ValueArray[r, 7].ToString() == "")
{
if (r != 2)
{
dataCount++;
}
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet1ValueArray[r, 10] == null ? "" : sheet1ValueArray[r, 7].ToString();
}
else if (sheet1ValueArray[r, 10].Equals(currentTitle))
{
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
}
else
{
dataCount++;
tempDataList[dataCount].paragraphs.Add(tempParagraphs);
currentTitle = sheet1ValueArray[r, 10].ToString();
}
}
EtopTag.data = tempDataList.Cast <object>().ToList();
// * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
dataCount = 0;
int paragraphCount = 0;
int currentParagraph = 1;
tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
List <ETRI_Qas> tempQasList = new List <ETRI_Qas>();
for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
{
ETRI_Qas tempQas = new ETRI_Qas();
tempQas.id = sheet1ValueArray[r, 2] == null ? "" : sheet1ValueArray[r, 2].ToString();
tempQas.question = sheet1ValueArray[r, 7] == null ? "" : sheet1ValueArray[r, 7].ToString();
tempQas.question_en = sheet1ValueArray[r, 8] == null ? "" : sheet1ValueArray[r, 8].ToString();
tempQas.question_tagged = sheet1ValueArray[r, 27] == null ? "" : sheet1ValueArray[r, 27].ToString();
tempQas.questionType = sheet1ValueArray[r, 28] == null ? "" : sheet1ValueArray[r, 28].ToString();
tempQas.questionFocus = sheet1ValueArray[r, 29] == null ? "" : sheet1ValueArray[r, 29].ToString();
tempQas.questionSAT = sheet1ValueArray[r, 30] == null ? "" : sheet1ValueArray[r, 30].ToString();
tempQas.questionLAT = sheet1ValueArray[r, 31] == null ? "" : sheet1ValueArray[r, 31].ToString();
int ansStartColNum = 32;
ETRI_Answers tempAnswers = new ETRI_Answers();
tempAnswers.text = sheet1ValueArray[r, ansStartColNum] == null ? "" : sheet1ValueArray[r, ansStartColNum].ToString();
tempAnswers.text_en = sheet1ValueArray[r, ansStartColNum + 1] == null ? "" : sheet1ValueArray[r, ansStartColNum + 1].ToString();
tempAnswers.text_tagged = sheet1ValueArray[r, ansStartColNum + 2] == null ? "" : sheet1ValueArray[r, ansStartColNum + 2].ToString();
tempAnswers.text_syn = sheet1ValueArray[r, ansStartColNum + 3] == null ? "" : sheet1ValueArray[r, ansStartColNum + 3].ToString();
tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
tempAnswers.answer_end = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);
List <ETRI_Answers> tempAnswersList = new List <ETRI_Answers>();
tempAnswersList.Add(tempAnswers);
tempQas.answers = tempAnswersList.Cast <object>().ToList();
tempQasList.Add(tempQas);
currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 38]); //36
if (r + 1 <= sheet1ValueArray.GetLength(0)) // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
{
if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 38])) // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
{
EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
tempQasList = new List <ETRI_Qas>();
if (paragraphCount < EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Count - 1) // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
{
paragraphCount++;
}
else // 도달하고 난 후에는 이렇게 처리
{
dataCount++;
paragraphCount = 0;
}
}
}
if (r == sheet1ValueArray.GetLength(0)) // 현재 row가 마지막일때
{
EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList();
}
}
// ** JSON 파일로 저장
m_savePath = Path.ChangeExtension(m_path, "json");
FileInfo fi = new FileInfo(m_savePath);
if (fi.Exists) // 파일이 이미 존재하면 삭제
{
fi.Delete();
}
string saveJSONText;
bool m_EtoJNullRemoveCheck = false;
if (m_EtoJNullRemoveCheck)
{
saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore // Null값 객체 제거
}
);
}
else
{
saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Include // Null값 객체 포함
}
);
}
using (StreamWriter sw = new StreamWriter(m_savePath))
{
sw.Write(saveJSONText);
}
#endregion
}
}
catch (Exception e)
{
if (excelOpen)
{
Marshal.FinalReleaseComObject(range);
Marshal.FinalReleaseComObject(objWorksheet);
Marshal.FinalReleaseComObject(objWorksheets);
objWorkbook.Close(false, missing, missing);
objWorkbooks.Close();
objApp.Quit();
Marshal.FinalReleaseComObject(objWorkbook);
Marshal.FinalReleaseComObject(objWorkbooks);
Marshal.FinalReleaseComObject(objApp);
objApp = null;
}
return("예외처리 된 오류 발생.\r\n파일: " + m_path + "오류 이유:" + e.ToString());
}
}
return("모든 파일 변환 성공");
}
public static void ExportToExcel(System.Data.DataTable table, string saveFileName)
{
try
{
if (table == null)
{
return;
}
//bool fileSaved = false;
//ExcelApp xlApp = new ExcelApp();
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
return;
}
saveFileName = GetSaveFileName(saveFileName);
Excel.Workbooks workbooks = (Excel.Workbooks)xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
long rows = table.Rows.Count;
/*下边注释的两行代码当数据行数超过行时，出现异常：异常来自HRESULT:0x800A03EC。因为：Excel 2003每个sheet只支持最大行数据
*
* //Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count+2, gridview.Columns.View.VisibleColumns.Count+1]);
*
* //fchR.Value2 = datas;*/
if (rows > 65535)
{
long pageRows = 60000;//定义每页显示的行数,行数必须小于
int scount = (int)(rows / pageRows);
if (scount * pageRows < table.Rows.Count)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, table.Columns.Count + 1];
for (int i = 0; i < table.Columns.Count; i++) //写入字段
{
datas[0, i] = table.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= table.Rows.Count)
{
result = table.Rows.Count;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < table.Columns.Count; i++)
{
if (table.Columns[i].DataType == typeof(DateTime))
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
else
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[index, i] = obj == null ? "" : obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
}
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 2, table.Columns.Count + 1]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, table.Columns.Count]);
//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[table.Rows.Count + 2, table.Columns.Count + 1];
for (int i = 0; i < table.Columns.Count; i++) //写入字段
{
datas[0, i] = table.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < table.Rows.Count; r++)
{
for (int i = 0; i < table.Columns.Count; i++)
{
if (table.Columns[i].DataType == typeof(DateTime))
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
else
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[r + 1, i] = obj == null ? "" : obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
}
//System.Windows.Forms.Application.DoEvents();
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 1]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, table.Columns.Count]);
//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
// fileSaved = true;
}
catch
{
// fileSaved = false;
}
}
else
{
//fileSaved = false;
}
xlApp.Quit();
MessageBox.Show("导出完成");
GC.Collect();//强行销毁
//web后台谈框框，winform可以使用messagebox.. 大家都懂的
// System.Web.HttpContext.Current.Response.Write("<Script Language=JavaScript>...alert('Export Success! File path in D disk root directory.');</Script>");
}
catch (Exception)
{
// System.Web.HttpContext.Current.Response.Write("<Script Language=JavaScript>...alert('Export Error! please contact administrator!');</Script>");
}
}
/// <summary> Displays a 2DArray in excel. </summary>
/// <remarks> Tplateus, 3/01/2018. </remarks>
/// <param name="ListMatrix"> The 2DArray of entries. </param>
private void DisplayInExcel(string[,] ListMatrix, string[,] TableMatrix)
{
Excel.Application app = null;
Excel.Application openApp = null;
Excel.Workbooks books = null;
Excel.Workbooks openBooks = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheetList = null;
Excel.Worksheet sheetTable = null;
Excel.Range range = null;
Excel.Range rows = null;
Excel.Range cols = null;
List <string> errors = new List <string>();
try
{
try
{
openApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
openBooks = openApp.Workbooks;
for (int i = 1; i <= openBooks.Count; i++)
{
Excel.Workbook openBook = openBooks.Item[i];
Console.WriteLine(openBook.FullName);
if (openBook.FullName == outputfile)
{
errors.Add("The output file is already opened. Please close this file or choose a different output file.");
}
Marshal.ReleaseComObject(openBook);
}
}
catch
{
Console.WriteLine("No active Excel instance found. Starting an Excel instance...");
}
finally
{
if (openBooks != null)
{
Marshal.ReleaseComObject(openBooks);
}
if (openApp != null)
{
Marshal.ReleaseComObject(openApp);
}
}
app = new Excel.Application();
if (app == null)
{
errors.Add("No working instance of Excel could be found on this computer.");
}
if (errors.Count == 0)
{
app.SheetsInNewWorkbook = 2;
app.DisplayAlerts = false;
books = app.Workbooks;
book = books.Add();
sheets = book.Sheets;
sheetList = sheets.Item[1];
sheetTable = sheets.Item[2];
range = sheetList.Cells[1, 1];
sheetList.Name = "List parameters";
sheetTable.Name = "Table parameters";
#region format Sheet
//int rowCount = Matrix.GetLength(0);
//int columnCount = Matrix.GetLength(1);
//range = range.Resize[rowCount, columnCount];
//range.Value = Matrix;
//rows = range.Rows;
//cols = range.Columns;
////Set column widths to 300 pixels for each column in range.
//for (int iCol = 1; iCol <= columnCount; iCol++) //COMObject has a 1-based index.
//{
// Excel.Range column = cols[iCol];
// column.ColumnWidth = 32.56; //Translates to 300 pixels. Verify in Excel.
// if (column != null) Marshal.ReleaseComObject(column);
//}
//for (int iRow = 2; iRow <= rowCount; iRow++) // COMObject index starts at 1, not 0. First row is ignored (headers) => iRow = 2
//{
// Excel.Range row = rows.Item[iRow];
// object[,] objRow = row.Value2;
// List<string> listRow = objRow.Cast<string>().ToList(); //To utilize methods like findAll, the array is transformed to a list.
// for (int i = nParams; i < listRow.Count; i++) //First 3 columns are ignored since they dont have values (only ids).
// {
// string cellValue = listRow[i];
// List<string> allSameValues = listRow.FindAll(x => x == cellValue); //Search all cells with value == current cell value. If all values in the row are the same, its count should be the listRowCount -3 (ids).
// List<string> allValuesWithNotFound = listRow.FindAll(x => x == "!NOT_FOUND"); //!NOT_FOUND value is manually added in the 'TransformInto2DArray' method.
// if (allValuesWithNotFound.Count == 0)
// {
// if (allSameValues.Count < listRow.Count - nParams)
// {
// //Console.WriteLine(row.Cells[iRow, listRow.Count].Value2);
// Excel.Range value = row.Cells[1, i + 1];
// //Console.WriteLine(value.Value2);
// value.Interior.Color = Color.Red;
// value.Font.Color = Color.White;
// if (value != null) Marshal.ReleaseComObject(value);
// }
// }
// if (cellValue == "!NOT_FOUND")
// {
// Excel.Range cell = row.Cells[1, i + 1];
// cell.Interior.Color = Color.Orange;
// cell.Value2 = "";
// if (cell != null) Marshal.ReleaseComObject(cell);
// }
// }
// if (row != null) Marshal.ReleaseComObject(row);
//}
#endregion
FormatSheet(4, ListMatrix, sheetList);
FormatSheet(5, TableMatrix, sheetTable);
object missing = System.Reflection.Missing.Value;
book.SaveAs(outputfile, Excel.XlFileFormat.xlWorkbookDefault, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
book.Close(true, missing, missing);
app.SheetsInNewWorkbook = 3;
app.DisplayAlerts = true;
app.Quit();
string message = "The file '" + OutputFilenameBox.Text + ".xlsx' was succesfully placed in " + OutputDirBox.Text + ".";
MessageBox.Show(message, "Succes!");
}
else
{
DisplayErrors(errors);
}
}
finally
{
if (cols != null)
{
Marshal.ReleaseComObject(cols);
}
if (rows != null)
{
Marshal.ReleaseComObject(rows); Console.WriteLine("COMObject 'rows' released.");
}
if (range != null)
{
Marshal.ReleaseComObject(range); Console.WriteLine("COMObject 'range' released.");
}
if (sheetTable != null)
{
Marshal.ReleaseComObject(sheetTable); Console.WriteLine("COMObject 'sheetTable' released.");
}
if (sheetList != null)
{
Marshal.ReleaseComObject(sheetList); Console.WriteLine("COMObject 'sheetList' released.");
}
if (sheets != null)
{
Marshal.ReleaseComObject(sheets); Console.WriteLine("COMObject 'sheets' released.");
}
if (book != null)
{
Marshal.ReleaseComObject(book); Console.WriteLine("COMObject 'book' released.");
}
if (books != null)
{
Marshal.ReleaseComObject(books); Console.WriteLine("COMObject 'books' released.");
}
if (app != null)
{
Marshal.ReleaseComObject(app); Console.WriteLine("COMObject 'app' released.");
}
}
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
private void Excelbutton_Click(object sender, EventArgs e)
{
string fileName = "学生名单";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog
{
DefaultExt = "xls",
Filter = "Excel文件|*.xls",
FileName = fileName
};
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
#region 写入标题
for (int i = 0; i < studentdataview.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = studentdataview.Columns[i].HeaderText;
}
#endregion
#region 写入数值
for (int r = 0; r < studentdataview.Rows.Count; r++)
{
for (int i = 0; i < studentdataview.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = studentdataview.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
#endregion
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
