private void DC02Button_Click(object sender, EventArgs e)
{
string[,] checks = { { "C7:E19", "C21:E35"}, { "H7:J19", "H21:J35"}, { "M7:O19", "M21:O35" }, { "R7:T19", "R21:T35" }, { "W7:Y19", "W21:Y35" },
{ "C50:E62", "C64:E78"}, { "H50:J62", "H64:J78"}, { "M50:O62", "M64:O78" }, { "R50:T62", "R64:T78" }, { "W50:Y62", "W64:Y78" }};
string[] nameLocations = { "C5", "H5", "M5", "R5", "W5", "C48", "H48", "M48", "R48", "W48" };
if (checkTextbox.Text == null || checkTextbox.Text == "")
{
MessageBox.Show("Chọn file");
return;
}
if (DC02Textbox.Text == null || DC02Textbox.Text == "")
{
MessageBox.Show("Chọn thư mục");
return;
}
string checkFileName = checkTextbox.Text;
string dc02FolderName = DC02Textbox.Text;
string fileName = Path.GetFileName(checkFileName);
string[] arr = fileName.Split(' ');
int householdId = int.Parse(arr[0]);
Household household = village[householdId];
List<Person> members = new List<Person>();
members.Add(household.MainMember);
if (household.OtherMembers != null)
{
members.AddRange(household.OtherMembers);
}
Excel.Application excelApplication = new Excel.Application(); ;
Excel.Workbook workbook = excelApplication.Workbooks.Open(checkFileName);
Excel.Worksheet worksheet = worksheet = workbook.Worksheets[1];
List<string> corrections;
Excel.Range titles1 = worksheet.get_Range("B7:B19");
Excel.Range titles2 = worksheet.get_Range("B21:B35");
string address = worksheet.get_Range("A3").Value.ToString();
address = address.Split(':')[1].Trim();
List<DC02> dc02s = new List<DC02>();
for (int i = 0; i < household.NumRegistered; i++)
{
Excel.Range range1 = worksheet.get_Range(checks[i, 0]);
Excel.Range range2 = worksheet.get_Range(checks[i, 1]);
object[,] data1 = (object[,]) range1.Value2;
object[,] data2 = (object[,]) range2.Value2;
corrections = new List<string>();
for (int j = 1; j <= data1.GetLength(0); j++)
{
for (int k = 1; k <= data1.GetLength(1); k++)
{
string d1 = data1[j, k] != null ? data1[j, k].ToString() : "x";
if (d1.ToUpper() == "T" || d1.ToUpper() == "S")
{
string action = d1.ToUpper() == "T" ? "Thêm" : "Sửa";
string newValue = range1.Cells[j, k].Comment != null ? range1.Cells[j, k].Comment.Text() : "";
string correction = string.Format("{0} {1} thành: {2}", action, titles1.Cells[j, 1].Value.ToString(), newValue);
corrections.Add(correction);
break;
}
}
}
for (int j = 1; j <= data2.GetLength(0); j++)
{
for (int k = 1; k <= data2.GetLength(1); k++)
{
string d2 = data2[j, k] != null ? data2[j, k].ToString() : "x";
if (d2.ToUpper() == "T" || d2.ToUpper() == "S")
{
string action = d2.ToUpper() == "T" ? "Thêm" : "Sửa";
string newValue = range2.Cells[j, k].Comment != null ? range2.Cells[j, k].Comment.Text() : "";
string correction = string.Format("{0} {1} thành: {2}", action, titles2.Cells[j, 1].Value.ToString(), newValue);
corrections.Add(correction);
break;
}
}
}
if (corrections.Count > 0)
{
string name = worksheet.get_Range(nameLocations[i]).Value.ToString();
string attachment = worksheet.get_Range(nameLocations[i]).Comment != null ? worksheet.get_Range(nameLocations[i]).Comment.Text() : "";
var person = members.Where(p => p.Name == name).Single();
dc02s.Add(new DC02() { Member = person, Corrections = corrections, Attachment = attachment });
}
}
workbook.Close();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
GC.Collect();
GC.WaitForPendingFinalizers();
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
if (dc02s.Count > 0)
{
string templateFile = Directory.GetCurrentDirectory() + @"\DC02-" + dc02s.Count + ".docx";
string dc02File = DC02Textbox.Text + @"\" + string.Format("DC02 {0} {1}.docx", household.HouseholdId, household.MainMember.Name);
File.Copy(templateFile, dc02File, true);
Word.Application wordApplication = new Word.Application();
Word.Document document = wordApplication.Documents.Open(dc02File);
for (int i = 0; i < dc02s.Count; i++)
{
string rName = dc02s[i].Member.Name != null ? dc02s[i].Member.Name : "";
string rDOB = dc02s[i].Member.DateOfBirth != null ? dc02s[i].Member.DateOfBirth : "";
string rGender = dc02s[i].Member.Gender ? "Nam" : "Nữ";
string rId = dc02s[i].Member.IdNumber != null ? dc02s[i].Member.IdNumber : "";
string correction = "";
foreach (string str in dc02s[i].Corrections)
{
correction += " - " + str + "\x0B";
}
FindAndReplace(wordApplication, string.Format("<name{0}>", i + 1), rName);
FindAndReplace(wordApplication, string.Format("<dob{0}>", i + 1), rDOB);
FindAndReplace(wordApplication, string.Format("<gender{0}>", i + 1), rGender);
FindAndReplace(wordApplication, string.Format("<id{0}>", i + 1), rId);
FindAndReplace(wordApplication, string.Format("<address{0}>", i + 1), address);
FindAndReplace(wordApplication, string.Format("<update{0}>", i + 1), correction);
FindAndReplace(wordApplication, string.Format("<attachments{0}>", i + 1), dc02s[i].Attachment);
}
document.Save();
Marshal.ReleaseComObject(document);
GC.Collect();
GC.WaitForPendingFinalizers();
wordApplication.Quit();
Marshal.ReleaseComObject(wordApplication);
}
}
private void load()
{
string filePath = fileTextBox.Text;
if (filePath == null)
{
MessageBox.Show("Chọn file");
return;
}
else
{
Excel.Application excelApplication = new Excel.Application();
Excel.Workbook workbook = excelApplication.Workbooks.Open(filePath);
Excel.Worksheet worksheet = workbook.Worksheets[1];
Excel.Range range = worksheet.UsedRange;
object[,] data = (object[,])range.Value2;
workbook.Close();
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
GC.Collect();
GC.WaitForPendingFinalizers();
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
if (data != null)
{
village = new Dictionary<int, Household>();
Household household = null;
int numMembers = 0;
int numRegistered = 0;
for (int i = 2; i <= data.GetLength(0); i++)
{
string name = data[i, 1] != null ? data[i, 1].ToString() : "";
string dateOfBirth = data[i, 2] != null ? data[i, 2].ToString() : "";
bool gender = (data[i, 3] != null && data[i, 3].ToString() == "1") ? true : false;
string id = (data[i, 4] != null) ? data[i, 4].ToString() : "";
bool isMainMember = (data[i, 5] != null && data[i, 5].ToString() == "1") ? true : false;
int householdId = (data[i, 6] != null) ? int.Parse(data[i, 6].ToString()) : -1;
bool isRegistered = (data[i, 7] != null && data[i, 7].ToString() == "1") ? false : true;
Person person = new Person() { Name = name, DateOfBirth = dateOfBirth, Gender = gender, IdNumber = id, IsRegistered = isRegistered };
if (isMainMember)
{
if (household != null)
{
household.NumMembers = numMembers;
household.NumRegistered = numRegistered;
}
household = new Household();
household.HouseholdId = householdId;
household.MainMember = person;
village.Add(householdId, household);
numMembers = 1;
numRegistered = isRegistered ? 1 : 0;
}
else
{
if (household.OtherMembers == null)
{
household.OtherMembers = new List<Person>();
}
household.OtherMembers.Add(person);
numMembers++;
if (isRegistered)
{
numRegistered++;
}
}
}
household.NumMembers = numMembers;
household.NumRegistered = numRegistered;
}
}
}