/// <summary>
/// 插入数据行
/// </summary>
private void InsertRow1(String shopName, String dateStr,DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int sheetCount = 1;
ISheet sheet = excelWorkbook.GetSheetAt(0);//表示在模板的第一个工作簿里写入
// IRow headerRow = sheet.CreateRow(0);
IRow shopRow = sheet.GetRow(1);
//遍历模板行
IRow tableHeaderRow = sheet.GetRow(2);
//样式行
IRow tableStyleRow = sheet.GetRow(3);
shopRow.Cells[2].SetCellValue(shopName);
shopRow.Cells[5].SetCellValue(dateStr);
int rowCount = sheet.PhysicalNumberOfRows-1;
//CreateHeader(newsheet);
int i = 0;
foreach (DataRow dr in dtSource.Rows)
{
IRow newRow = null;
if (i == 0) {
newRow = tableStyleRow;
i++;
} else {
// 第一次使用tableStyleRow
rowCount++;
//超出10000条数据 创建新的工作簿
if (rowCount == 10000)
{
rowCount = 1;
sheetCount++;
sheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
IRow newHeaderRow = sheet.CreateRow(0);
CopyRow(tableHeaderRow, newHeaderRow);
// CreateHeader(newsheet);
}
// HSSFRow newRow //= (HSSFRow)sheet.CreateRow(rowCount);
newRow = tableStyleRow.CopyRowTo(rowCount);
// CopyRow(tableStyleRow, newRow);
}
InsertCell2(dtSource, dr, newRow, sheet, excelWorkbook);
}
rowCount++;
if (rowCount != 10000)
{
IRow newRow = tableStyleRow.CopyRowTo(rowCount);
newRow.Cells[0].SetCellValue(String.Empty);
newRow.Cells[1].SetCellValue(String.Empty);
newRow.Cells[2].SetCellValue(String.Empty);
newRow.Cells[3].SetCellValue(String.Empty);
newRow.Cells[4].SetCellFormula("sum(E4:E"+ (rowCount)+ ")");
newRow.Cells[5].SetCellFormula("sum(F4:F" + (rowCount) + ")");
}
}
public static void TestCopyArea(string TemplatePath, string newFilePath)
{
using (NPOIExcelHelper source = new NPOIExcelHelper(TemplatePath))
{
source.CurrentSheetName = "请款单";
if (source.CurrentSheet != null)
{
for (int j = 1; j <= 50; j++)
{
int startRow = 15 * j;
var sheet = source.CurrentSheet;
for (int i = 0; i < 13; i++)
{
IRow row = sheet.GetRow(i);
IRow newRow = row.CopyRowTo(i + startRow);
newRow.Height = row.Height;
}
CellRangeAddress cra1 = new CellRangeAddress(0 + startRow, 0 + startRow, 0, 13);
source.CurrentSheet.AddMergedRegion(cra1);
CellRangeAddress cra2 = new CellRangeAddress(1 + startRow, 1 + startRow, 0, 13);
source.CurrentSheet.AddMergedRegion(cra2);
CellRangeAddress cra3 = new CellRangeAddress(2 + startRow, 2 + startRow, 0, 13);
source.CurrentSheet.AddMergedRegion(cra3);
CellRangeAddress cra4 = new CellRangeAddress(3 + startRow, 4 + startRow, 0, 1);
source.CurrentSheet.AddMergedRegion(cra4);
//金 额（人民币）
CellRangeAddress cra5 = new CellRangeAddress(3 + startRow, 3 + startRow, 3, 13);
source.CurrentSheet.AddMergedRegion(cra5);
CellRangeAddress cra6 = new CellRangeAddress(5 + startRow, 8 + startRow, 2, 2);
source.CurrentSheet.AddMergedRegion(cra6);
CellRangeAddress cra7 = new CellRangeAddress(9 + startRow, 9 + startRow, 0, 1);
source.CurrentSheet.AddMergedRegion(cra7);
//收款单位名称：北京京东世纪信息技术有限公司
CellRangeAddress cra8 = new CellRangeAddress(10 + startRow, 10 + startRow, 0, 1);
source.CurrentSheet.AddMergedRegion(cra8);
//"开户行：招商银行北京青年路支行 账号：xxxxxxxx"
CellRangeAddress cra9 = new CellRangeAddress(10 + startRow, 10 + startRow, 2, 13);
source.CurrentSheet.AddMergedRegion(cra9);
//"请于 年 月 日前完成付款。 备注说明：（若无法在申请付款时间内付款，请提前告知原因及可付款日期。）"
CellRangeAddress cra10 = new CellRangeAddress(11 + startRow, 11 + startRow, 0, 13);
source.CurrentSheet.AddMergedRegion(cra10);
//公司领导： 财务经理： 会计： 部门负责人： 经办人：
CellRangeAddress cra11 = new CellRangeAddress(12 + startRow, 12 + startRow, 0, 13);
source.CurrentSheet.AddMergedRegion(cra11);
source.SaveAs(newFilePath);
}
}
}
}
/// <summary>Заполнение текста из базы</summary>
/// <param name="ListName">имя листа</param>
protected void LoadTextTemplate(ISheet sheet, string ListName, int count = 0)
{
CellExchange_Class ex = new CellExchange_Class(sheet);
foreach (var one in TemplateStorage.textFromTP.Where(x => x.ListName == ListName))
{
var texts = one.Text.Split('\n').ToList();
ICellStyle defStyle = null;
IRow Defrow = SearchRowFromMark(sheet, one.ThisTemplate);
if (texts.FirstOrDefault(x => x.Contains(StyleTarget.DefStyle)) != null)
{
defStyle = LoadStyle(texts.First(x => x.Contains(StyleTarget.DefStyle)));
texts.Remove(texts.First(x => x.Contains(StyleTarget.DefStyle)));
}
if (texts.Count > 1)
{
foreach (var text in texts)
{
Defrow = Defrow.CopyRowTo(Defrow.RowNum + 1);
IRow row = sheet.GetRow(Defrow.RowNum - 1);
string stroka = text;
if (text.Contains(StyleTarget.Style))
{
ICellStyle style = LoadStyle(text.Substring(text.IndexOf(StyleTarget.Style)));
foreach (var cell in row.Cells)
{
cell.CellStyle = style;
}
stroka = text.Substring(0, text.IndexOf(StyleTarget.Style));
}
else if (defStyle != null)
{
foreach (var cell in row.Cells)
{
cell.CellStyle = defStyle;
}
}
stroka = stroka.StringDivision(80);
ex.AddExchange(one.ThisTemplate, (e) => e.Row.Height = (short)(300 / 11 * 12 * stroka.Split('\n').Length), 1);
ex.AddExchange(one.ThisTemplate, stroka, 1);
}
}
else
{
ex.AddExchange(one.ThisTemplate, one.Text, count);
}
ex.AddExchange(one.ThisTemplate, (e) => { sheet.RemoveRow(e.Row); }, 1);
}
ex.Exchange();
}
/// <summary>Заполнения оглавления</summary>
private void ClientLoad()
{
Substitute.AddExchange(mark.client, $"{obj.Client.Detail.FullName}\n{obj.Adres}", 0);
MonthYearSelect();
var workers = AdditionnTable.GetWorkers(data.ETypeTemplate.CalculationFees, true, "Расчёт");
IRow row = SearchRowFromMark(sheet, mark.job);
foreach (var one in workers)
{
row.CopyRowTo(row.RowNum + 1);
Substitute.AddExchange(mark.job, one.Post, 1);
Substitute.AddExchange(mark.fio, one.FIO, 1);
}
}
/// <summary>
/// 创建明细行模板
/// </summary>
/// <param name="sheetTemplate">模板工作簿</param>
/// <param name="lineStartIndex">开始行号</param>
/// <param name="lineCount">总行数</param>
private void CreateLineTemplate(ISheet sheetTemplate, int lineStartIndex, int lineCount)
{
//if (lineStartIndex > sheetTemplate.LastRowNum)
//{
// return;
//}
//复制lineCount-1行明细行
IRow templateRow = sheetTemplate.GetRow(lineStartIndex - 1);//模板行
IRow targetRow = null;
for (int i = lineStartIndex; i < lineStartIndex + lineCount - 1; i++)
{
targetRow = templateRow.CopyRowTo(i);
targetRow.Height = templateRow.Height;
}
}
private void LoadTable()
{
sheet = book.GetSheet("Таблица");
Substitute = new CellExchange_Class(sheet);
IRow row = SearchRowFromMark(sheet, StaticMark.abonent, false);
var selectionWells = Helpers.LogicHelper.SelectionWellLogic.Find(DateControl_Class.SelectMonth, 1);
foreach (var selectionWell in selectionWells)
{
sheet.ShiftRows(row.RowNum + 3, sheet.LastRowNum, 0);
row.CopyRowTo(row.RowNum + 3);
sheet.GetRow(row.RowNum + 1).CopyRowTo(row.RowNum + 4);
sheet.GetRow(row.RowNum + 2).CopyRowTo(row.RowNum + 5);
}
foreach (var selectionWell in selectionWells.OrderBy(x => x.Number))
{
Client client = selectionWell.Objecte.Client;
ClientName(client, false);
WellNumber(selectionWell);
Substitute.AddExchange(mark.number_sample,
$"{selectionWell.Number}-С-{MyTools.YearMonth_From_YM(MyTools.YM_From_YMDHM(selectionWell.YMDHM), MyTools.EDateTimeTypes.DivisionSymbol, DivisionSymbol: "/")}",
1);
row = SearchRowFromMark(sheet, "{таблица}");
tableSelection = new TableSelection(selectionWell.Objecte, selectionWell.Sample);
tableSelection.CreateTable(SearchCellFromMark(sheet, "{таблица}", false), selectionWell);
}
tableSelection.Signature(SearchCellFromMark(sheet, "{пример}", false), Substitute);
ResizeWidth(sheet, MyTools.ETypeFormatBook.Horizontal, tableSelection.table);
MonthYearSelect();
Worker worker = AdditionnTable.GetSigner(data.ETypeTemplate.Journal, "Таблица");
Substitute.AddExchange(mark.fio, worker.FIO, 1);
Substitute.AddExchange(mark.job, worker.Post, 1);
Substitute.Exchange();
}
public IRow CopyRowTo(int targetIndex)
{
return(_row.CopyRowTo(targetIndex));
}
