fz
public class Do { XSSFWorkbook hssfworkbook; XSSFWorkbook templateLeft; XSSFWorkbook templateRight; List<string[]> data = new List<string[]>(); string filePath; public Do(string filePath) { this.filePath = filePath; this.ReadTemplate(); } public bool Exchange() { bool flag = true; try { this.ReadExcel(); var sheetLeft = templateLeft.GetSheetAt(0); var sheetRight = templateRight.GetSheetAt(0); ICell cell; foreach (var arr in data) { if (arr == null) continue; #region left cell = this.SearchSell(sheetLeft, 1, 2); cell.SetCellValue(arr[1]); cell = this.SearchSell(sheetLeft, 2, 6); cell.SetCellValue(arr[24]); cell = this.SearchSell(sheetLeft, 5, 1); cell.SetCellValue(arr[3]); cell = this.SearchSell(sheetLeft, 5, 2); cell.SetCellValue(arr[4]); cell = this.SearchSell(sheetLeft, 5, 3); cell.SetCellValue(arr[5]); cell = this.SearchSell(sheetLeft, 5, 4); cell.SetCellValue(arr[6]); cell = this.SearchSell(sheetLeft, 5, 5); cell.SetCellValue(arr[2]); cell = this.SearchSell(sheetLeft, 5, 6); cell.SetCellValue(arr[12]); #endregion #region right cell = this.SearchSell(sheetRight, 1, 2); cell.SetCellValue(arr[1]); cell = this.SearchSell(sheetRight, 2, 6); cell.SetCellValue(arr[24]); cell = this.SearchSell(sheetRight, 5, 1); cell.SetCellValue(arr[8]); cell = this.SearchSell(sheetRight, 5, 2); cell.SetCellValue(arr[9]); cell = this.SearchSell(sheetRight, 5, 3); cell.SetCellValue(arr[10]); cell = this.SearchSell(sheetRight, 5, 4); cell.SetCellValue(arr[11]); cell = this.SearchSell(sheetRight, 5, 5); cell.SetCellValue(arr[7]); cell = this.SearchSell(sheetRight, 5, 6); cell.SetCellValue(arr[18]); #endregion this.WirteToFiles(arr[1]); } } catch (Exception ex) { flag = false; } return flag; } private void ReadExcel() { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); if (hssfworkbook != null) { ISheet sheet = hssfworkbook.GetSheetAt(0); for (int i = 3; i < sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); string[] dataRows = new string[25]; for (int j = 0; j < row.LastCellNum; j++) { ICell cell = row.GetCell(j); dataRows[j] = string.IsNullOrWhiteSpace(cell.ToString()) ? string.Empty : cell.NumericCellValue.ToString(); } data.Add(dataRows); } } } } private void ReadTemplate() { string filePathLeft = @".\template\left.xlsx"; string filePathRight = @".\template\left.xlsx"; using (FileStream file = new FileStream(filePathLeft, FileMode.Open, FileAccess.Read)) { templateLeft = new XSSFWorkbook(file); } using (FileStream file = new FileStream(filePathRight, FileMode.Open, FileAccess.Read)) { templateRight = new XSSFWorkbook(file); } } private ICell SearchSell(ISheet sheet, int rowNumber, int columnNumber) { var row = sheet.GetRow(rowNumber); return row.GetCell(columnNumber); } private void WirteToFiles(string fileName) { using (FileStream fileLeft = new FileStream(string.Format(@"export\{0}左.xlsx", fileName), FileMode.Create)) { templateLeft.Write(fileLeft); } using (FileStream fileRight = new FileStream(string.Format(@"export\{0}右.xlsx", fileName), FileMode.Create)) { templateRight.Write(fileRight); } } }