/// <summary>
/// 循环插入数据
/// </summary>
/// <param name="startRowIndex"></param>
/// <param name="setValueAction"></param>
public void CirculateLetterSetValue(int startRowIndex, Func <int, ExcelRowItem, bool> setValueAction)
{
if (_currentSheet.LastRowNum <= startRowIndex)
{
_currentSheet.CreateRow(startRowIndex);
}
int currentRowIndex = startRowIndex;
bool isWhile = false;
int dataIndex = 0;
do
{
ExcelRowItem item = new ExcelRowItem();
isWhile = setValueAction.Invoke(dataIndex, item);
if (isWhile)
{
_currentSheet.CopyRow(currentRowIndex, currentRowIndex + 1);
}
SetRowValue(currentRowIndex, item);
currentRowIndex++;
dataIndex++;
} while (isWhile);
}
/// <summary>
/// 复制行
/// </summary>
/// <param name="rowIndex">复制行行标</param>
/// <returns>待追加的行标增量</returns>
public void CopyRow(int rowIndex)
{
rowIndex = _rowIndexAccumulation.GetCurrentRowIndex(rowIndex);
int span = _sheet.CopyRow(rowIndex);
_rowIndexAccumulation.Add(span);
}
private static void PrintPlanets(DBStar star, ISheet sheet, int i, IRow row, int j, ICell cell, DBPlanet planet)
{
row = sheet.GetRow(i);
cell = row.GetCell(j);
cell.SetCellValue(planet.Name);
cell = row.GetCell(j + 1) ?? row.CreateCell(j + 1);
cell.SetCellValue(planet.MiddleDistanceValue +
planet.MiddleDistanceUnit.ToString());
cell = row.GetCell(j + 2) ?? row.CreateCell(j + 2);
cell.SetCellValue(planet.Radius);
cell = row.GetCell(j + 3) ?? row.CreateCell(j + 3);
cell.SetCellValue(planet.Star);
cell = row.GetCell(j + 4) ?? row.CreateCell(j + 4);
cell.SetCellValue(planet.Temperature);
cell = row.GetCell(j + 5) ?? row.CreateCell(j + 5);
cell.SetCellValue(planet.Type.ToString());
cell = row.GetCell(j + 6) ?? row.CreateCell(j + 6);
cell.SetCellValue(planet.InventingDate);
cell.CellStyle.DataFormat = 14;
cell = row.GetCell(j + 7) ?? row.CreateCell(j + 7);
cell.SetCellValue(planet.HasAtmosphere);
if (planet != star.Planets.Last())
{
row = sheet.CopyRow(i, i + 1);
}
}
private static void PrintStarProp(DBStar star, ISheet sheet, ref int i, IRow row, int j, ICell cell)
{
cell.SetCellValue("Дата открытия");
cell = row.GetCell(j + 1) ?? row.CreateCell(j + 1);
cell.SetCellValue(star.InventingDate);
cell.CellStyle.DataFormat = 14;
row = sheet.CopyRow(i, i + 1);
i++;
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.SetCellValue("Галактика");
row.CreateCell(j + 1).SetCellValue(star.Galaxy ?? "");
row = sheet.CreateRow(i++);
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.SetCellValue("Радиус");
row.CreateCell(j + 1).SetCellValue(star.Radius);
row = sheet.CreateRow(i++);
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.SetCellValue("Температура");
row.CreateCell(j + 1).SetCellValue(star.Temperature);
row = sheet.CreateRow(i++);
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.SetCellValue("Среднее расстояние");
row.CreateCell(j + 1).SetCellValue(star.MiddleDistanceValue);
row = sheet.CreateRow(i++);
}
protected IRow GetNewRow(int rowNum, TBodyList tList)
{
IRow row = null;;
if (mode == ExportMode.Fill)
{
switch (exportSetting.Body.RowMode)
{
case FillRowMode.Copy:
//copy current row if has more row
if (curTIndex < GetCount(tList) - 1)
{
sheet.CopyRow(rowNum, rowNum + 1);
}
row = sheet.GetRow(rowNum);
sheet.GetRow(rowNum + 1).Height = row.Height;
break;
case FillRowMode.Fill:
row = sheet.GetRow(rowNum);
break;
default:
row = sheet.CreateRow(rowNum);
break;
}
}
else
{
row = sheet.CreateRow(rowNum);
}
return(row);
}
/// <summary>
/// 填写表格数据
/// </summary>
/// <param name="tabelName"></param>
/// <param name="data"></param>
public void WriteTable(string tabelName, List <object> datas)
{
//找到所有表格标签
var labels = Labels.Where(x => x is TableLabel label && label.Table == tabelName).Cast <TableLabel>().ToList();
if (labels.Count == 0)
{
return;
}
//获得模板行
//var tempRow = Sheet.GetRow(labels.First().Cell.RowIndex);
var tempRow = labels.First().Cell.Row;
//根据表格行数量创建空表格
for (int i = 0; i < datas.Count - 1; i++)
{
Sheet.CopyRow(tempRow.RowNum, tempRow.RowNum + 1);
}
//填充数据
for (int dataRowIndex = 0; dataRowIndex < datas.Count; dataRowIndex++)
{
var properties = datas[dataRowIndex].GetType().GetProperties();
foreach (var label in labels)
{
var currentCell = Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex);
if (currentCell == null)
{
continue;
}
if (string.IsNullOrWhiteSpace(label.Formula))
{//没有公式，那么只是普通的数据替换
FillValue(currentCell, label.Name, datas[dataRowIndex], properties);
}
else
{//进入公式编辑方式
FillFormula(currentCell, label.Formula, label => Sheet.GetCell(tempRow.RowNum + dataRowIndex, label.Cell.ColumnIndex)?.Address?.FormatAsString());
}
}
}
//计算每列的区域
foreach (var label in labels)
{
label.CellRange = new CellRangeAddress(tempRow.RowNum, tempRow.RowNum + datas.Count - 1, label.Cell.ColumnIndex, label.Cell.ColumnIndex);
}
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
for (int i = 1; i <= count; i++)
{
workSheet.CopyRow(rowIndex - 1, rowIndex);
}
}
catch (Exception e)
{
//this.KillExcelProcess(false);
throw e;
}
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet s = hssfworkbook.GetSheetAt(0);
ICell cell = s.GetRow(4).GetCell(1);
cell.CopyCellTo(3); //copy B5 to D5
IRow c = s.GetRow(3);
c.CopyCell(0, 1); //copy A4 to B4
s.CopyRow(0, 1); //copy row A to row B, original row B will be moved to row C automatically
WriteToFile();
}
private void ExpandSheet(ISheet sheet, DataRow mainRow)
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
{
NamedRange expandRegion = GetExpandRegion(sheet, i, j);
if (expandRegion == null)
{
ReplaceRange(sheet.GetRow(i).GetCell(j), mainRow, 1);
}
else
{
int rowCount = expandRegion.RowCount;
int colCount = expandRegion.ColCount;
DataRowCollection rows = res.Data.Rows;
if (rows != null)
{
for (int k = 0; k < rows.Count; k++)
{
Range range = new Range(sheet, i, i + rowCount - 1, j, j + colCount - 1);
if (j == range.FirstCol && k + 1 < rows.Count)
{
sheet.ShiftRows(range.FirstRow, range.LastRow, rowCount, true, false);
for (int l = range.FirstRow; l <= range.LastRow; l++)
{
sheet.CopyRow(l + rowCount, l);
if (!autoHeight)
{
sheet.GetRow(l).Height = sheet.GetRow(l + rowCount).Height;
}
}
}
ExpandRange(range, rows[k], k + 1);
i += rowCount;
}
}
else
{
ExpandRange(expandRegion, null, 1);
}
j += colCount - 1;
}
}
}
}
/// <summary>
/// 将数据填充到工作表中。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet"></param>
/// <param name="data">列表数据。</param>
/// <param name="document">解析的文档对象。</param>
/// <param name="source">源行，即定义字段标签的行。</param>
private static void FillSheet(ISheet sheet, IList data, ExportingDocument document, IRow source)
{
var index = 0;
var mergedCellIndex = -1;
foreach (var item in data)
{
var row = index == 0 ? sheet.GetRow(source.RowNum) : sheet.CopyRow(source.RowNum, source.RowNum + index);
foreach (var ckv in document.DataSets[0].Properties)
{
ICell cell = null;
if ((cell = row.GetCell(ckv.Key.ColumnIndex)) == null)
{
cell = row.CreateCell(ckv.Key.ColumnIndex);
}
if (index != 0)
{
var scell = source.GetCell(ckv.Key.ColumnIndex);
row.Height = source.Height;
cell.CellStyle = scell.CellStyle;
}
else
{
if (cell.IsMergedCell)
{
mergedCellIndex = ckv.Key.ColumnIndex;
}
}
var properties = TypeDescriptor.GetProperties(item);
var property = properties[ckv.Value.Key];
if (property == null)
{
continue;
}
var value = property == null ?
string.Empty : property.GetValue(item);
SetCellValue(cell, ckv.Value, value);
}
index++;
}
}
public override void SetValue(ISheet sheet, JToken token)
{
JArray data;
if (this.Each == "$root")
{
data = (JArray)token;
}
else
{
data = (JArray)token[Each];
}
var row = sheet.GetRow(RowIndex);
var grouprows = ExcelGroupRow.GetGroupRows(data, TreeCode);
for (int i = 0; i < data.Count; i++)
{
var item = data[i];
foreach (var cellTemplate in Cells)
{
var cell = row.GetCell(cellTemplate.ColIndex);
var val = item[cellTemplate.Bind];
cellTemplate.SetCellValue(cell, val);
}
if (i != data.Count - 1)
{
row = sheet.CopyRow(row.RowNum, row.RowNum + 1);
}
}
var groups = grouprows.Values.OrderByDescending(o => o.TreeCode).ToList();
var b = JsonConvert.SerializeObject(groups);
foreach (var grouprow in groups)
{
sheet.GroupRow(grouprow.StartIndex + RowIndex + 1, grouprow.EndIndex + RowIndex);
}
if (data.Count == 0)
{
sheet.ShiftRows(row.RowNum + 1, sheet.LastRowNum, -1);
}
}
/// <summary>
/// 复制行
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="sourceIndex">源数据行</param>
/// <param name="targetIndex">目标行</param>
public static void CopyRowRelative(this ISheet sheet, int sourceIndex, int targetIndex)
{
GuardUtils.NotNull(sheet, nameof(sheet));
sheet.CopyRow(sourceIndex, targetIndex);
var row = sheet.GetRow(targetIndex);
var columnCount = row.Cells.Count;
for (int j = 0; j < columnCount; j++)
{
var cell = row.GetCell(j);
if (cell.CellType == CellType.Formula)
{
var formula = cell.CellFormula;
formula = CopyFormula(formula, sheet, targetIndex);
cell.SetCellFormula(formula);
}
}
}
protected override void Process(ISheet table)
{
int rowFIO = table.FindCellByMacros(FIOConst).RowIndex;
int rowNumbers = table.FindCellByMacros(numbersConst).RowIndex;
int colNumbers = table.FindCellByMacros(numbersConst).ColumnIndex;
int rowPrice = table.FindCellByMacros(SUM).RowIndex;
double price;
try
{
IRow r = table.GetRow(rowPrice);
price = r.GetCell(table.FindCellByMacros(SUM).ColumnIndex - 1).NumericCellValue;
}
catch
{
price = 0.0;
}
table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter);
table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter);
table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString());
table.FindCellByMacros(FIOConst).SetValue(FIO);
table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString());
table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString());
int rowIndex = table.FindCellByMacros(SUM).RowIndex;
int columnIndex = table.FindCellByMacros(numbersConst).ColumnIndex;
if (ListBsoNumber.Count <= 1)
{
table.FindCellByMacros(SUM).SetValue((price * ListBsoNumber.Count).ToString());
rowIndex = table.FindCellByMacros(numbersConst).RowIndex;
table.SetCellValue(rowIndex, columnIndex, ListBsoNumber.FirstOrDefault());
}
else
{
List <long> listLong = new List <long>(ListBsoNumber.Count);
try
{
//создаем список чисел (парсим номера БСО в long)
foreach (var elem in ListBsoNumber)
{
listLong.Add(long.Parse(elem));
}
//сортируем
listLong.Sort((a, b) => a.CompareTo(b));
//создаем сортированный список БСО
List <string> newListBsoNumber = new List <string>(listLong.Count);
foreach (long l in listLong)
{
newListBsoNumber.Add(string.Format("{0,9:D9}", l));
}
//группируем по диапазонам
Dictionary <string, long> dic = new Dictionary <string, long>();
long temp = listLong.FirstOrDefault();
long count = 0;
dic.Add("с " + newListBsoNumber.FirstOrDefault(), 0);
for (int i = 0; i < listLong.Count; i++)
{
if (listLong[i] - temp > 1)
{
dic.Add("по " + newListBsoNumber[i - 1], count); dic.Add("с " + newListBsoNumber[i], 0); count = 0;
}
temp = listLong[i];
count++;
}
dic.Add("по " + newListBsoNumber.LastOrDefault(), count);
//если много данных - двигаем 2 последние строчки еще ниже
if (dic.Count > 10)
{
table.CopyRow(rowFIO, rowFIO + dic.Count - 9);
table.CopyRow(rowFIO + 1, rowFIO + dic.Count - 8);
table.CreateRow(rowFIO);
table.CreateRow(rowFIO + 1);
}
ICellStyle style = table.Workbook.CreateCellStyle();
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
//выводим весь этот колохоз
int r = rowNumbers;
foreach (var element in dic)
{
IRow row = table.CreateRow(r);
row.CreateCell(0).CellStyle = style;
row.CreateCell(1).CellStyle = style;
row.CreateCell(2).CellStyle = style;
row.CreateCell(3).CellStyle = style;
row.CreateCell(4).CellStyle = style;
row.CreateCell(5).CellStyle = style;
row.CreateCell(6).CellStyle = style;
row.CreateCell(7).CellStyle = style;
row.CreateCell(8).CellStyle = style;
row.CreateCell(9).CellStyle = style;
row.CreateCell(10).CellStyle = style;
row.GetCell(3).SetValue(element.Key);
if (element.Value > 0)
{//2ая строка в группе
row.GetCell(2).SetValue("Временные свидетельства"); row.GetCell(2).CellStyle = style;
row.GetCell(5).SetValue("шт.");
row.GetCell(6).SetValue(element.Value.ToString());
row.GetCell(7).SetValue(element.Value.ToString());
row.GetCell(8).SetValue(price.ToString());
row.GetCell(9).SetValue((price * element.Value).ToString());
}
else
{
//1ая строка
}
r++;
}
}
catch
{
}
}
}
/// <summary>
/// Формирование отчета
/// </summary>
/// <param name="name">Имя соединения</param>
/// <param name="drv">Драйвер соединения</param>
/// <param name="qname">Имя запроса</param>
/// <param name="group">Группа запроса</param>
/// <param name="subgroup">Подгруппа запроса</param>
/// <param name="sql">SQL запроса</param>
/// <param name="repId">ID отчета</param>
/// <param name="person">Текущий пользователь</param>
/// <returns>Путь формированного файла-отчета</returns>
public string GenerateReport(string name, asv.Models.eDriverType drv, string sql, object [] args, string qname, string group, string subgroup, int repId, MemberPrincipal person)
{
this.person = person;
GenerateParams(qname, group, subgroup);
string cv = null,
tplPath = GetTemplate(repId),
ext = Path.GetExtension(tplPath),
path = Path.GetFileNameWithoutExtension(tplPath) + "_" + person.Id + ext,
newPath = repPath + repId + @"\" + path;
Type t = typeof(HSSFWorkbook);
if (ext == ".xlsx")
{
repFormat = eOfficeFormat.OfficeXml;
t = typeof(XSSFWorkbook);
}
FileStream fs = new FileStream(repPath + repId + @"\" + tplPath, FileMode.Open, FileAccess.Read);
IWorkbook wb = (IWorkbook)Activator.CreateInstance(t, new object[] { fs });
fs.Close();
bool isComplex = false;
ICell cell = null;
IRow row = null;
ISheet sheet = null;
ComplexRows compl = null;
IList <ComplexRows> complrows = new List <ComplexRows>();
Regex rx = new Regex(@"#Запрос,?\s?(\d+)*-?\s?(\d+)*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
Match m;
IEnumerator cells, rows, sheets = wb.GetEnumerator();
while (sheets.MoveNext())
{
sheet = (ISheet)sheets.Current;
rows = sheet.GetEnumerator();
while (rows.MoveNext())
{
row = (IRow)rows.Current;
cells = row.GetEnumerator();
while (cells.MoveNext())
{
cell = (ICell)cells.Current;
try
{
cv = cell.StringCellValue;
m = rx.Match(cv);
if (m.Success)
{
isComplex = true;
compl = new ComplexRows(sheet.SheetName);
compl.R1 = row.RowNum;
compl.P1 = Misc.GetConfigValue(m.Groups[1].Value, -1);
compl.P2 = Misc.GetConfigValue(m.Groups[2].Value, -1);
if (compl.P1 != -1)
{
compl.P1--;
}
if (compl.P2 != -1)
{
compl.P2--;
}
break;
}
else if (cv.StartsWith("#Конец_Запрос", StringComparison.CurrentCultureIgnoreCase))
{
compl.R2 = row.RowNum;
complrows.Add(compl);
isComplex = false;
break;
}
else if (!isComplex)
{
InsertParam(cell);
}
}
catch
{
}
}
}
}
#region запрос
DataManager dm = new DataManager();
dm.Person = person;
List <dynamic> complexParams = dm.GetQData(name, drv, sql, args, -1).ToList();
#endregion
dynamic data;
int i, len = complexParams.Count() - 1,
rn, total;
string sn = string.Empty;
foreach (ComplexRows cr in complrows)
{
if (!sn.Equals(cr.S))
{
sheet = wb.GetSheet(cr.S);
sn = cr.S;
}
total = cr.TotalRows;
if (cr.R2 == sheet.LastRowNum)
{
sheet.CreateRow(cr.R2 + 1);
}
// удалить ряд 'Запрос' --> смещение -1
sheet.RemoveRow(sheet.GetRow(cr.R1));
sheet.ShiftRows(cr.R1 + 1, sheet.LastRowNum, -1);
// удалить ряд 'Конец_Запрос' --> смещение -1
cr.R2--;
sheet.RemoveRow(sheet.GetRow(cr.R2));
sheet.ShiftRows(cr.R2 + 1, sheet.LastRowNum, -1);
if (cr.P2 == -1)
{
cr.P2 = len;
}
for (i = cr.P1; i < cr.P2 - 1; ++i)
{
data = complexParams[i];
for (rn = 0; rn < total; ++rn)
{
sheet.CopyRow(cr.R1, cr.R1 + total);
row = sheet.GetRow(cr.R1);
cells = row.GetEnumerator();
while (cells.MoveNext())
{
cell = (ICell)cells.Current;
InsertParam(cell, data);
}
cr.R1++;
}
}
// последний ряд
data = complexParams[i];
row = sheet.GetRow(cr.R1);
cells = row.GetEnumerator();
while (cells.MoveNext())
{
cell = (ICell)cells.Current;
InsertParam(cell, data);
}
}
switch (repFormat)
{
case eOfficeFormat.OfficeXls:
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
break;
case eOfficeFormat.OfficeXml:
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
break;
}
fs = new FileStream(repPath + repId + @"\" + path, FileMode.Create, FileAccess.Write);
wb.Write(fs);
fs.Close();
return(path);
}
protected override void Process(ISheet table)
{
int rowNumber = 16;
int rowNumberDown = 27;
string dateTime = string.Format("за отчетный период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString());
table.FindCellByMacros(dateTimeConst).SetValue(dateTime);
table.FindCellByMacros(deliveryPointConst).SetValue(DeliveryPoint);
var ListHistoryGroupByBSO_IDs = ListHistory.GroupBy(a => a.Id);
foreach (var item in ListHistoryGroupByBSO_IDs)
{
BSOHistoryItem temp = item.OrderBy(a => a.StatusDate).Where(b => b.Status.Id == (long)ListBSOStatusID.OnDelivery || b.Status.Id == (long)ListBSOStatusID.OnResponsible).LastOrDefault();
if (temp != null)
{
if (temp.StatusDate < DateTimeFrom)
{
BSOInfo bsoInfo = new BSOInfo()
{
TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
//TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
Status = new BSOStatusRef()
{
Id = temp.Status.Id
}
};
if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber))
{
ListBSOBeforeDate.Add(bsoInfo);
}
}
else
{
BSOInfo bsoInfo = new BSOInfo()
{
TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
//TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(),
Status = new BSOStatusRef()
{
Id = temp.Status.Id
}
};
if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber))
{
ListBSOGet.Add(bsoInfo);
}
}
}
}
Dictionary <string, long> dicBefore = SortingAndGroup(ListBSOBeforeDate, new List <long>()
{
(long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
});
Dictionary <string, long> dicRange = SortingAndGroup(ListBSOGet, new List <long>()
{
(long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
});
Dictionary <string, long> dicAll = SortingAndGroup(ListBSOAllDate, new List <long>()
{
(long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible
});
Dictionary <string, long> dicOnClient = SortingAndGroup(ListBSOinRangeDate, new List <long>()
{
(long)ListBSOStatusID.OnClient
});
Dictionary <string, long> dicFail = SortingAndGroup(ListBSOinRangeDate, new List <long>()
{
(long)ListBSOStatusID.FailOnStorage, (long)ListBSOStatusID.FailOnResponsible, (long)ListBSOStatusID.FailGotoStorage
});
//определяем в каком словаре максимальное кол-во элементов, для того чтоб насоздавать строки
List <int> countDictionary = new List <int>();
countDictionary.Add(dicBefore == null ? 0 : dicBefore.Count);
countDictionary.Add(dicRange == null ? 0 : dicRange.Count);
countDictionary.Add(dicAll == null ? 0 : dicAll.Count);
countDictionary.Add(dicOnClient == null ? 0 : dicOnClient.Count);
countDictionary.Add(dicFail == null ? 0 : dicFail.Count);
var max = countDictionary.Max() / 2;
//если много данных - двигаем последние строчки еще ниже
if (max > 10)
{
for (int i = 0; i < 10; i++)
{
table.CopyRow(i + rowNumberDown, i + rowNumberDown + max);
}
}
ICellStyle style = table.Workbook.CreateCellStyle();
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
List <IRow> rows = new List <IRow>();
for (int i = 0; i < countDictionary.Max() / 2; i++)
{
IRow row = table.CreateRow(i + rowNumber);
for (int j = 0; j <= 14; j++)
{
row.CreateCell(j).CellStyle = style;
}
rows.Add(row);
}
Output(dicBefore, rows, 0);
Output(dicRange, rows, 3);
Output(dicOnClient, rows, 6);
Output(dicFail, rows, 9);
Output(dicAll, rows, 12);
//Итого:
IRow rowSum = table.CreateRow(max + rowNumber);
rowSum.CreateCell(0).SetValue("Итого:");
rowSum.CreateCell(2).SetCellFormula(string.Format("SUM(C{0}:C{1})", rowNumber + 1, rowNumber + max));
rowSum.CreateCell(5).SetCellFormula(string.Format("SUM(F{0}:F{1})", rowNumber + 1, rowNumber + max));
rowSum.CreateCell(8).SetCellFormula(string.Format("SUM(I{0}:I{1})", rowNumber + 1, rowNumber + max));
rowSum.CreateCell(11).SetCellFormula(string.Format("SUM(L{0}:L{1})", rowNumber + 1, rowNumber + max));
rowSum.CreateCell(14).SetCellFormula(string.Format("SUM(O{0}:O{1})", rowNumber + 1, rowNumber + max));
table.FindCellByMacros(FIOConst).SetValue(FIO);
table.FindCellByMacros(PositionConst).SetValue(Position);
table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString());
}
/// <summary>
/// 供应商客服提取报减信息
/// </summary>
/// <param name="getParam"></param>
/// <returns></returns>
public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
{
FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报减数据提取模版.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
try
{
string search = getParam.search;
int getid = 0;
int total = 0;
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
{
string str = queryDic["CollectState"];
if (str.Contains(Common.CollectState.未提取.ToString()))
{
string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
search += "State&" + state + "^";
}
else
{
string state = Common.EmployeeAdd_State.已发送供应商.ToString();
search += "State&" + state + "^";
}
}
}
search += "UserID_Supplier&" + LoginInfo.UserID + "^";
List <SupplierAddView> queryData = m_BLL.GetEmployeeStopExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
if (queryData.Count == 0)
{
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = ""
};
return(data);
}
//string excelName = "供应商导出" + DateTime.Now.ToString();
using (MemoryStream ms = new MemoryStream())
{
string ids = string.Empty;
int[] intArray = new int[queryData.Count];
//员工社保一览
ISheet sheet = workbook.GetSheetAt(0);
sheet.SetActiveCell(0, 0);
int rowNum = 0;
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
string tmpCertificateNumber = "";
string tmpCity = "";
string tmpYearMonth = "";
string strRemark = "";
IRow currentRow = null;
#region 数据输出
int i = 0;
foreach (var query in queryData)
{
intArray[i] = query.EmployeeAddId ?? 0;
i++;
if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
(tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
{
//保存临时数据
tmpCertificateNumber = query.CertificateNumber;
tmpCity = query.City;
tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");
if (currentRow != null)
{
currentRow.GetCell(10).SetCellValue(strRemark); // 备注
strRemark = "";
}
rowNum++;
currentRow = sheet.CopyRow(rowNum + 1, rowNum);
currentRow.GetCell(0).SetCellValue(rowNum); // 序号
currentRow.GetCell(1).SetCellValue(query.CustomerName); // 客服姓名
currentRow.GetCell(2).SetCellValue(query.City); // 缴费地
currentRow.GetCell(3).SetCellValue(query.BranchName); // 分公司名称
currentRow.GetCell(4).SetCellValue(query.CompanyName); // 客户单位名称
currentRow.GetCell(5).SetCellValue(query.EmployeeName); // 员工姓名
currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
currentRow.GetCell(7).SetCellValue(query.Telephone); // 联系电话
if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
{
currentRow.GetCell(8).SetCellValue(tmpYearMonth); // 缴费开始时间
}
else
{
currentRow.GetCell(9).SetCellValue(tmpYearMonth); // 缴费开始时间
}
}
int cellNum = 0;
switch (Convert.ToInt32(query.InsuranceKindId))
{
case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
cellNum = 9;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.养老:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.失业:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.生育:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.大病:
cellNum = 8;
break;
default:
break;
}
if (query.State == "1")
{
currentRow.GetCell(cellNum).CellStyle = style;
}
if (!string.IsNullOrEmpty(query.Remark))
{
strRemark += query.Remark + Convert.ToChar(10);
}
ids += query.EmployeeAddId + ",";
}
#endregion
var results = 0;//返回的结果
if (queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()))
{
int[] ApprovedId;
string[] strArray = ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
ApprovedId = Array.ConvertAll <string, int>(strArray, s => int.Parse(s));
string returnValue = string.Empty;
if (ApprovedId != null && ApprovedId.Length > 0)
{
if (m_BLL.EmployeeStopPaymentApproved(ref validationErrors, ApprovedId, Common.EmployeeStopPayment_State.待供应商客服提取.ToString(), Common.EmployeeStopPayment_State.已发送供应商.ToString(), null, LoginInfo.LoginName))
{
results = ApprovedId.Count();
LogClassModels.WriteServiceLog("供应商客服已提取" + "，信息的Id为" + string.Join(",", ApprovedId), "消息"
);//回退成功，写入日志
}
else
{
if (validationErrors != null && validationErrors.Count > 0)
{
validationErrors.All(a =>
{
returnValue += a.ErrorMessage;
return(true);
});
}
LogClassModels.WriteServiceLog("供应商客服提取失败" + "，信息的Id为" + string.Join(",", ApprovedId) + "," + returnValue, "消息"
);//回退失败，写入日志
}
}
}
string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
string urlPath = "DataExport\\" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
string Message = queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()) ? "已成功提取报减信息" : "导出成功";
if (queryData.Count == 0)
{
//ActionResult result =
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = urlPath
};
return(data);
}
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = Message,
URL = urlPath
});
}
}
catch (Exception e)
{
file.Close();
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Fail,
Message = e.Message
});
}
}
public static MemoryStream ExportIntoFile <TViewModel>(Stream stream,
bool isXlsx,
string sheetName,
IList <TViewModel> viewModels,
IList <CustomField> customData = null,
int headerRowOffset = 3,
bool createRow = true,
bool copyRow = false,
bool useOriginalTypes = false)
{
var output = new MemoryStream();
var tmpl = stream;
stream.Seek(0, SeekOrigin.Begin);
IWorkbook workbook = null;
if (isXlsx)
{
workbook = new XSSFWorkbook(tmpl);
}
else
{
workbook = new HSSFWorkbook(tmpl);
}
ISheet sheet = null;
if (!String.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName); //"Template");
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (viewModels.Any())
{
var type = viewModels[0].GetType();
var properties = GetProperties <TViewModel>(type);
properties = properties.OrderBy(p => GetExcelAttribute(p).Order).ToList();
var propertyToAttribute =
properties.Select(
p => new Tuple <PropertyInfo, ExcelSerializableAttribute>(p, GetExcelAttribute(p))).ToList();
for (var i = 0; i < viewModels.Count; i++)
{
IRow row;
if (copyRow)
{
if (i > 1)
{
row = sheet.CopyRow(headerRowOffset + 1, headerRowOffset + i);
}
row = sheet.GetRow(i + headerRowOffset);
}
else
{
if (createRow)
{
row = sheet.CreateRow(i + headerRowOffset);
}
else
{
row = sheet.GetRow(i + headerRowOffset);
}
}
WriteViewModelToRow(viewModels[i], row, propertyToAttribute, createRow, useOriginalTypes);
}
if (customData != null)
{
foreach (var field in customData)
{
var row = sheet.GetRow(field.Row);
var cell = row.GetCell(field.Cell);
cell.SetCellValue(field.Value);
}
}
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
workbook.Write(output);
}
return(output);
}
// export via template
public static ISheet EntityListToSheetByTemplate <TEntity>(
[NotNull] ISheet sheet,
IEnumerable <TEntity> entityList,
object extraData = null)
{
if (null == entityList)
{
return(sheet);
}
var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>();
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary(configuration);
var globalDictionary = extraData.ParseParamInfo()
.ToDictionary(x => TemplateOptions.TemplateGlobalParamFormat.FormatWith(x.Key), x => x.Value);
foreach (var propertyConfiguration in propertyColumnDictionary)
{
globalDictionary.Add(TemplateOptions.TemplateHeaderParamFormat.FormatWith(propertyConfiguration.Key.Name), propertyConfiguration.Value.ColumnTitle);
}
var dataFuncDictionary = propertyColumnDictionary
.ToDictionary(x => TemplateOptions.TemplateDataParamFormat.FormatWith(x.Key.Name), x => x.Key.GetValueGetter <TEntity>());
foreach (var key in propertyColumnDictionary.Keys)
{
if (InternalCache.OutputFormatterFuncCache.TryGetValue(key, out var formatterFunc) && formatterFunc?.Method != null)
{
dataFuncDictionary[TemplateOptions.TemplateDataParamFormat.FormatWith(key.Name)] = entity =>
{
var val = key.GetValueGetter <TEntity>()?.Invoke(entity);
try
{
var formattedValue = formatterFunc.Invoke(new[] { entity, val });
return(formattedValue);
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
return(val);
};
}
}
// parseTemplate
int dataStartRow = -1, dataRowsCount = 0;
for (var rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
for (var cellIndex = row.FirstCellNum; cellIndex < row.LastCellNum; cellIndex++)
{
var cell = row.GetCell(cellIndex);
if (cell == null)
{
continue;
}
var cellValue = cell.GetCellValue <string>();
if (!string.IsNullOrEmpty(cellValue))
{
var beforeValue = cellValue;
if (dataStartRow <= 0 || dataRowsCount <= 0)
{
if (dataStartRow >= 0)
{
if (cellValue.Contains(TemplateOptions.TemplateDataEnd))
{
dataRowsCount = rowIndex - dataStartRow + 1;
cellValue = cellValue.Replace(TemplateOptions.TemplateDataEnd, string.Empty);
}
}
else
{
if (cellValue.Contains(TemplateOptions.TemplateDataBegin))
{
dataStartRow = rowIndex;
cellValue = cellValue.Replace(TemplateOptions.TemplateDataBegin, string.Empty);
}
}
}
foreach (var param in globalDictionary.Keys)
{
if (cellValue.Contains(param))
{
cellValue = cellValue
.Replace(param,
globalDictionary[param]?.ToString() ?? string.Empty);
}
}
if (beforeValue != cellValue)
{
cell.SetCellValue(cellValue);
}
}
}
}
if (dataStartRow >= 0 && dataRowsCount > 0)
{
foreach (var entity in entityList)
{
sheet.ShiftRows(dataStartRow, sheet.LastRowNum, dataRowsCount);
for (var i = 0; i < dataRowsCount; i++)
{
var row = sheet.CopyRow(dataStartRow + dataRowsCount + i, dataStartRow + i);
if (null != row)
{
for (var j = 0; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (null != cell)
{
var cellValue = cell.GetCellValue <string>();
if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains(TemplateOptions.TemplateDataPrefix))
{
var beforeValue = cellValue;
foreach (var param in dataFuncDictionary.Keys)
{
if (cellValue.Contains(param))
{
cellValue = cellValue.Replace(param,
dataFuncDictionary[param]?.Invoke(entity)?.ToString() ?? string.Empty);
}
}
if (beforeValue != cellValue)
{
cell.SetCellValue(cellValue);
}
}
}
}
}
}
//
dataStartRow += dataRowsCount;
}
// remove data template
for (var i = 0; i < dataRowsCount; i++)
{
var row = sheet.GetRow(dataStartRow + i);
if (null != row)
{
sheet.RemoveRow(row);
}
}
sheet.ShiftRows(dataStartRow + dataRowsCount, sheet.LastRowNum, -dataRowsCount);
}
return(sheet);
}
/// <summary>
/// 复制单元格(解决xssf处理excel2007复制行=null的bug）
/// </summary>
/// <param name="sheet">表单</param>
/// <param name="sourceIndex">原始行</param>
/// <param name="targetIndex">目标(起始)行</param>
/// <param name="count">复制次数</param>
/// <param name="dataIncluded">是否复制数据</param>
/// <returns>复制行数</returns>
public static int CopyRow(ISheet sheet, int sourceIndex, int targetIndex, int count = 1, bool dataIncluded = true)
{
if (count > 0)
{
IDictionary <int, CellRangeAddress> rowMergeDic = GetRangeBetweenRows(sheet, sourceIndex, targetIndex);
IRow styleRow = sheet.GetRow(sourceIndex);
//模板无效时直接退出
if (styleRow == null)
{
return(0);
}
//NPOI的Copy操作默认是复制数据的，如不需要复制数据，用字符串清空
if (!dataIncluded)
{
foreach (var exCell in styleRow.Cells)
{
exCell.SetCellValue("");
}
}
/***
* ISheet.CopyRow 只复制不插入（字体、边框、样式、数据、单元格合并，不包括数据有效性）
*/
if (sheet is HSSFSheet)
{
if (sheet.GetRow(targetIndex) == null)
{
sheet.CreateRow(targetIndex);//达到每次Copy都插入行的目标
}
for (int indexOffset = 0; indexOffset < count; indexOffset++)
{
//已经存在的行，自动插入新行；不存在的行，直接Copy行
sheet.CopyRow(sourceIndex, targetIndex);
//XSSF的此操作会导致targetIndex变成空行
}
}
else if (sheet is XSSFSheet)
{
if (targetIndex <= sheet.LastRowNum)
{
sheet.ShiftRows(targetIndex, sheet.LastRowNum, count, true, false);//目的：让目标行始终是空的
}
for (int indexOffset = 0; indexOffset < count; indexOffset++)
{
//目标行：不存在时，直接Copy；存在时，有bug！执行后目标行反而变成了空行
sheet.CopyRow(sourceIndex, targetIndex + indexOffset);
}
}
CopyFormula(styleRow, targetIndex, count);
CellRangeAddress tmpRange = null;
foreach (var keyValue in rowMergeDic)
{
tmpRange = keyValue.Value;
tmpRange.LastRow += count;
sheet.RemoveMergedRegion(keyValue.Key);
sheet.AddMergedRegion(tmpRange);
}
return(count);
}
return(0);
}
public void WriteSchemaInfo(ISheet formatSheet, ISheet sheet, EPoint start, SchemaInfoTuple schemaInfo)
{
//复制表头
formatSheet.CopyRow(ExcelTemplateFormat.TSTHeadRowNum, sheet, start.X, start.Y);
//写入 表名（说明） 信息
String name = schemaInfo.ObjectSchema.Name;
if (!String.IsNullOrEmpty(schemaInfo.ObjectSchema.Explain))
{
name += $"({schemaInfo.ObjectSchema.Explain})";
}
sheet.SetCellValue(start.X, 0, name);
start.X += ExcelTemplateFormat.RowSpan;
//复制列头
formatSheet.CopyRow(ExcelTemplateFormat.TSTColumnHeaderRowNum, sheet, start.X, start.Y);
start.X += ExcelTemplateFormat.RowSpan;
//开始写入列信息
Int32 num = 1;
if (schemaInfo.ColumnSchemas == null || schemaInfo.ColumnSchemas.Count == 0)
{
return;
}
foreach (var colSchema in schemaInfo.ColumnSchemas)
{
//复制行
formatSheet.CopyRow(ExcelTemplateFormat.TSTRowTemlateNum, sheet, start.X, start.Y);
Int32 col = 0;
//写入序号
sheet.SetCellValue(start.X, col, num++);
col += ExcelTemplateFormat.TableNumColLength;
//写入列名
sheet.SetCellValue(start.X, col, colSchema.Name);
col += ExcelTemplateFormat.TableFieldNameColLength;
//写入注释
sheet.SetCellValue(start.X, col, colSchema.Explain);
col += ExcelTemplateFormat.TableExplainColLength;
//写入类型
sheet.SetCellValue(start.X, col, colSchema.DbTypeString);
col += ExcelTemplateFormat.TableTypeColLength;
//写入长度
if (colSchema.Length.HasValue)
{
sheet.SetCellValue(start.X, col, colSchema.Length.Value);
}
col += ExcelTemplateFormat.TableLengthColLength;
//写入主键信息
if (colSchema.IsPrimaryKey)
{
sheet.SetCellValue(start.X, col, colSchema.IsPrimaryKey);
}
col += ExcelTemplateFormat.TableIsPrimaryColLength;
//写入可空信息
if (!colSchema.IsNullable)
{
sheet.SetCellValue(start.X, col, colSchema.IsNullable);
}
col += ExcelTemplateFormat.TableIsNullableColLength;
start.X += ExcelTemplateFormat.RowSpan;
}
}
public void FillChartData(int chartIndex, DataTable dt, int categoryTextLength)
{
var chart = document.Charts[chartIndex];
var series = chart.Series;
MemoryStream stream = new MemoryStream();
stream.Write(chart.ExcelFileBytes, 0, chart.ExcelFileBytes.Length);
stream.Position = 0;
IWorkbook book = new XSSFWorkbook(stream);
ISheet sheet = book.GetSheetAt(0);
int lastRowNum = sheet.LastRowNum;
int lastColNum = sheet.GetRow(0).LastCellNum;
Dictionary <string, List <string> > seriesData = new Dictionary <string, List <string> >();
for (int j = 0; j < dt.Columns.Count; j++)
{
seriesData.Add(j.ToString(), new List <string>());
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
IRow row = sheet.GetRow(i + 1);
if (row == null)
{
row = sheet.CopyRow(1, i + 1);
}
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].DataType.FullName)
{
case "System.Double":
row.Cells[j].SetCellValue(Convert.ToDouble(dr[j]));
break;
default:
row.Cells[j].SetCellValue(dr[j].ToString());
break;
}
var list = seriesData[j.ToString()];
if (j == 0 && categoryTextLength > 0)
{
if (dr[j].ToString().Length > categoryTextLength)
{
list.Add(dr[j].ToString().Substring(0, categoryTextLength) + "..");
}
else
{
list.Add(dr[j].ToString());
}
}
else
{
list.Add(dr[j].ToString());
}
}
}
int seriesIndex = 1;
foreach (var item in series)
{
item.Bind(seriesData["0"], seriesData[seriesIndex.ToString()]);
seriesIndex++;
}
stream.Close();
stream.Dispose();
//转为字节数组
stream = new MemoryStream();
book.Write(stream);
chart.ExcelFileBytes = stream.ToArray();
stream.Close();
stream.Dispose();
}
private void Store(ISheet sheet, IPersistEntity entity, ClassMapping mapping, ExpressType expType, EntityContext context)
{
var multiRow = -1;
List <string> multiValues = null;
PropertyMapping multiMapping = null;
var row = GetRow(sheet);
//fix on "Special Case" Assembly Row to Entity mapping
if ((context?.RootEntity != null) && (expType?.ExpressNameUpper == "TYPEORCOMPONENT")) //without CobieExpress reference and not using reflection this is as good as it gets to ID Assembly
{
RowNoToEntityLabelLookup[sheet.SheetName].Add(row.RowNum, context.RootEntity.EntityLabel);
}
else
{
RowNoToEntityLabelLookup[sheet.SheetName].Add(row.RowNum, entity.EntityLabel);
}
foreach (var propertyMapping in mapping.PropertyMappings)
{
object value = null;
foreach (var path in propertyMapping.Paths)
{
value = GetValue(entity, expType, path, context);
if (value != null)
{
break;
}
}
if (value == null && propertyMapping.Status == DataStatus.Required)
{
value = propertyMapping.DefaultValue ?? "n/a";
}
var isMultiRow = IsMultiRow(value, propertyMapping);
if (isMultiRow)
{
multiRow = row.RowNum;
var values = new List <string>();
var enumerable = value as IEnumerable <string>;
if (enumerable != null)
{
values.AddRange(enumerable);
}
//get only first value and store it
var first = values.First();
Store(row, first, propertyMapping);
//set the rest for the processing as multiValue
values.Remove(first);
multiValues = values;
multiMapping = propertyMapping;
}
else
{
Store(row, value, propertyMapping);
}
}
//adjust width of the columns after the first and the eight row
//adjusting fully populated workbook takes ages. This should be almost all right
if (row.RowNum == 1 || row.RowNum == 8)
{
AdjustAllColumns(sheet, mapping);
}
//it is not a multi row so return
if (multiRow <= 0 || multiValues == null || !multiValues.Any())
{
return;
}
//add repeated rows if necessary
foreach (var value in multiValues)
{
var rowNum = GetNextRowNum(sheet);
var copy = sheet.CopyRow(multiRow, rowNum);
Store(copy, value, multiMapping);
RowNoToEntityLabelLookup[sheet.SheetName].Add(rowNum, entity.EntityLabel);
}
}
public static bool WriteExcel(string path, string templatePath, DataTable dt, int startRow, int startCol)
{
string pLocalFilePath = templatePath; //要复制的文件路径
string pSaveFilePath = path; //指定存储的路径
if (File.Exists(pLocalFilePath) && !path.Equals(templatePath)) //必须判断要复制的文件是否存在
{
File.Copy(pLocalFilePath, pSaveFilePath, true); //三个参数分别是源文件路径，存储路径，若存储路径有相同文件是否替换
}
IWorkbook workbook = null;
try
{
RemoveEmpty(ref dt);
workbook = WorkbookFactory.Create(pSaveFilePath);
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(startRow);
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
int end = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
row.Cells[j + 1].SetCellValue(dt.Columns[j].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
if (sheet.GetRow(i + startRow + 1) == null)
{
sheet.CopyRow(startRow, i + startRow + 1);
}
row = sheet.GetRow(i + startRow + 1);
DataRow dr = dt.Rows[i];
ICell cell = row.GetCell(0);
//cell.CellStyle = style;
end = i + startRow + 1;
if (cell == null)
{
cell = row.CreateCell(0);
}
cell.SetCellValue((i + 1).ToString());
for (int j = 0; j < dt.Columns.Count; j++)
{
if (row.GetCell(j + startCol) == null)
{
cell = row.CreateCell(j + startCol);
cell.CellStyle = style;
}
else
{
cell = row.GetCell(j + startCol);
}
cell.SetCellValue(dr[dt.Columns[j]].ToString());
}
}
//导出excel
FileStream fs = new FileStream(pSaveFilePath, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Close();
}
catch (Exception e) {
if (workbook != null)
{
workbook.Close();
}
MessageBox.Show(e.Message);
return(false);
}
if (workbook != null)
{
workbook.Close();
}
return(true);
}
/// <summary>
/// 供应商客服提取报增信息
/// </summary>
/// <param name="getParam"></param>
/// <returns></returns>
public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
{
FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报增数据提取模板.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//ICellStyle cellStyle = workbook.CreateCellStyle();
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
try
{
string search = getParam.search;
int total = 0;
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
{
string str = queryDic["CollectState"];
if (str.Equals(Common.CollectState.未提取.ToString()))
{
string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
search += "State&" + state + "^";
}
else if (str.Equals(Common.CollectState.已提取.ToString()))
{
string state = Common.EmployeeAdd_State.已发送供应商.ToString();
search += "State&" + state + "^";
}
}
}
search += "UserID_Supplier&" + LoginInfo.UserID + "^";
//search += "BranchID&" + LoginInfo.BranchID + "^";
//search += "BranchName&" + LoginInfo.BranchName + "^";
List <SupplierAddView> queryData = m_BLL.GetEmployeeAddExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
if (queryData.Count == 0)
{
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = ""
};
return(data);
}
using (MemoryStream ms = new MemoryStream())
{
#region 生成Excel
string ids = string.Empty;
int[] intArray = new int[queryData.Count];
//员工社保一览
ISheet sheet = workbook.GetSheetAt(0);
sheet.SetActiveCell(0, 0);
int rowNum = 1;
string tmpCertificateNumber = "";
string tmpCity = "";
string tmpYearMonth = "";
string strRemark = "";
IRow currentRow = null;
#region 数据输出
int i = 0;
foreach (var query in queryData)
{
intArray[i] = query.EmployeeAddId ?? 0;
i++;
if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
(tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
{
//保存临时数据
tmpCertificateNumber = query.CertificateNumber;
tmpCity = query.City;
tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");
if (currentRow != null)
{
currentRow.GetCell(19).SetCellValue(strRemark); // 备注
strRemark = "";
}
rowNum++;
currentRow = sheet.CopyRow(rowNum + 1, rowNum);
currentRow.GetCell(0).SetCellValue(rowNum - 1); // 序号
currentRow.GetCell(1).SetCellValue(query.CustomerName); // 客服姓名
currentRow.GetCell(2).SetCellValue(query.City); // 缴费地
currentRow.GetCell(3).SetCellValue(query.BranchName); // 分公司名称
currentRow.GetCell(4).SetCellValue(query.CompanyName); // 客户单位名称
currentRow.GetCell(5).SetCellValue(query.EmployeeName); // 员工姓名
currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
currentRow.GetCell(7).SetCellValue(query.Telephone); // 联系电话
if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
{
currentRow.GetCell(8).SetCellValue(tmpYearMonth); // 缴费开始时间
}
}
int cellNum = 0;
switch (Convert.ToInt32(query.InsuranceKindId))
{
case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
currentRow.GetCell(15).SetCellValue(Convert.ToDateTime(query.OperationTime).ToString("yyyyMM")); // 公积金缴费开始时间
currentRow.GetCell(17).SetCellValue((query.EmployeePercent * 100).ToString() + "%"); // 公积金比例（个人）
currentRow.GetCell(18).SetCellValue((query.CompanyPercent * 100).ToString() + "%"); // 公积金比例（单位）
cellNum = 16;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.养老:
cellNum = 9;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
cellNum = 10;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.失业:
cellNum = 11;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
cellNum = 12;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.生育:
cellNum = 13;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.大病:
cellNum = 14;
break;
default:
break;
}
currentRow.GetCell(cellNum).SetCellValue(query.CompanyNumber.ToString()); // 基数
if (query.State == "1")
{
currentRow.GetCell(cellNum).CellStyle = style;
}
if (!string.IsNullOrEmpty(query.SupplierRemark))
{
strRemark += query.SupplierRemark + " ";
}
}
if (!string.IsNullOrEmpty(strRemark))
{
currentRow.GetCell(19).SetCellValue(strRemark); // 备注
}
#endregion
#endregion
bool rst = m_BLL.ChangeStatus(intArray, Common.EmployeeAdd_State.待供应商客服提取.ToString(), EmployeeAdd_State.已发送供应商.ToString(), LoginInfo.LoginName);
if (rst)
{
string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
//string fileName = name + "供应商导出.xls";
string urlPath = "DataExport\\" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
string Message = "已成功提取报增信息";
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = "已成功提取报增信息",
URL = urlPath
});
}
else
{
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = "报增信息提取失败",
URL = ""
});
}
}
}
//catch (Exception e)
//{
// file.Close();
// return new Common.ClientResult.UrlResult
// {
// Code = ClientCode.Fail,
// Message = e.Message
// };
//}
catch (DbEntityValidationException ex)
{
string errmsg = "";
foreach (var errors in ex.EntityValidationErrors)
{
foreach (var item in errors.ValidationErrors)
{
errmsg += item.ErrorMessage + item.PropertyName;
}
}
return(null);
}
}
public override void SetValue(ISheet sheet, JToken token)
{
JArray data;
if (this.Each == "$root")
{
data = (JArray)token;
}
else
{
data = (JArray)token[Each];
}
var row = sheet.GetRow(RowIndex);
var grouprows = ExcelGroupRow.GetGroupRows(data, TreeCode);
for (int i = 0; i < data.Count; i++)
{
var item = data[i];
foreach (var cellTemplate in Cells)
{
var cell = row.GetCell(cellTemplate.ColIndex);
var val = item[cellTemplate.Bind];
cellTemplate.SetCellValue(cell, val);
}
if (i != data.Count - 1) {
row = sheet.CopyRow(row.RowNum, row.RowNum + 1);
}
}
var groups = grouprows.Values.OrderByDescending(o => o.TreeCode).ToList();
var b = JsonConvert.SerializeObject(groups);
foreach (var grouprow in groups)
{
sheet.GroupRow(grouprow.StartIndex + RowIndex+1, grouprow.EndIndex + RowIndex);
}
if (data.Count == 0) {
sheet.ShiftRows(row.RowNum+1, sheet.LastRowNum, -1);
}
}
