跟你提供一个解决方案吧,用法就参照btnTemplate_Click事件中的说明即可;
- private void btnTemplate_Click(object sender, RoutedEventArgs e)
- {
- OpenFileDialog ofd = new OpenFileDialog();
- if (ofd.ShowDialog().Value)
- {
- C1XLBook book = new C1XLBook();
- book.Load(ofd.FileName, FileFormat.OpenXml, true);
- XLSheet sheet = book.Sheets[0];
- // 取template范围作为模版
- XLCellRange template = new XLCellRange(0, 3, 0, 6);
- // 应用到(5,0)起始位置
- ApplyTemplate(sheet, template, 5, 0);
- book.Save(ofd.FileName, FileFormat.OpenXml);
- }
- }
复制代码- /// <summary>
- /// 获取工作簿中某块儿区域内的所有合并单元格
- /// </summary>
- /// <param name="sheet">当前工作簿</param>
- /// <param name="rng">指定区域</param>
- private List<XLCellRange> GetMergedCellRanges(XLSheet sheet, XLCellRange rng)
- {
- List<XLCellRange> mergedCellRanges = new List<XLCellRange>();
- foreach (XLCellRange cellRange in sheet.MergedCells)
- {
- if (rng.Contains(cellRange))// 包含
- {
- mergedCellRanges.Add(cellRange);
- }
- else if (rng.Intersects(rng))// 相交,取交集部分作为合并区域
- {
- int colFrom = rng.ColumnFrom <= cellRange.ColumnFrom ? cellRange.ColumnFrom : rng.ColumnFrom;
- int colTo = rng.ColumnTo <= cellRange.ColumnTo ? rng.ColumnTo : cellRange.ColumnTo;
- int rowFrom = rng.RowFrom <= cellRange.RowFrom ? cellRange.RowFrom : rng.RowFrom;
- int rowTo = rng.RowTo <= cellRange.RowTo ? rng.RowTo : cellRange.RowTo;
- mergedCellRanges.Add(new XLCellRange(rowFrom, rowTo, colFrom, colTo));
- }
- }
- return mergedCellRanges;
- }
复制代码- /// <summary>
- /// 根据模版templateRng,应用到指定位置(targetRow, targetCol)
- /// </summary>
- private void ApplyTemplate(XLSheet sheet, XLCellRange templateRng, int targetRow, int targetCol)
- {
- // 获取模版区域的合并单元格
- List<XLCellRange> cellRngs = GetMergedCellRanges(sheet, templateRng);
- List<XLCellRange> newMergedRngs = new List<XLCellRange>();
- foreach (XLCellRange mergedRng in cellRngs)
- {
- int rowFrom = (mergedRng.RowFrom - templateRng.RowFrom) + targetRow;
- int rowTo = rowFrom + mergedRng.RowCount - 1;
- int colFrom = (mergedRng.ColumnFrom - templateRng.ColumnFrom) + targetCol;
- int colTo = colFrom + mergedRng.ColumnCount - 1;
- newMergedRngs.Add(new XLCellRange(rowFrom, rowTo, colFrom, colTo));
- }
- foreach (XLCellRange rng in newMergedRngs)
- {
- sheet.MergedCells.Add(rng);
- }
- // 应用样式和内容
- for (int i = templateRng.ColumnFrom; i <= templateRng.ColumnTo; i++)
- {
- for (int j = templateRng.RowFrom; j <= templateRng.RowTo; j++)
- {
- int rowIndex = j - templateRng.RowFrom + targetRow;
- int colIndex = i - templateRng.ColumnFrom + targetCol;
- sheet[rowIndex, colIndex].Style = sheet[j, i].Style.Clone();
- sheet[rowIndex, colIndex].Value = sheet[j, i].Value;
- }
- }
- }
复制代码 |