public static void SaveToFile(NPOI.SS.UserModel.IWorkbook workbook, string fileName) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName); System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite); workbook.Write(fileStream); fileStream.Close(); workbook.Close(); }
public static bool SaveExcel(NPOI.SS.UserModel.IWorkbook WorkBook, string FileName, bool OpenAfterSave, bool ShowErrorMessage = true) { try { using (var fs = new System.IO.FileStream(FileName, FileMode.Create, System.IO.FileAccess.Write)) { WorkBook.Write(fs); } } catch (Exception ex) { if (ShowErrorMessage) { MessageBox.Show("При попытке сохранения файла печатной формы возникла ошибка:\n" + ex.ToString(), "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } if (OpenAfterSave) { System.Diagnostics.Process.Start(FileName); } return(true); }
private void button6_Click(object sender, EventArgs e) { List <FishEntity.CallRecordsEntity> list = new List <FishEntity.CallRecordsEntity>(); for (int i = 0; i < 10; i++) { FishEntity.CallRecordsEntity entity = new FishEntity.CallRecordsEntity(); entity.code = "00000" + i; entity.customer = "杭州" + i; entity.linkman = "金" + i; entity.mobile = "13757193476"; list.Add(entity); } string startDate = "2015-03-01"; string endDate = "2015-03-25"; string templatepath = Application.StartupPath + "\\template\\callrecord.xls"; FileStream fs = new FileStream(templatepath, FileMode.Open, FileAccess.Read); NPOI.SS.UserModel.IWorkbook workbook = null; workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); int firstRow = sheet.FirstRowNum; int lastRow = sheet.LastRowNum; System.Collections.Hashtable hs = new System.Collections.Hashtable(); hs.Add("startDate", startDate); hs.Add("endDate", endDate); hs.Add("items", list); int arrStart = -1; int arrItem = -1; int arrEnd = -1; List <FishEntity.CallRecordsEntity> items = null; for (int idx = firstRow; idx <= lastRow; idx++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(idx); foreach (NPOI.SS.UserModel.ICell cell in row.Cells) { string val = cell.ToString(); if (string.IsNullOrEmpty(val)) { continue; } foreach (System.Collections.DictionaryEntry entry in hs) { string key1 = "<jx:forEach items=\"${" + entry.Key.ToString() + "}\" var=\"item\"}>"; if (val.Equals(key1)) { arrStart = idx; items = entry.Value as List <FishEntity.CallRecordsEntity>; break; } string key2 = "</jx:forEach>"; if (val.Equals(key2)) { arrEnd = idx; break; } string key = "${" + entry.Key.ToString() + "}"; if (val.Contains(key)) { val = val.Replace(key, entry.Value.ToString()); } } } } if (arrStart >= 0 && arrEnd >= 2) { arrItem = arrStart + 1; NPOI.SS.UserModel.IRow rrrow = sheet.GetRow(arrItem); List <KV> columns = GetItemNames(rrrow); NPOI.SS.UserModel.IRow row = null; for (int idx = 0; idx < items.Count; idx++) { int tttidx = arrStart + idx; if (tttidx != arrItem) { row = sheet.CreateRow(tttidx); NPOI.SS.UserModel.IRow ffrow = sheet.GetRow(arrItem); //row = sheet.CopyRow(arrItem, tttidx); for (int i = 0; i < columns.Count; i++) { row.CreateCell(i); } } else { row = rrrow; } foreach (NPOI.SS.UserModel.ICell cell in row.Cells) { foreach (KV kv in columns) { if (kv.colIdx == cell.ColumnIndex) { System.Reflection.PropertyInfo prop = items[idx].GetType().GetProperty(kv.name); if (prop != null) { string temp = prop.GetValue(items[idx], null).ToString(); cell.SetCellValue(temp); } } } } } if ((arrEnd - arrStart + 1) > items.Count) { //TODO } string fpath = Application.StartupPath + "\\test.xls"; System.IO.FileStream fss = new FileStream(fpath, FileMode.Create, FileAccess.Write); workbook.Write(fss); fss.Close(); } }