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();
}
}
