C#Excelへの画像のエクスポート(Excelの作成、ファイルの圧縮、ファイルの再帰削除、フォルダなど)
37407 ワード
using ICSharpCode.SharpZipLib.Zip;
public void CreateDirectory(string DirectoryPath)
{
if (!Directory.Exists(DirectoryPath)) // 。
{
Directory.CreateDirectory(DirectoryPath); // 。
}
}
protected void dlZipDir(string strPath, string strFileName)
{
MemoryStream ms = null;
Response.ContentType = "application/octet-stream";
strFileName = HttpUtility.UrlEncode(strFileName);
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + ".zip");
ms = new MemoryStream();
zos = new ZipOutputStream(ms);
strBaseDir = strPath + "";
addZipEntry(strBaseDir);
zos.Finish();
zos.Close();
this.DeleteFolder(strPath);
Response.Clear();
Response.BinaryWrite(ms.ToArray());
Response.End();
}
protected void addZipEntry(string PathStr)
{
DirectoryInfo di = new DirectoryInfo(PathStr);
foreach (DirectoryInfo item in di.GetDirectories())
{
addZipEntry(item.FullName);
}
foreach (FileInfo item in di.GetFiles())
{
FileStream fs = File.OpenRead(item.FullName);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
string strEntryName = item.FullName.Replace(strBaseDir + "\\", "");
ZipEntry entry = new ZipEntry(strEntryName);
zos.PutNextEntry(entry);
zos.Write(buffer, 0, buffer.Length);
fs.Close();
}
}
public void DeleteFolder(string dir)
{
if (Directory.Exists(dir)) //
{
foreach (string d in Directory.GetFileSystemEntries(dir))
{
if (File.Exists(d))
File.Delete(d); //
else
DeleteFolder(d); //
}
Directory.Delete(dir, true); //
}
}
///
///
///
///
///
///
/// (0), (1), (2)
public void ExcelRange(Range range, bool MergeCells, bool Bold, int Num)
{
//
range.RowHeight = 20;
//
range.Font.Size = 10;
//
if (MergeCells == true)
{
range.MergeCells = true;
}
//
if (Bold == true)
{
range.Font.Bold = true;
}
//
range.ColumnWidth = 9.4;
//
range.NumberFormatLocal = "@";
//
range.Font.ColorIndex = 0;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Num == 0 ? Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft : Num == 1 ? Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter : Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//
range.Borders.LineStyle = XlLineStyle.xlContinuous;
}
///
///
///
///
///
///
/// Excel
///
protected void OutPortExcel(StudentModel StudentModel, List FamilyList, List StudentPremiumList, string ExcelPath, string ImagePath)
{
object miss = Missing.Value;
Application excel = new Application();
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets.Add(miss, miss, miss, miss);
try
{
excel.Selection.NumberFormatLocal = "@";
//----------------------------------- ------------------------------------------
worksheet.Cells[1, 1] = "XX ";
//
Range range1 = (Range)worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 10]];
this.ExcelRange(range1, true, true, 1);
range1.RowHeight = 30;
range1.Font.Size = 20;
//----------------------------------- -------------------------------------------------
worksheet.Cells[2, 1] = " ";
//
Range range2_1 = (Range)worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[15, 1]];
this.ExcelRange(range2_1, true, true, 0);
range2_1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range2_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// 2
range2_1.Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Cells[2, 2] = " ";
Range range2_3 = (Range)worksheet.Range[worksheet.Cells[2, 2], worksheet.Cells[2, 2]];
this.ExcelRange(range2_3, false, false, 0);
worksheet.Cells[2, 3] ="'"+TextToHtml(StudentModel.StudentCode.ToString());
Range range2_4 = (Range)worksheet.Range[worksheet.Cells[2, 3], worksheet.Cells[2, 4]];
this.ExcelRange(range2_4, true, false, 0);
worksheet.Cells[2, 5] = " ";
Range range2_5 = (Range)worksheet.Range[worksheet.Cells[2, 5], worksheet.Cells[2, 5]];
this.ExcelRange(range2_5, false, false, 0);
worksheet.Cells[2, 6] = TextToHtml(StudentModel.StudentName);
Range range2_6 = (Range)worksheet.Range[worksheet.Cells[2, 6], worksheet.Cells[2, 8]];
this.ExcelRange(range2_6, true, false, 0);
worksheet.Cells[2, 9] = " ";
Range range2_9 = (Range)worksheet.Range[worksheet.Cells[2, 9], worksheet.Cells[7, 9]];
this.ExcelRange(range2_6, true, false, 0);
// 3
worksheet.Cells[3, 2] = " ";
Range range3_2 = (Range)worksheet.Range[worksheet.Cells[3, 2], worksheet.Cells[3, 2]];
this.ExcelRange(range3_2, false, false, 0);
worksheet.Cells[3, 3] = TextToHtml(StudentModel.GraduateSchool2.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", "") + StudentModel.GraduateSchool.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", ""));
Range range3_3 = (Range)worksheet.Range[worksheet.Cells[3, 3], worksheet.Cells[3, 4]];
this.ExcelRange(range3_3, true, false, 0);
worksheet.Cells[3, 5] = " ";
Range range3_5 = (Range)worksheet.Range[worksheet.Cells[3, 5], worksheet.Cells[3, 5]];
this.ExcelRange(range3_5, false, false, 0);
worksheet.Cells[3, 6] = TextToHtml(StudentModel.SexName);
Range range3_6 = (Range)worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 8]];
this.ExcelRange(range3_6, true, false, 0);
// 4
worksheet.Cells[4, 2] = " ";
Range range4_2 = (Range)worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[4, 2]];
this.ExcelRange(range4_2, false, false, 0);
worksheet.Cells[4, 3] = TextToHtml(StudentModel.LocationName.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", ""));
Range range4_3 = (Range)worksheet.Range[worksheet.Cells[4, 3], worksheet.Cells[4, 4]];
this.ExcelRange(range4_3, true, false, 0);
worksheet.Cells[4, 5] = " ";
Range range4_5 = (Range)worksheet.Range[worksheet.Cells[4, 5], worksheet.Cells[4, 5]];
this.ExcelRange(range4_5, false, false, 0);
worksheet.Cells[4, 6] = TextToHtml(StudentModel.NationName);
Range range4_6 = (Range)worksheet.Range[worksheet.Cells[4, 6], worksheet.Cells[4, 8]];
this.ExcelRange(range4_6, true, false, 0);
// 4
worksheet.Cells[5, 2] = " ";
Range range5_2 = (Range)worksheet.Range[worksheet.Cells[5, 2], worksheet.Cells[5, 2]];
this.ExcelRange(range5_2, false, false, 0);
worksheet.Cells[5, 3] = TextToHtml(StudentModel.PoliticalName);
Range range5_3 = (Range)worksheet.Range[worksheet.Cells[5, 3], worksheet.Cells[5, 4]];
this.ExcelRange(range5_3, true, false, 0);
worksheet.Cells[5, 5] = " ";
Range range5_5 = (Range)worksheet.Range[worksheet.Cells[5, 5], worksheet.Cells[5, 5]];
this.ExcelRange(range4_5, false, false, 0);
worksheet.Cells[5, 6] =(StudentModel.PoliticalDate == MinDateTime ? string.Empty : StudentModel.PoliticalDate.ToString(DateFormat));
Range range5_6 = (Range)worksheet.Range[worksheet.Cells[5, 6], worksheet.Cells[5, 8]];
this.ExcelRange(range5_6, true, false, 0);
range5_6.NumberFormat = "yyyy-MM-dd";
// 5
worksheet.Cells[6, 2] = " ";
Range range6_2 = (Range)worksheet.Range[worksheet.Cells[6, 2], worksheet.Cells[6, 2]];
this.ExcelRange(range6_2, false, false, 0);
worksheet.Cells[6, 3] =(StudentModel.BirthdayDate == MinDateTime ? string.Empty : StudentModel.BirthdayDate.ToString(DateFormat));
Range range6_3 = (Range)worksheet.Range[worksheet.Cells[6, 3], worksheet.Cells[6, 4]];
this.ExcelRange(range6_3, true, false, 0);
range6_3.NumberFormat = "yyyy-MM-dd";
worksheet.Cells[6, 5] = " ";
Range range6_5 = (Range)worksheet.Range[worksheet.Cells[6, 5], worksheet.Cells[6, 5]];
this.ExcelRange(range6_5, false, false, 0);
worksheet.Cells[6, 6] = TextToHtml(StudentModel.HealthName);
Range range6_6 = (Range)worksheet.Range[worksheet.Cells[6, 6], worksheet.Cells[6, 8]];
this.ExcelRange(range6_6, true, false, 0);
// 6
worksheet.Cells[7, 2] = " ";
Range range7_2 = (Range)worksheet.Range[worksheet.Cells[7, 2], worksheet.Cells[7, 2]];
this.ExcelRange(range7_2, false, false, 0);
worksheet.Cells[7, 3] = TextToHtml(StudentModel.LocationAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", ""));
Range range7_3 = (Range)worksheet.Range[worksheet.Cells[7, 3], worksheet.Cells[7, 4]];
this.ExcelRange(range7_3, true, false, 0);
worksheet.Cells[7, 5] = " ";
Range range7_5 = (Range)worksheet.Range[worksheet.Cells[7, 5], worksheet.Cells[7, 5]];
this.ExcelRange(range7_5, false, false, 0);
worksheet.Cells[7, 6] = TextToHtml(StudentModel.BirthDayAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", ""));
Range range7_6 = (Range)worksheet.Range[worksheet.Cells[7, 6], worksheet.Cells[7, 8]];
this.ExcelRange(range7_6, true, false, 0);
// 7
worksheet.Cells[8, 2] = " ";
Range range8_2 = (Range)worksheet.Range[worksheet.Cells[8, 2], worksheet.Cells[8, 2]];
this.ExcelRange(range8_2, false, false, 0);
worksheet.Cells[8, 3] = TextToHtml(StudentModel.BirthDayAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace(" ", ""));
Range range8_3 = (Range)worksheet.Range[worksheet.Cells[8, 3], worksheet.Cells[8, 4]];
this.ExcelRange(range8_3, true, false, 0);
worksheet.Cells[8, 5] = " ";
Range range8_5 = (Range)worksheet.Range[worksheet.Cells[8, 5], worksheet.Cells[8, 5]];
this.ExcelRange(range8_5, false, false, 0);
worksheet.Cells[8, 6] = TextToHtml(StudentModel.Blood);
Range range8_6 = (Range)worksheet.Range[worksheet.Cells[8, 6], worksheet.Cells[8, 10]];
this.ExcelRange(range8_6, true, false, 0);
// 8
worksheet.Cells[9, 2] = " ";
Range range9_2 = (Range)worksheet.Range[worksheet.Cells[9, 2], worksheet.Cells[9, 2]];
this.ExcelRange(range9_2, false, false, 0);
worksheet.Cells[9, 3] = TextToHtml(StudentModel.ScienceSessionName);
Range range9_3 = (Range)worksheet.Range[worksheet.Cells[9, 3], worksheet.Cells[9, 4]];
this.ExcelRange(range9_3, true, false, 0);
worksheet.Cells[9, 5] = " ";
Range range9_5 = (Range)worksheet.Range[worksheet.Cells[9, 5], worksheet.Cells[9, 5]];
this.ExcelRange(range9_5, false, false, 0);
worksheet.Cells[9, 6] ="'"+TextToHtml(StudentModel.IDNumber);
Range range9_6 = (Range)worksheet.Range[worksheet.Cells[9, 6], worksheet.Cells[9, 10]];
this.ExcelRange(range9_6, true, false, 0);
// 9
worksheet.Cells[10, 2] = " ";
Range range10_2 = (Range)worksheet.Range[worksheet.Cells[10, 2], worksheet.Cells[10, 2]];
this.ExcelRange(range10_2, false, false, 0);
worksheet.Cells[10, 3] = (StudentModel.IsForeign == decimal.One ? " " : " ");
Range range10_3 = (Range)worksheet.Range[worksheet.Cells[10, 3], worksheet.Cells[10, 4]];
this.ExcelRange(range10_3, true, false, 0);
worksheet.Cells[10, 5] = " ";
Range range10_5 = (Range)worksheet.Range[worksheet.Cells[10, 5], worksheet.Cells[10, 5]];
this.ExcelRange(range10_5, false, false, 0);
worksheet.Cells[10, 6] = TextToHtml(StudentModel.PostalCode);
Range range10_6 = (Range)worksheet.Range[worksheet.Cells[10, 6], worksheet.Cells[10, 10]];
this.ExcelRange(range10_6, true, false, 0);
// 10
worksheet.Cells[11, 2] = " ";
Range range11_2 = (Range)worksheet.Range[worksheet.Cells[11, 2], worksheet.Cells[11, 2]];
this.ExcelRange(range11_2, false, false, 0);
worksheet.Cells[11, 3] = "'"+TextToHtml(StudentModel.Phone);
Range range11_3 = (Range)worksheet.Range[worksheet.Cells[11, 3], worksheet.Cells[11, 4]];
this.ExcelRange(range11_3, true, false, 0);
worksheet.Cells[11, 5] = " ";
Range range11_5 = (Range)worksheet.Range[worksheet.Cells[11, 5], worksheet.Cells[11, 5]];
this.ExcelRange(range11_5, false, false, 0);
worksheet.Cells[11, 6] = TextToHtml(StudentModel.Email);
Range range11_6 = (Range)worksheet.Range[worksheet.Cells[11, 6], worksheet.Cells[11, 10]];
this.ExcelRange(range11_6, true, false, 0);
// 11
worksheet.Cells[12, 2] = " ";
Range range12_2 = (Range)worksheet.Range[worksheet.Cells[12, 2], worksheet.Cells[12, 2]];
this.ExcelRange(range12_2, false, false, 0);
worksheet.Cells[12, 3] = TextToHtml(StudentModel.Address);
Range range12_3 = (Range)worksheet.Range[worksheet.Cells[12, 3], worksheet.Cells[12, 10]];
this.ExcelRange(range12_3, true, false, 0);
// 12
worksheet.Cells[13, 2] = " ";
Range range13_2 = (Range)worksheet.Range[worksheet.Cells[13, 2], worksheet.Cells[3, 2]];
this.ExcelRange(range13_2, false, false, 0);
worksheet.Cells[13, 3] = TextToHtml(StudentModel.Address);
Range range13_3 = (Range)worksheet.Range[worksheet.Cells[13, 3], worksheet.Cells[13, 10]];
this.ExcelRange(range13_3, true, false, 0);
// 13
worksheet.Cells[14, 2] = " ";
Range range14_2 = (Range)worksheet.Range[worksheet.Cells[14, 2], worksheet.Cells[14, 2]];
this.ExcelRange(range14_2, false, false, 0);
worksheet.Cells[14, 3] = TextToHtml(StudentModel.CurrentAddress);
Range range14_3 = (Range)worksheet.Range[worksheet.Cells[14, 3], worksheet.Cells[14, 10]];
this.ExcelRange(range14_3, true, false, 0);
// 14
worksheet.Cells[15, 2] = " ";
Range range15_2 = (Range)worksheet.Range[worksheet.Cells[15, 2], worksheet.Cells[15, 2]];
this.ExcelRange(range15_2, false, false, 0);
worksheet.Cells[15, 3] = TextToHtml(StudentModel.Speciality);
Range range15_3 = (Range)worksheet.Range[worksheet.Cells[15, 3], worksheet.Cells[15, 10]];
this.ExcelRange(range15_3, true, false, 0);
//---------------------------------------- -----------------------------------------------------
worksheet.Cells[16, 1] = " ";
Range range16_1 = (Range)worksheet.Range[worksheet.Cells[16, 1], worksheet.Cells[16, 1]];
this.ExcelRange(range16_1, false, true, 1);
worksheet.Cells[16, 2] = " ";
Range range16_2 = (Range)worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[16, 2]];
this.ExcelRange(range16_2, false, false, 1);
worksheet.Cells[16, 3] = (StudentModel.IsVillageStay == decimal.One ? " " : " ");
Range range16_3 = (Range)worksheet.Range[worksheet.Cells[16, 3], worksheet.Cells[16, 3]];
this.ExcelRange(range16_3, false, false, 0);
worksheet.Cells[16, 4] = " ";
Range range16_4 = (Range)worksheet.Range[worksheet.Cells[16, 4], worksheet.Cells[16, 4]];
this.ExcelRange(range16_4, false, false, 1);
worksheet.Cells[16, 5] = (StudentModel.IsOnlyChild == decimal.One ? " " : " ");
Range range16_5 = (Range)worksheet.Range[worksheet.Cells[16, 5], worksheet.Cells[16, 5]];
this.ExcelRange(range16_5, false, false, 0);
worksheet.Cells[16, 6] = " ";
Range range16_6 = (Range)worksheet.Range[worksheet.Cells[16, 6], worksheet.Cells[16, 6]];
this.ExcelRange(range16_6, false, false, 1);
worksheet.Cells[16, 7] = (StudentModel.IsOrphan == decimal.One ? " " : " ");
Range range16_7 = (Range)worksheet.Range[worksheet.Cells[16, 7], worksheet.Cells[16, 10]];
this.ExcelRange(range16_7, true, false, 0);
//------------------------------------------- ---------------------------------------------
worksheet.Cells[17, 1] = " ";
Range range17_1 = (Range)worksheet.Range[worksheet.Cells[17, 1], worksheet.Cells[22, 1]];
this.ExcelRange(range17_1, true, true, 1);
worksheet.Cells[17, 2] = " ";
Range range17_2 = (Range)worksheet.Range[worksheet.Cells[17, 2], worksheet.Cells[17, 2]];
this.ExcelRange(range17_2, true, false, 1);
worksheet.Cells[17, 3] = " ";
Range range17_3 = (Range)worksheet.Range[worksheet.Cells[17, 3], worksheet.Cells[17, 3]];
this.ExcelRange(range17_3, false, false, 1);
worksheet.Cells[17, 4] = " ";
Range range17_4 = (Range)worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[17, 4]];
this.ExcelRange(range17_4, false, false, 1);
worksheet.Cells[17, 5] = " ";
Range range17_5 = (Range)worksheet.Range[worksheet.Cells[17, 5], worksheet.Cells[17, 5]];
this.ExcelRange(range17_5, false, false, 1);
worksheet.Cells[17, 6] = " ";
Range range17_6 = (Range)worksheet.Range[worksheet.Cells[17, 6], worksheet.Cells[17, 6]];
this.ExcelRange(range17_6, false, false, 1);
worksheet.Cells[17, 7] = " ";
Range range17_7 = (Range)worksheet.Range[worksheet.Cells[17, 7], worksheet.Cells[17, 7]];
this.ExcelRange(range17_7, false, false, 1);
worksheet.Cells[17, 8] = " ";
Range range17_8 = (Range)worksheet.Range[worksheet.Cells[17, 8], worksheet.Cells[17, 8]];
this.ExcelRange(range17_8, false, false, 1);
worksheet.Cells[17, 9] = " ";
Range range17_9 = (Range)worksheet.Range[worksheet.Cells[17, 9], worksheet.Cells[17, 10]];
this.ExcelRange(range17_9, true, false, 1);
var RowCountIndex = 18;//
var index = 1;
foreach (var tt in FamilyList)
{
worksheet.Cells[RowCountIndex, 2] = index.ToString();
Range range18_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]];
this.ExcelRange(range18_2, false, false, 1);
worksheet.Cells[RowCountIndex, 3] = TextToHtml(tt.NumberRelation);
Range range18_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]];
this.ExcelRange(range18_3, false, false, 1);
worksheet.Cells[RowCountIndex, 4] = (tt.IsGuardianShip == decimal.One ? " " : " ");
Range range18_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]];
this.ExcelRange(range18_4, false, false, 1);
worksheet.Cells[RowCountIndex, 5] = TextToHtml(tt.NemberName);
Range range18_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]];
this.ExcelRange(range18_5, false, false, 1);
worksheet.Cells[RowCountIndex, 6] = TextToHtml(tt.Education);
Range range18_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 6]];
this.ExcelRange(range18_6, false, false, 1);
worksheet.Cells[RowCountIndex, 7] = "'"+TextToHtml(tt.Telephone);
Range range18_7 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 7], worksheet.Cells[RowCountIndex, 7]];
this.ExcelRange(range18_7, false, false, 0);
worksheet.Cells[RowCountIndex, 8] = TextToHtml(tt.Job);
Range range18_8 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 8], worksheet.Cells[RowCountIndex, 8]];
this.ExcelRange(range18_8, false, false, 0);
worksheet.Cells[RowCountIndex, 9] = TextToHtml(tt.WorkPlace);
Range range18_9 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 9], worksheet.Cells[RowCountIndex, 10]];
this.ExcelRange(range18_9, true, false, 0);
RowCountIndex++;
index++;
}
for (int i = 0; i < 5 - FamilyList.Count; i++)
{
worksheet.Cells[RowCountIndex, 2] = index.ToString();
Range range18_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]];
this.ExcelRange(range18_2, false, false, 1);
worksheet.Cells[RowCountIndex, 3] = "";
Range range18_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]];
this.ExcelRange(range18_3, false, false, 1);
worksheet.Cells[RowCountIndex, 4] = "";
Range range18_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]];
this.ExcelRange(range18_4, false, false, 1);
worksheet.Cells[RowCountIndex, 5] = "";
Range range18_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]];
this.ExcelRange(range18_5, false, false, 1);
worksheet.Cells[RowCountIndex, 6] = "";
Range range18_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 6]];
this.ExcelRange(range18_6, false, false, 1);
worksheet.Cells[RowCountIndex, 7] = "";
Range range18_7 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 7], worksheet.Cells[RowCountIndex, 7]];
this.ExcelRange(range18_7, false, false, 0);
worksheet.Cells[RowCountIndex, 8] = "";
Range range18_8 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 8], worksheet.Cells[RowCountIndex, 8]];
this.ExcelRange(range18_8, false, false, 0);
worksheet.Cells[RowCountIndex, 9] = "";
Range range18_9 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 9], worksheet.Cells[RowCountIndex, 10]];
this.ExcelRange(range18_9, true, false, 0);
RowCountIndex++;
index++;
}
//------------------------------------------- ---------------------------------------------
worksheet.Cells[23, 1] = " ";
Range range23_1 = (Range)worksheet.Range[worksheet.Cells[23, 1], worksheet.Cells[28, 1]];
this.ExcelRange(range23_1, true, true, 1);
worksheet.Cells[23, 2] = " ";
Range range23_2 = (Range)worksheet.Range[worksheet.Cells[23, 2], worksheet.Cells[23, 2]];
this.ExcelRange(range23_2, false, false, 1);
worksheet.Cells[23, 3] = " ";
Range range23_3 = (Range)worksheet.Range[worksheet.Cells[23, 3], worksheet.Cells[23, 3]];
this.ExcelRange(range23_3, false, false, 1);
worksheet.Cells[23, 4] = " ";
Range range23_4 = (Range)worksheet.Range[worksheet.Cells[23, 4], worksheet.Cells[23, 4]];
this.ExcelRange(range23_4, false, false, 1);
worksheet.Cells[23, 5] = " ";
Range range23_5 = (Range)worksheet.Range[worksheet.Cells[23, 5], worksheet.Cells[23, 5]];
this.ExcelRange(range23_5, false, false, 1);
worksheet.Cells[23, 6] = " ";
Range range23_6 = (Range)worksheet.Range[worksheet.Cells[23, 6], worksheet.Cells[23, 10]];
this.ExcelRange(range23_6, true, false, 1);
RowCountIndex = 24;
index = 1;
foreach (var item in StudentPremiumList)
{
worksheet.Cells[RowCountIndex, 2] = index.ToString();
Range range24_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]];
this.ExcelRange(range24_2, false, false, 1);
worksheet.Cells[RowCountIndex, 3] = TextToHtml(item.PremiumName);
Range range24_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]];
this.ExcelRange(range24_3, false, false, 0);
worksheet.Cells[RowCountIndex, 4] = TextToHtml(item.PremiumLevelName);
Range range24_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]];
this.ExcelRange(range24_4, false, false, 1);
worksheet.Cells[RowCountIndex, 5] =(item.PremiumDate == MinDateTime ? string.Empty : item.PremiumDate.ToString(DateFormat));
Range range24_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]];
this.ExcelRange(range24_5, false, false, 1);
range24_5.NumberFormat = "yyyy-MM-dd";
worksheet.Cells[RowCountIndex, 6] = TextToHtml(item.PremiumAddress);
Range range24_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 10]];
this.ExcelRange(range24_6, true, false, 0);
RowCountIndex++;
index++;
}
for (int i = 0; i < 5 - StudentPremiumList.Count; i++)
{
worksheet.Cells[RowCountIndex, 2] = index.ToString();
Range range24_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]];
this.ExcelRange(range24_2, false, false, 1);
worksheet.Cells[RowCountIndex, 3] = "";
Range range24_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]];
this.ExcelRange(range24_3, false, false, 0);
worksheet.Cells[RowCountIndex, 4] = "";
Range range24_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]];
this.ExcelRange(range24_4, false, false, 1);
worksheet.Cells[RowCountIndex, 5] = "";
Range range24_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]];
this.ExcelRange(range24_5, false, false, 1);
worksheet.Cells[RowCountIndex, 6] = "";
Range range24_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 10]];
this.ExcelRange(range24_6, true, false, 0);
RowCountIndex++;
index++;
}
//------------------------------------------- ---------------------------------------------
worksheet.Cells[29, 1] = " ";
Range range29_1 = (Range)worksheet.Range[worksheet.Cells[29, 1], worksheet.Cells[29, 1]];
range29_1.RowHeight = 80;
//
range29_1.Font.Size = 10;
range29_1.Font.Bold = true;
//
range29_1.Font.ColorIndex = 0;
//
range29_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//
range29_1.Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Cells[29, 2] =StudentModel.Remark; //TextToHtml()
Range range29_2 = (Range)worksheet.Range[worksheet.Cells[29, 2], worksheet.Cells[29, 10]];
range29_2.RowHeight = 80;
//
range29_2.Font.Size = 10;
range29_2.MergeCells = true;
//
range29_2.Font.ColorIndex = 0;
//
range29_2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//
range29_2.Borders.LineStyle = XlLineStyle.xlContinuous;
range29_2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
range29_2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range29_2.WrapText = true;//
worksheet.Cells[30, 1] = " , , , ";
Range range30_1 = (Range)worksheet.Range[worksheet.Cells[30, 1], worksheet.Cells[30, 10]];
range30_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
range30_1.Borders.LineStyle = XlLineStyle.xlContinuous;
range30_1.MergeCells = true;
range30_1.RowHeight = 20;
range30_1.Font.ColorIndex = 0;
range30_1.Font.Size = 10;
worksheet.Cells[31, 1] = " : : ";
Range range31_1 = (Range)worksheet.Range[worksheet.Cells[31, 1], worksheet.Cells[31, 10]];
range31_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
range31_1.Borders.LineStyle = XlLineStyle.xlContinuous;
range31_1.MergeCells = true;
range31_1.RowHeight = 20;
range31_1.Font.ColorIndex = 0;
range31_1.Font.Size = 10;
//excel.Visible = true;
worksheet.Cells[32, 1] = " ";
Range range32_1 = (Range)worksheet.Range[worksheet.Cells[32, 1], worksheet.Cells[32, 10]];
range32_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
range32_1.Borders.LineStyle = XlLineStyle.xlContinuous;
range32_1.MergeCells = true;
range32_1.RowHeight = 20;
range32_1.Font.ColorIndex = 0;
range32_1.Font.Size = 10;
//------------------------------------ ---------------------------------------------------------
Microsoft.Office.Interop.Excel.Range rangeImage = excel.Range[excel.Cells[2, 9], excel.Cells[7, 10]];
rangeImage.Borders.LineStyle = XlLineStyle.xlContinuous;
rangeImage.MergeCells = true;
rangeImage.Select();
Microsoft.Office.Interop.Excel.Range pic2 = excel.Range[excel.Cells[2, 9], excel.Cells[7, 10]];
pic2.Select();
float picleft, pictop;
picleft = Convert.ToSingle(pic2.Left + 0.35);
pictop = Convert.ToSingle(pic2.Top + 1);
float width, height;
width = 118;
height = 115;
worksheet.Shapes.AddPicture(ImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, picleft, pictop, width, height);
workbook.SaveAs(ExcelPath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
workbook.RefreshAll();
worksheet = null;
}
catch (Exception err)
{
this.ShowMessage(" Excel : " + Environment.NewLine + err.Message);
}
finally
{
if (workbook != null)
{
workbook.Close(false, Missing.Value, Missing.Value);
workbook = null;
}
if (excel != null)
{
excel.Quit();
excel = null;
}
GC.Collect();
}
}