// TODO: 是否超期列也应能从 returningDate 计算出，以适应非 AdvanceXml 的情况
// parameters:
// bAdvanceXml 是否为 AdvanceXml 情况
static void OutputBorrows(IXLWorksheet sheet,
XmlDocument dom,
Delegate_GetBiblioSummary procGetBiblioSummary,
bool bAdvanceXml,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("borrows/borrow");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 在借 --- " + nodes.Count,
XLColor.DarkGreen,
2,
7);
List<IXLCell> cells = new List<IXLCell>();
// 册信息若干行的标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("借阅时间");
titles.Add("借期");
titles.Add("应还时间");
titles.Add("是否超期");
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strBorrowDate = ToLocalTime(borrow.GetAttribute("borrowDate"), "yyyy-MM-dd HH:mm");
string strBorrowPeriod = GetDisplayTimePeriodString(borrow.GetAttribute("borrowPeriod"));
string strReturningDate = ToLocalTime(borrow.GetAttribute("returningDate"), "yyyy-MM-dd");
string strRecPath = borrow.GetAttribute("recPath");
string strIsOverdue = borrow.GetAttribute("isOverdue");
bool bIsOverdue = DomUtil.IsBooleanTrue(strIsOverdue, false);
string strOverdueInfo = borrow.GetAttribute("overdueInfo1");
if (bAdvanceXml == false)
{
string strPeriod = borrow.GetAttribute("borrowPeriod");
string strRfc1123String = borrow.GetAttribute("returningDate");
if (string.IsNullOrEmpty(strRfc1123String) == false)
{
try
{
DateTime time = DateTimeUtil.FromRfc1123DateTimeString(strRfc1123String);
TimeSpan delta = DateTime.Now - time.ToLocalTime();
if (strPeriod.IndexOf("hour") != -1)
{
if (delta.Hours > 0)
{
strOverdueInfo = "已超期 " + delta.Hours + " 小时";
bIsOverdue = true;
}
}
else
{
if (delta.Days > 0)
{
strOverdueInfo = "已超期 " + delta.Days + " 天";
bIsOverdue = true;
}
}
}
catch
{
}
}
}
string strSummary = borrow.GetAttribute("summary");
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
int nRet = procGetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strBorrowDate);
cols.Add(strBorrowPeriod);
cols.Add(strReturningDate);
if (bIsOverdue)
cols.Add(strOverdueInfo);
else
cols.Add("");
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
// 超期的行为黄色背景
if (bIsOverdue)
{
var line = sheet.Range(nRowIndex, 2, nRowIndex, 2 + cols.Count - 1);
line.Style.Fill.BackgroundColor = XLColor.Yellow;
}
nItemIndex++;
nRowIndex++;
}
// 册信息标题下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
#if NO
// 第一行上面的横线
rngData = sheet.Range(cell_no, cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Medium;
#endif
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
static void OutputOverdues(IXLWorksheet sheet,
XmlDocument dom,
Delegate_GetBiblioSummary procGetBiblioSummary,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("overdues/overdue");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 费用 --- " + nodes.Count,
XLColor.DarkRed,
2,
6);
int nRet = 0;
List<IXLCell> cells = new List<IXLCell>();
// 栏目标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("说明");
titles.Add("金额");
titles.Add("ID");
#if NO
titles.Add("以停代金情况");
titles.Add("起点日期");
titles.Add("期限");
titles.Add("终点日期");
#endif
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strReason = borrow.GetAttribute("reason");
string strPrice = borrow.GetAttribute("price");
string strID = borrow.GetAttribute("id");
string strRecPath = borrow.GetAttribute("recPath");
string strSummary = borrow.GetAttribute("summary");
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
nRet = procGetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strReason);
cols.Add(strPrice);
cols.Add(strID);
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
nColIndex++;
cells.Add(cell);
}
nItemIndex++;
nRowIndex++;
}
// 标题行下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
// 创建读者详情 Excel 文件。这是便于被外部调用的版本，只需要提供读者 XML 记录即可
// return:
// -1 出错
// 0 用户中断
// 1 成功
public static int CreateReaderDetailExcelFile(List<string> xmls,
Delegate_GetBiblioSummary procGetBiblioSummary,
Stop stop,
bool bAdvanceXml,
bool bLaunchExcel,
out string strError)
{
strError = "";
//int nRet = 0;
ExportPatronExcelDialog dlg = new ExportPatronExcelDialog();
MainForm.SetControlFont(dlg, Program.MainForm.Font, false);
dlg.UiState = Program.MainForm.AppInfo.GetString(
"ReaderSearchForm",
"ExportPatronExcelDialog_uiState",
"");
Program.MainForm.AppInfo.LinkFormState(dlg, "ReaderSearchForm_ExportPatronExcelDialog_uiState_state");
dlg.ShowDialog(Program.MainForm);
Program.MainForm.AppInfo.SetString(
"ReaderSearchForm",
"ExportPatronExcelDialog_uiState",
dlg.UiState);
if (dlg.DialogResult == System.Windows.Forms.DialogResult.Cancel)
{
strError = "放弃操作";
return 0;
}
string strTimeRange = "";
try
{
strTimeRange = GetTimeRange(dlg.ChargingHistoryDateRange);
}
catch (Exception ex)
{
strError = "日期范围字符串 '" + dlg.ChargingHistoryDateRange + "' 格式不合法: " + ex.Message;
return -1;
}
#if NO
// 询问文件名
SaveFileDialog dlg = new SaveFileDialog();
dlg.Title = "请指定要输出的 Excel 文件名";
dlg.CreatePrompt = false;
dlg.OverwritePrompt = true;
// dlg.FileName = this.ExportExcelFilename;
// dlg.InitialDirectory = Environment.CurrentDirectory;
dlg.Filter = "Excel 文件 (*.xlsx)|*.xlsx|All files (*.*)|*.*";
dlg.RestoreDirectory = true;
if (dlg.ShowDialog() != DialogResult.OK)
return 0;
#endif
XLWorkbook doc = null;
try
{
doc = new XLWorkbook(XLEventTracking.Disabled);
File.Delete(dlg.FileName);
}
catch (Exception ex)
{
strError = "ReaderSearchForm new XLWorkbook() {0BD1CB34-DF8A-4DDB-B884-8A9CF830D7C7} exception: " + ExceptionUtil.GetAutoText(ex);
return -1;
}
IXLWorksheet sheet = null;
sheet = doc.Worksheets.Add("表格");
try
{
if (stop != null)
stop.SetProgressRange(0, xmls.Count);
// 每个列的最大字符数
List<int> column_max_chars = new List<int>();
// TODO: 表的标题，创建时间
int nRowIndex = 3; // 空出前两行
//int nColIndex = 1;
int nReaderIndex = 0;
foreach (string strXml in xmls)
{
Application.DoEvents(); // 出让界面控制权
if (stop != null && stop.State != 0)
return 0;
if (string.IsNullOrEmpty(strXml) == true)
continue;
XmlDocument dom = new XmlDocument();
try
{
dom.LoadXml(strXml);
}
catch (Exception ex)
{
strError = "装载读者记录 XML 到 DOM 时发生错误: " + ex.Message;
return -1;
}
string strBarcode = DomUtil.GetElementText(dom.DocumentElement, "barcode");
//
if (dlg.ExportReaderInfo)
{
OutputReaderInfo(sheet,
dom,
nReaderIndex,
ref nRowIndex,
ref column_max_chars);
}
// 输出在借册表格
if (dlg.ExportBorrowInfo)
{
OutputBorrows(sheet,
dom,
procGetBiblioSummary,
bAdvanceXml,
ref nRowIndex,
ref column_max_chars);
}
// 输出违约金表格
if (dlg.ExportOverdueInfo)
{
OutputOverdues(sheet,
dom,
procGetBiblioSummary,
ref nRowIndex,
ref column_max_chars);
}
if (dlg.ExportChargingHistory)
{
LibraryChannel channel = Program.MainForm.GetChannel();
try
{
ChargingHistoryLoader history_loader = new ChargingHistoryLoader();
history_loader.Channel = channel;
history_loader.Stop = stop;
history_loader.PatronBarcode = strBarcode;
history_loader.TimeRange = strTimeRange;
history_loader.Actions = "return,lost";
history_loader.Order = "descending";
CacheableBiblioLoader summary_loader = new CacheableBiblioLoader();
summary_loader.Channel = channel;
summary_loader.Stop = stop;
summary_loader.Format = "summary";
summary_loader.GetBiblioInfoStyle = GetBiblioInfoStyle.None;
// summary_loader.RecPaths = biblio_recpaths;
// 输出借阅历史表格
// 可能会抛出异常，例如权限不够
OutputBorrowHistory(sheet,
dom,
history_loader,
// this.MainForm.GetBiblioSummary,
summary_loader,
ref nRowIndex,
ref column_max_chars);
}
catch (Exception ex)
{
strError = "输出借阅历史时出现异常: " + ex.Message;
return -1;
}
finally
{
Program.MainForm.ReturnChannel(channel);
}
}
nRowIndex++; // 读者之间的空行
nReaderIndex++;
if (stop != null)
stop.SetProgressValue(nReaderIndex);
}
{
if (stop != null)
stop.SetMessage("正在调整列宽度 ...");
Application.DoEvents();
// 字符数太多的列不要做 width auto adjust
foreach (IXLColumn column in sheet.Columns())
{
int MAX_CHARS = 50; // 60
int nIndex = column.FirstCell().Address.ColumnNumber - 1;
if (nIndex >= column_max_chars.Count)
break;
int nChars = column_max_chars[nIndex];
if (nIndex == 1)
{
column.Width = 10;
continue;
}
if (nIndex == 3)
MAX_CHARS = 50;
else
MAX_CHARS = 24;
if (nChars < MAX_CHARS)
column.AdjustToContents();
else
column.Width = Math.Min(MAX_CHARS, nChars);
}
}
}
finally
{
if (doc != null)
{
doc.SaveAs(dlg.FileName);
doc.Dispose();
}
if (bLaunchExcel)
{
try
{
System.Diagnostics.Process.Start(dlg.FileName);
}
catch
{
}
}
}
return 1;
}