public void Dispose()
{
if (_tempSheet != null)
{
_tempSheet.Delete();
_tempSheet.Dispose();
_tempSheet = null;
}
}
public void Dispose(bool disposing)
{
if (disposing)
{
_worksheet.Dispose();
_workbook.Dispose();
_stream.Close();
_stream.Dispose();
}
}
/// <summary>
/// Changes to using the passed in sheet. Note that changing to a new sheet automatically resets the
/// internal row and column counter used by WriteRecords.
/// </summary>
/// <param name="sheet">Sheet to change to</param>
public void ChangeSheet(
int sheet)
{
// Peform any column resizing for the current sheet before we change it
PerformColumnResize();
// Insert all the sheets up to the index we need if the count is less
var sheets = _book.Worksheets;
if (sheet >= sheets.Count)
{
for (var i = sheets.Count; i <= sheet; i++)
{
sheets.Add($"Sheet{i+1}");
}
}
// Dispose of the old sheet and reference the new one
_sheet?.Dispose();
_sheet = sheets.Worksheet(sheet + 1);
_row = _col = 1;
}
private static void CreateXlBook(string bookFilename)
{
XLWorkbook myXlBook = null;
IXLWorksheet myXlSheet = null;
try
{
// Initialize a new Workbook
myXlBook = new XLWorkbook();
// Check to see if a specific worksheet exists, if not, Add it.
string mySheetname = "VENUS";
if (myXlBook.Worksheets.Where(w => w.Name == mySheetname).Count() == 0)
{
myXlSheet = myXlBook.Worksheets.Add(mySheetname);
Console.WriteLine(" + worksheet created = " + mySheetname);
}
// Add worksheet content ...
myXlSheet.Cell(1, 1).Value = "Hello World!";
// Save the Workbook
if (File.Exists(bookFilename))
{
Console.Write(" - deleting old file ... ");
File.Delete(bookFilename);
Console.WriteLine(" old file deleted.");
}
Console.Write(" * Saving XL Workbook -> " + bookFilename + " ... ");
myXlBook.SaveAs(bookFilename);
Console.WriteLine("Workbook Saved!");
}
catch (Exception xlException)
{
Console.WriteLine("XL Creation Error -> " + xlException.Message);
if (xlException.InnerException != null)
{
Console.WriteLine(" --> " + xlException.InnerException.Message);
}
}
// clean up
myXlSheet.Dispose();
myXlBook.Dispose();
}
/// <summary>
/// Releases unmanaged and - optionally - managed resources.
/// </summary>
/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (disposeWorksheet)
{
worksheet.Dispose();
}
if (disposeWorkbook)
{
workbook.SaveAs(path);
workbook.Dispose();
}
}
disposed = true;
}
public void Dispose()
{
_ws?.Dispose();
_wb?.Dispose();
}
public MemoryStream ObjectToExcel <TModel>(TModel obj) where TModel : ExcelReplyBase
{
Type type = obj.GetType();
MemoryStream fs = new MemoryStream();
var typeProcessorMap = new Dictionary <Type, Delegate>
{
{ typeof(ApplyExcelReplyDataModel), new Action <ExcelReplyBase>(m => {
/* Excel Bind*/
ApplyExcelReplyDataModel model = (m as ApplyExcelReplyDataModel);
try
{
string folderPath = "~/Content/ExcelTemp/";
string TemplateFile = string.Empty;
int rangeDefualtLength = 0;
switch (model.GetExcelFormType)
{
case ExcelForm.EmptyForm1:
TemplateFile = HttpContext.Current.Server.MapPath(folderPath + "Apply.xlsx");
rangeDefualtLength = 17;
break;
default:
throw new Exception("can't get method with excel base file.");
}
XLWorkbook exl = new XLWorkbook(TemplateFile);
IXLWorksheet wsht = exl.Worksheet(1);
wsht.RangeUsed().SetAutoFilter();
//var defualtRowHeight = wsht.Row(2).Height;
var defualtRowHeight = 80;
var sexCell = wsht.Cell("G2").Value;
int satartRow = 3;
/*
* 報名編號 隊名 參賽組別 教練 聯絡人 聯絡電話 E-mail 其他 隊長 隊員
*/
string docTitle = "比賽報名明細表";
wsht.Cell(1, 1).Value = string.Concat(model.ActivityName, docTitle);
wsht.Cell(2, 2).Value = model.ActivityName;
if (model.GetExcelFormType == ExcelForm.EmptyForm1)
{
foreach (var data in model.ApplyListData)
{
wsht.Cell(++satartRow, 1).Value = data.ApplyNumber;
wsht.Cell(satartRow, 2).Value = data.ApplyTeamName;
wsht.Cell(satartRow, 3).Value = model.GroupList.Where(o => o.Key == data.GroupID).First().Value;
wsht.Cell(satartRow, 4).Value = data.Coach;
wsht.Cell(satartRow, 5).Value = data.Contact;
wsht.Cell(satartRow, 6).Value = data.ContactPhone;
wsht.Cell(satartRow, 7).Value = data.ContactEmail;
wsht.Cell(satartRow, 8).Value = data.Remark;
int mbIndex = 10;
foreach (var mb in data.MemberInfo)
{
string memberInfo = string.Format("{0}\r\n{1}\r\n{2}", mb.MemberName, mb.MemberBirthday, mb.MemberIdentityID);
if (mb.MemberType == "Leader")
{
wsht.Cell(satartRow, 9).Value = memberInfo;
}
else
{
wsht.Cell(satartRow, mbIndex).Value = memberInfo;
mbIndex++;
}
}
wsht.Row(satartRow).Height = defualtRowHeight;
}
}
wsht.RangeUsed().SetAutoFilter(); //Add autofilter
wsht.Columns().AdjustToContents();
//wsht.FirstRowUsed().Style.Fill.BackgroundColor = XLColor.PowderBlue;
//wsht.FirstRowUsed().Style.Font.Bold = true;
satartRow = wsht.RowsUsed().Count();
IXLRange rangeFirst = wsht.Range(1, 1, 1, rangeDefualtLength);
rangeFirst.Style.Fill.BackgroundColor = XLColor.PowderBlue;
rangeFirst.Style.Font.Bold = true;
rangeFirst.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
IXLRange range = wsht.Range(4, 1, satartRow, rangeDefualtLength);
range.Style.Font.FontSize = 12;
//水平置中
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//垂直置中
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;;
range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range.Style.Alignment.WrapText = true;
range.Style.Font.SetFontName("細明體");
exl.SaveAs(fs);
fs.Position = 0;
fs.Close();
fs.Dispose();
wsht.Dispose();
exl.Dispose();
}
catch (Exception ex)
{
model.IsSuccess = false;
model.Message = ex.Message;
}
model.IsSuccess = true;
}) },
};
typeProcessorMap[type].DynamicInvoke(obj);
if (!obj.IsSuccess)
{
throw new Exception(obj.Message);
}
return(fs);
}
public void Dispose()
{
_template?.Dispose();
_ws?.Dispose();
_wb?.Dispose();
}
///------------------------------------------------------------------
/// <summary>
/// 会員稼働予定テーブル更新メインルーチン
/// : closedXML版 2018/02/22</summary>
/// <param name="sFile">
/// 予定申告エクセルファイルパス</param>
///------------------------------------------------------------------
private void dataOutputXML(string sFile, DateTime sDt)
{
string lg = string.Empty;
jfgDataClassDataContext db = new jfgDataClassDataContext();
// 個人データクラス
clsPersonalData cPData = new clsPersonalData();
cPData.sDt = sDt;
try
{
using (var book = new XLWorkbook(sFile, XLEventTracking.Disabled))
{
// ワークシートを取得する
IXLWorksheet sheet = book.Worksheet(1);
// 予定申告書シートよりカード番号取得
cPData.cNo = Utility.cNumberCheck(Utility.nulltoString(sheet.Cell(2, 11).Value).Trim());
if (cPData.cNo == -1)
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
"カード番号が正しい数字情報ではありません : 更新から除外されました。";
// ログ配列に出力
arrayLog(lg);
// 戻る
return;
}
// 会員情報よりフリガナを取得
if (!db.会員情報.Any(a => a.カード番号 == cPData.cNo))
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
"カード番号に該当する会員が存在しません [" + cPData.cNo.ToString() + "] : 更新から除外されました。";
// ログ配列に出力
arrayLog(lg);
// 戻る
return;
}
else
{
foreach (var t in db.会員情報.Where(a => a.カード番号 == cPData.cNo))
{
cPData.furi = t.フリガナ;
}
}
// 各月の予定を会員稼働予定テーブルに書き込む
for (int i = 0; i < 6; i++)
{
// 年を取得
cPData.eYear = cIntCheck(rangeNullToString(Utility.nulltoString(sheet.Cell(5, i * 4 + 2).Value).Trim()));
if (cPData.eYear == -1)
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
(i + 1).ToString() + "番目の年が正しくありません : 更新から除外されました。";
// ログ配列に出力
arrayLog(lg);
// 次ぎへ
continue;
}
// 月を取得
cPData.eMonth = cIntCheck(Utility.nulltoString(sheet.Cell(5, i * 4 + 3).Value).Trim());
if (cPData.eMonth == -1)
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
(i + 1).ToString() + "番目の月が正しくありません : 更新から除外されました。";
// ログ配列に出力
arrayLog(lg);
// 次ぎへ
continue;
}
// 過去の予定は更新しない
int nowYYMM = DateTime.Today.Year * 100 + DateTime.Today.Month;
int xlsYYMM = cPData.eYear * 100 + cPData.eMonth;
if (xlsYYMM < nowYYMM)
{
continue; // 次へ
}
// 連絡事項を取得
cPData.memo = Utility.nulltoString(sheet.Cell(2, 15).Value).Trim();
// 該当列を取得
cPData.colidx = i * 4 + 3;
// エクセルシートオブジェクトを取得
cPData.sheet = sheet;
// データ書き込み
if (saveData(cPData))
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
cPData.eYear.ToString() + "年" + cPData.eMonth.ToString() + "月の予定が登録されました。";
}
else
{
// ログ文字列作成
lg = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + System.IO.Path.GetFileName(sFile) + " " +
cPData.eYear.ToString() + "年" + cPData.eMonth.ToString() + "月の予定の登録に失敗しました。";
}
// ログ配列に出力
arrayLog(lg);
}
// シート後片付け
sheet.Dispose();
}
}
catch (Exception e)
{
}
finally
{
}
}