using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
namespace PACC
{
public partial class Form1 : Form
{
private string attachDir;
public Form1()
{
InitializeComponent();
}
private void btnSelectDir_Click(object sender, EventArgs e)
{
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
attachDir = fbd.SelectedPath;
label1.Text = attachDir;
}
}
private void btnDo_Click(object sender, EventArgs e)
{
Do(InsertAttachments);
}
private void InsertAttachments() {
//attachDir = @"C:\Users\Administrator\Desktop\平安财产\平安财产";
if (string.IsNullOrEmpty(attachDir))
{
MessageBox.Show("请先选择附件所在目录!");
return;
}
string excelFileName = string.Empty;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "excel文件(*.xlsx)|*.xlsx";
if (ofd.ShowDialog() != System.Windows.Forms.DialogResult.OK)
return;
else {
excelFileName = ofd.FileName;
}
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wb = app.Workbooks.Open(Filename: excelFileName);
Excel.Worksheet sht = wb.Worksheets[1];
Excel.OLEObjects objs = sht.OLEObjects();
for (int i = objs.Count; i >= 1; i--)
objs.Item(i).Delete();
FileInfo[] fileInfos = new DirectoryInfo(attachDir).GetFiles("*.xlsx");
List<string> lFiles = new List<string>();
List<string> lYjhs = new List<string>();
foreach (var item in fileInfos)
{
lFiles.Add(item.FullName);
lYjhs.Add(GetYjh(item.Name));
}
int startLine = 9, lastLine = 68;
int yjzhColNo = 3;
Excel.Range rng = null;
int count = 0;
for (int i = startLine; i <= lastLine; i++)
{
if (null != sht.Cells[i, yjzhColNo])
{
object o = sht.Cells[i, yjzhColNo].Value;
string yjh = o.ToString();
if (lYjhs.Contains(yjh))
{
int index = lYjhs.IndexOf(yjh);
rng = sht.Cells[i, 7]; rng.Select();
Excel.OLEObject obj = objs.Add(Filename: lFiles[index]
, Link: false
, DisplayAsIcon: true
, IconFileName: "excel.exe"
, IconIndex: 0
, IconLabel: yjh
, Left: rng.Left
, Top: rng.Top
, Width: rng.Width
, Height: rng.Height);
obj.Select();
rng.Select();
count++;
}
}
if (count == -1)
break;
}
if (0!=count)
MessageBox.Show(string.Format("插入附件 {0} 个!",count));
}
private string GetYjh(string filename)
{
string result = string.Empty;
string temp1 = filename.Split('_')[1];
result = temp1.Substring(0, temp1.IndexOf('-'));
return result;
}
private void Do(MyDo myDo) {
try
{
if (null != myDo)
{
myDo();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public delegate void MyDo();
}
}