public int WriteArray_To_ExcelFormulas(int rowAvailableCell, int startingCol, string[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(infoArray[rowCounter, columnCounter]);
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
/// <summary>
/// Копирование содержимого ячеек с соранением типа данных
/// </summary>
/// <param name="oldCell"></param>
/// <param name="newCell"></param>
private void CopyCellValue(HSSFCell oldCell, XSSFCell newCell)
{
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
public void TestMultisheetFormulaEval()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;
// sheet1 A1
XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 A1
cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 B1
cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet3 A1
cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
Assert.AreEqual(3.0, cell.NumericCellValue, 0);
cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
Assert.AreEqual(4.0, cell.NumericCellValue, 0);
}
finally
{
wb.Close();
}
}
public StringBuilder readXls(string file, EERRDataAndMethods eerr, XSSFWorkbook twb, bool applyRate, Double rate)
{
StringBuilder retVal = new StringBuilder("");
HSSFWorkbook wb;
wb = new HSSFWorkbook(new FileStream(file, FileMode.Open));
ISheet sheet = wb.GetSheetAt(0);
IRow r = sheet.GetRow(0);
ICell c = r.GetCell(0);
string company = c.StringCellValue;
XSSFSheet sh = (XSSFSheet)twb.GetSheet("Estado resultado");
string acct = "";
string acctDesc = "";
for (int i = 1; i < sheet.LastRowNum; i++)
{
r = sheet.GetRow(i);
if (r != null)
{
c = r.GetCell(0);
if (c != null)
{
if (c.CellType == CellType.String && (c.StringCellValue).StartsWith(C_STR_IN_ACCOUNT))
{
acct = c.StringCellValue;
acct = acct.Substring(C_STR_IN_ACCOUNT.Length).Trim();
int pos = acct.IndexOf(' ');
acctDesc = acct.Substring(pos + 1).Trim();
acct = acct.Substring(0, pos).Trim();
Console.WriteLine("Account: " + acct + " " + acctDesc);
}
else if (acct.Length > 0 && r.LastCellNum >= 16)
{
//"Estado" 1,"Empresa" 2,"Agrupacion" 3,"Marca," 4,"EERR" 5,"Detalle EERR" 6,"Cuenta" 7,"Desc Cuenta" 8,
//"Mes" 9,"Fecha" 10,"# Compte" 11,"Tipo;Glosa" 12,"Area" 13,"C.Costo" 14,"Item" 15,"Desc Item" 16, "F.Efec" 17,
//"Analisis" 18,"Refer" 19,"Fch Ref" 20,"Fch Vto" 21,"DEBE" 22,"HABER" 23,"SALDO" 24,"Sucursal" 25
string s = "";
XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1);
XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_STAT - 1);
cell.SetCellValue(C_DATA_STATUS);
cell = (XSSFCell)row.CreateCell(C_OUT_CIA - 1);
cell.SetCellValue(company);
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DESC_AREA - 1);
cell.SetCellValue(eerr.getAgrupacion(c.ToString()));
cell = (XSSFCell)row.CreateCell(C_OUT_BRAND - 1);
cell.SetCellValue(eerr.getBrand(getCellValue(c)));
cell = (XSSFCell)row.CreateCell(C_OUT_DET_EERR - 1);
cell.SetCellValue(eerr.getLinea(acct));
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_NUM - 1);
cell.SetCellValue(acct);
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_DESC - 1);
cell.SetCellValue(acctDesc);
c = r.GetCell(C_IN_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_MONTH - 1);
cell.SetCellValue(getMonth(c));
c = r.GetCell(C_IN_COMPTE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMPTE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_TYPE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_TYPE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COMMENT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMMENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_AREA - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COST_CENTER - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COST_CENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_ITEM - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM - 1);
cell.SetCellValue(getCellValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM_DESC - 1);
cell.SetCellValue(eerr.getItem(getCellValue(c)));
c = r.GetCell(C_IN_EFF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EFF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_ANALISYS_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ANALYSIS_DATE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REFERENCE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_EXP_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EXP_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
XSSFCell deb = null;
short doubleFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //wb.CreateDataFormat().GetFormat("#,##0");
double v = 0;
c = r.GetCell(C_IN_DEBIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
deb = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
cell.SetCellValue((applyRate?rate:1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
XSSFCell cred = null;
c = r.GetCell(C_IN_CREDIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cred = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BALANCE - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BALANCE - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(String.Format("{0}{1}-{2}{3}", C_COL_DEBIT, cell.Row.RowNum + 1, C_COL_CREDIT, cell.Row.RowNum + 1));
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BRANCH - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BRANCH - 1);
cell.SetCellValue(eerr.getSucursal(s));
}
}
}
}
}
}
return(retVal);
}
private void DgvToXlsx(string fileName, DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2007格式文件（*.xlsx）|*.xlsx";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(fileName);
XSSFRow headRow = (XSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
//const string C_STR_IN_ACCOUNT = "Cuenta Contable";
//const char C_COL_SEPARATOR = ';';
//const string C_ERR_MSG_FILE_FMT_ERR = "File format incorrect";
//const string C_DATA_STATUS = "REAL";
public StringBuilder readXlsx(string file, /*EERRDataAndMethods eerr,*/ XSSFWorkbook twb)
{
StringBuilder retVal = new StringBuilder("");
IWorkbook wb = null;
FileInfo fi = new FileInfo(file);
if (file.EndsWith(".xlsx"))
{
wb = new XSSFWorkbook(fi);
}
else if (file.EndsWith(".xls"))
{
wb = new HSSFWorkbook(new FileStream(file, FileMode.Open));
}
ISheet sheet = wb.GetSheetAt(0);
int rowNum = 0;
IRow r = sheet.GetRow(rowNum);
ICell c = r.GetCell(0);
string company = c.StringCellValue;
XSSFSheet sh = (XSSFSheet)twb.GetSheet("Data");
while (++rowNum < sheet.LastRowNum)
{
r = sheet.GetRow(rowNum);
c = r.GetCell(0);
if (c.StringCellValue.Trim().Equals("Cliente"))
{
break;
}
}
while (++rowNum < sheet.LastRowNum)
{
String sCuenta = "";
String sNum = "";
String sCuenta2 = "";
String sCliente = "";
String sSuc = "";
String sNombre = "";
r = sheet.GetRow(rowNum);
c = r.GetCell(C_IN_CUENTA);
String value = getCellValue(c).Trim();
if (value.Equals(""))
{
String aux = value;
c = r.GetCell(C_IN_SUC);
value = getCellValue(c).Trim();
if (value.Equals(""))
{
continue;
}
value = aux;
}
int iCuenta;
if (int.TryParse(value, out iCuenta)) //Nro de cliente
{
sCliente = value;
sSuc = "0";
sNombre = "NN";
c = r.GetCell(C_IN_NOMBRE); // Nombre del cliente
value = getCellValue(c); // "- 3 / 0 BLANCA ALVARADO DE ARANDIA"
value = value.Remove(0, 3); // "3 / 0 BLANCA ALVARADO DE ARANDIA"
int pos;
for (pos = value.IndexOf('/') + 2; pos < value.Length && value[pos] != ' '; pos++)
{
;
}
if (pos < value.Length)
{
sSuc = value.Substring(0, pos); //"3 / 0"
sNombre = value.Substring(pos + 1); // "BLANCA ALVARADO DE ARANDIA"
}
}
else if (value.Trim().Length > 0) //Numero de cuenta
{
String[] ss = value.Split(' ');
sCuenta = ss[0].Substring(0, 18);
sCuenta2 = ss[0];
sNum = ss[1];
continue;
}
XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1);
XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_CTAS);
cell.SetCellValue(sCuenta);
cell = (XSSFCell)row.CreateCell(C_OUT_NUM);
cell.SetCellValue(sNum);
cell = (XSSFCell)row.CreateCell(C_OUT_CTAL);
cell.SetCellValue(sCuenta2);
cell = (XSSFCell)row.CreateCell(C_OUT_CLTE);
cell.SetCellValue(sCliente);
cell.SetCellType(CellType.Numeric);
cell = (XSSFCell)row.CreateCell(C_OUT_SUC);
cell.SetCellValue(sSuc); //"3 / 0"
cell = (XSSFCell)row.CreateCell(C_OUT_NOMBRE);
cell.SetCellValue(sNombre); // "BLANCA ALVARADO DE ARANDIA"
c = r.GetCell(C_IN_SUC); // Sucursal
cell = (XSSFCell)row.CreateCell(C_OUT_SUCURSAL);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_GRU); // Grupo
cell = (XSSFCell)row.CreateCell(C_OUT_GRUPO);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_NUM); // Numero
cell = (XSSFCell)row.CreateCell(C_OUT_NUMERO);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_FECHA); // Fecha
cell = (XSSFCell)row.CreateCell(C_OUT_FECHA);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_VCTO); // Vencimiento
cell = (XSSFCell)row.CreateCell(C_OUT_VCTO);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_SALDOPOR); // Saldo por vencer
cell = (XSSFCell)row.CreateCell(C_OUT_SALDOP);
cell.SetCellValue(getCellValue(c));
cell.SetCellType(CellType.Numeric);
c = r.GetCell(C_IN_SALDOV); // Saldo vencido
cell = (XSSFCell)row.CreateCell(C_OUT_SALDOV);
cell.SetCellValue(getCellValue(c));
cell.SetCellType(CellType.Numeric);
c = r.GetCell(C_IN_DIAS); // Dias
cell = (XSSFCell)row.CreateCell(C_OUT_DIAS);
cell.SetCellValue(getCellValue(c));
cell.SetCellType(CellType.Numeric);
c = r.GetCell(C_IN_SALDODOC); // Dias
cell = (XSSFCell)row.CreateCell(C_OUT_SALDOD);
cell.SetCellValue(getCellValue(c));
cell.SetCellType(CellType.Numeric);
}
return(retVal);
}
public void CopyRange(ISheet sheet, IWorkbook wb, SurnameObject Surname)
{
if (Surname.Start == 0 || Surname.Finish == 0)
{
MessageBox.Show("Не смог найти начало или конец диапазона копирования");
return;
}
for (int sourceRowNum = Surname.Start; sourceRowNum <= Surname.Finish; sourceRowNum++)
{
//read row
IRow sourceRow = sheet.GetRow(sourceRowNum);
IRow newRow = destinationWb.GetSheet(destinationWb.GetSheetName(0)).CreateRow(destinationRowNum);
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
XSSFCell oldCell = (XSSFCell)sourceRow.GetCell(i);
XSSFCell newCell = (XSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
XSSFCellStyle newCellStyle = (XSSFCellStyle)destinationWb.CreateCellStyle();
//newCellStyle.CloneStyleFrom(oldCell.CellStyle);
//Borders
CopyBordersStyle(oldCell, newCellStyle);
////Text Style
CopyTextStyle(oldCell, newCellStyle);
////Font
CopyFontStyle(wb, oldCell, newCellStyle);
////newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
SetCellValue(oldCell, newCell);
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
destinationWb.GetSheet(destinationWb.GetSheetName(0)).AddMergedRegion(newCellRangeAddress);
}
}
destinationRowNum++;
}
destinationRowNum++;
}
/// <summary>
/// 导出到Excel函数
/// </summary>
private bool ExportToExcel(string fileName, string[] columnName, string date)
{
bool bRet = true;
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel 文件(*.xlsx;*.xls)|*.xlsx;*.xls";
sfd.FileName = fileName;
if (sfd.ShowDialog() != DialogResult.OK)
{
bRet = false;
return(bRet);
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(fileName);
sheet.PrintSetup.PaperSize = 9; // A4打印形式
sheet.PrintSetup.Landscape = false; // 纵向打印
var p = fileName.Split('_');
#region 创建标题名称
XSSFRow title = (XSSFRow)sheet.CreateRow(0);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)title.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region1);
title.CreateCell(0).SetCellValue("庄信万丰（上海）化工有限公司装载机数据表");
ICellStyle titleStyle = workbook.CreateCellStyle();// 样式
// 设置单元格的样式：水平对齐居中
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// 将新的样式赋给单元格
title.GetCell(0).CellStyle = titleStyle;
#endregion
#region 创建日期
XSSFRow dateRow = (XSSFRow)sheet.CreateRow(1);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)dateRow.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region3 = new CellRangeAddress(1, 1, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region3);
string tempDate = "导出时间:" + date;
dateRow.CreateCell(0).SetCellValue(tempDate);
#endregion
// 加载数据
XSSFRow headRow = (XSSFRow)sheet.CreateRow(2);
for (int i = 0; i < columnName.Count(); i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(columnName[i]);
headCell.CellStyle = AddStytle(workbook);
}
for (int i = 0; i < dictData.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 3);
for (int j = 0; j < dictData[i].Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
cell.CellStyle = AddStytle(workbook);
if (dictData[i][j] == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dictData[i][j] is string)
{
cell.SetCellValue(dictData[i][j].ToString());
}
else if (dictData[i][j] is decimal)
{
cell.SetCellValue(Convert.ToSingle(dictData[i][j]));
}
else if (dictData[i][j] is int)
{
cell.SetCellValue(Convert.ToInt32(dictData[i][j]));
}
else if (dictData[i][j] is double)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is Decimal)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is DateTime)
{
cell.SetCellValue(Convert.ToDateTime(dictData[i][j]).ToString("yyyy-MM-dd hh:ss:mm"));
}
}
}
}
for (int i = 0; i < columnName.Count(); i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
workbook.Write(fs);
}
#endregion
AutoDeleteMessageBox Auto = new AutoDeleteMessageBox(); // 自动关闭窗口
MessageBox.Show("恭喜，导出成功!", "MessageBox");
return(bRet);
}
/// <summary>
///
/// </summary>
/// <param name="error"></param>
/// <param name="strFileName"></param>
/// <param name="dsSource"></param>
/// <param name="pwd"></param>
/// <param name="rowIndex"></param>
/// <param name="wait"></param>
/// <param name="isAppend"></param>
/// <param name="dateFormat"></param>
public static void DataTableToExcel(ref string error, string strFileName, DataSet dsSource, string pwd, ref int rowIndex, WaitDialogForm wait = null, bool isAppend = false, string dateFormat = "yyyy-MM-dd")
{
try {
error = string.Empty;
FileStream fs = null;
XSSFWorkbook workbook = null;
if (!isAppend)
{
fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write);
workbook = new XSSFWorkbook();
}
else
{
fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
workbook = new XSSFWorkbook(fs);//将文件读到内存，在内存中操作excel
}
fs.Close();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Xw";
// workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xw"; //填加xls文件作者信息
si.ApplicationName = "xw"; //填加xls文件创建程序信息
si.LastAuthor = "xw"; //填加xls文件最后保存者信息
si.Comments = "xw"; //填加xls文件作者信息
si.Title = "xw"; //填加xls文件标题信息
si.Subject = "xw"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
// workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFCellStyle rowStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont fontRow = (XSSFFont)workbook.CreateFont();
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "微软雅黑";
//font.Boldweight = 700;
headStyle.SetFont(font);
fontRow.FontHeightInPoints = 9;
fontRow.FontName = "微软雅黑";
rowStyle.SetFont(fontRow);
for (int k = 0; k < dsSource.Tables.Count; k++)
{
var dtSource = dsSource.Tables[k];
XSSFSheet sheet = null;
if (!isAppend)
{
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
else
{
sheet = (XSSFSheet)workbook.GetSheet(dtSource.TableName);
if (sheet == null)
{
isAppend = false;
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
}
if (wait != null)
{
wait.SetCaption(string.Format("正在创建第 {0} / {1} Excel的Sheet中", (k + 1), dsSource.Tables.Count));//"（" + (k + 1) + " /" + dsSource.Tables.Count + "）Excel的Sheet中...");
}
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (!isAppend)
{
//if (rowIndex != 0) {
//sheet = (XSSFSheet)workbook.CreateSheet();
//if (!string.IsNullOrEmpty(pwd)) {
// sheet.ProtectSheet(pwd);
//}
//}
#region 表头及样式
// if (!string.IsNullOrEmpty(strHeaderText)) {
// rowIndex++;
// XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// // headStyle.Alignment = CellHorizontalAlignment.CENTER;
// XSSFFont font = (XSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// }
#endregion
#region 列头及样式
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
isAppend = true;
rowIndex = 1;
}
#endregion
if (wait != null)
{
wait.SetCaption(string.Format("正在将数据写入Excel第 {0} / {1} 行中...", rowIndex, dtSource.Rows.Count));//"正在写入第（" + (k + 1) + "/" + dsSource.Tables.Count + "个ExcelSheet表格中的第 " + rowIndex + "/" + dtSource.Rows.Count + " 行数据...");
}
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = rowStyle;
//newCell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
var obj = row[column];
if (obj == null)
{
continue;
}
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double tempVal = 0;
var isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
isflg = System.DateTime.TryParse(drValue, out dateV);
if (isflg)
{
newCell.SetCellValue(drValue);
}
else
{
newCell.SetCellValue(string.Empty);
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.UInt16": //整型
case "System.UInt32":
case "System.UInt64":
int intV = 0;
isflg = int.TryParse(drValue, out intV);
if (isflg)
{
newCell.SetCellValue(intV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.Single": //浮点型
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
isflg = double.TryParse(drValue, out doubV);
if (isflg)
{
newCell.SetCellValue(doubV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
newCell.SetCellType(CellType.String);
break;
default:
tempVal = 0;
isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
}
}
#endregion
rowIndex++;
}
if (sheet != null)
{
sheet.ForceFormulaRecalculation = true;
}
}
//将内存数据写到文件
using (FileStream fs1 = File.OpenWrite(strFileName)) {
workbook.Write(fs1);
workbook.Close();
}
} catch (Exception ex) {
error = $"导出数据出错{ex.Message}";
}
}
