void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private static void CopyCell(NPOI.SS.UserModel.ICell sCell, NPOI.SS.UserModel.ICell dCell, List <ICellStyle> dCellStyles, List <IFont> dFonts)
{
ICellStyle currCellStyle = dCell.Sheet.Workbook.FindStyle(sCell.Sheet.Workbook, sCell.CellStyle, dCellStyles, dFonts);
if (currCellStyle == null)
{
currCellStyle = dCell.Sheet.Workbook.CreateCellStyle().CopyStyle(sCell.CellStyle, dCell.Sheet.Workbook, sCell.Sheet.Workbook, dCellStyles, dFonts);
}
dCell.CellStyle = currCellStyle;
switch (sCell.CellType)
{
case CellType.String:
dCell.SetCellValue(sCell.StringCellValue);
break;
case CellType.Numeric:
dCell.SetCellValue(sCell.NumericCellValue);
break;
case CellType.Blank:
dCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
dCell.SetCellValue(sCell.BooleanCellValue);
break;
case CellType.Error:
dCell.SetCellValue(sCell.ErrorCellValue);
break;
case CellType.Formula:
dCell.SetCellValue(sCell.CellFormula);
break;
default:
break;
}
}
//private static void GetEmail()
//{
// using (var emailClient = new Pop3Client())
// {
// emailClient.Connect(.PopServer, _emailConfiguration.PopPort, true);
// emailClient.AuthenticationMechanisms.Remove("XOAUTH2");
// emailClient.Authenticate(_emailConfiguration.PopUsername, _emailConfiguration.PopPassword);
// List<MailMessage> emails = new List<EmailMessage>();
// for (int i = 0; i < emailClient.Count && i < maxCount; i++)
// {
// var message = emailClient.GetMessage(i);
// var emailMessage = new EmailMessage
// {
// Content = !string.IsNullOrEmpty(message.HtmlBody) ? message.HtmlBody : message.TextBody,
// Subject = message.Subject
// };
// emailMessage.ToAddresses.AddRange(message.To.Select(x => (MailboxAddress)x).Select(x => new EmailAddress { Address = x.Address, Name = x.Name }));
// emailMessage.FromAddresses.AddRange(message.From.Select(x => (MailboxAddress)x).Select(x => new EmailAddress { Address = x.Address, Name = x.Name }));
// }
// }
//}
private static List <Transaction> getTransactionsFromFile(string path)
{
List <Transaction> transactionList = new List <Transaction>();
HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfwb = new HSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheet("transactions");
int iDateColumnIndex = -1;
int iTransactionDetailsColumnIndex = -1;
int iDebitColumnIndex = -1;
int iCreditColumnIndex = -1;
for (int col = 0; col < sheet.GetRow(1).Cells.Count - 1; col++)
{
NPOI.SS.UserModel.ICell cell = sheet.GetRow(1).GetCell(col);
if (cell.StringCellValue == "Data")
{
Console.WriteLine($"Date found on column {col}");
iDateColumnIndex = col;
}
if (cell.StringCellValue == "Detalii tranzactie")
{
Console.WriteLine($"Transaction details found on column {col}");
iTransactionDetailsColumnIndex = col;
}
if (cell.StringCellValue == "Debit")
{
Console.WriteLine($"Debit found on column {col}");
iDebitColumnIndex = col;
}
if (cell.StringCellValue == "Credit")
{
Console.WriteLine($"Credit found on column {col}");
iCreditColumnIndex = col;
}
}
if (iDateColumnIndex == -1 || iTransactionDetailsColumnIndex == -1 || iDebitColumnIndex == -1 || iCreditColumnIndex == -1)
{
throw new Exception("Could not find one of the columns.");
}
for (int row = 0; row <= sheet.LastRowNum; row++)
{
for (int col = 0; col < sheet.GetRow(row).Cells.Count - 1; col++)
{
NPOI.SS.UserModel.ICell cell = sheet.GetRow(row).GetCell(col);
if (cell.CellType != CellType.Blank)
{
if (col == iDebitColumnIndex)
{
if (cell.CellType == CellType.Numeric)
{
cell.SetCellType(CellType.String);
transactionList.Add(new Transaction());
transactionList[transactionList.Count - 1].Date = sheet.GetRow(row).GetCell(iDateColumnIndex).DateCellValue;
transactionList[transactionList.Count - 1].Debit = double.Parse(cell.StringCellValue, CultureInfo.InvariantCulture);
transactionList[transactionList.Count - 1].To = sheet.GetRow(row + 2).GetCell(iTransactionDetailsColumnIndex).StringCellValue;
transactionList[transactionList.Count - 1].Type = sheet.GetRow(row).GetCell(iTransactionDetailsColumnIndex).StringCellValue;
}
}
if (col == iCreditColumnIndex)
{
if (cell.CellType == CellType.Numeric)
{
cell.SetCellType(CellType.String);
transactionList.Add(new Transaction());
transactionList[transactionList.Count - 1].Date = sheet.GetRow(row).GetCell(iDateColumnIndex).DateCellValue;
transactionList[transactionList.Count - 1].Credit = double.Parse(cell.StringCellValue, CultureInfo.InvariantCulture);
transactionList[transactionList.Count - 1].From = sheet.GetRow(row + 1).GetCell(iTransactionDetailsColumnIndex).StringCellValue;
transactionList[transactionList.Count - 1].Type = sheet.GetRow(row).GetCell(iTransactionDetailsColumnIndex).StringCellValue;
}
}
}
}
}
return(transactionList);
}
public void Export(Stream ms, DataTable dt, string postfix)
{
//var fileName = m_Handler.GetFileName();
NPOI.SS.UserModel.IWorkbook book = null;
if (postfix == ".xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (postfix == ".xlsx")
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
}
m_Handler.Encode(ref dt);
//WriteExcel(ref book, dt);
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle cellStyle = book.CreateCellStyle();
var font = book.CreateFont();
font.FontHeightInPoints = 14;
font.IsBold = true;
cellStyle.SetFont(font);
int index = 0;
//string caption = m_Handler.GetCaption();
//if (string.IsNullOrEmpty(caption))
//{
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.CellStyle = cellStyle;
cell.SetCellValue(item.Caption);
index++;
}
//}
//else
//{
// NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
// cell.SetCellType(NPOI.SS.UserModel.CellType.String);
// cell.CellStyle = cellStyle;
// cell.SetCellValue(caption);
//}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//// 写入
//MemoryStream ms = new MemoryStream();
book.Write(ms);
book = null;
//using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
//ms.Close();
//ms.Dispose();
}