private void FillExcelMonthly(HSSFSheet sheet1, DataTable dt, int startRow)
{
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//最后一列bookingDate 不显示
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
HSSFCell cell = sheet1.Items((startRow + i + 1), j);
//HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
if (j == 2 || j == 3 || j == 5)
{
if (!string.IsNullOrEmpty(dr[j].ToString()))
{
cell.SetCellValue(double.Parse(dr[j].ToString()));
}
}
else
{
cell.SetCellValue(dr[j].ToString());
}
//cell.CellStyle = cellStyle;
}
}
}
public void TestMaxTextLength()
{
HSSFSheet sheet = (HSSFSheet) new HSSFWorkbook().CreateSheet();
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
int maxlen = NPOI.SS.SpreadsheetVersion.EXCEL97.MaxTextLength;
Assert.AreEqual(32767, maxlen);
StringBuilder b = new StringBuilder();
// 32767 is okay
for (int i = 0; i < maxlen; i++)
{
b.Append("X");
}
cell.SetCellValue(b.ToString());
b.Append("X");
// 32768 produces an invalid XLS file
try
{
cell.SetCellValue(b.ToString());
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The maximum length of cell contents (text) is 32,767 characters", e.Message);
}
}
public static void createConditionH(HSSFSheet sheet, int row, int lscol, string condition, HSSFCellStyle style, int height)
{
HSSFRow hsshRow = sheet.CreateRow(row) as HSSFRow;
hsshRow.Height = (short)(height * 256);
for (int i = 0; i < lscol; i++)
{
HSSFCell cell = hsshRow.CreateCell(i) as HSSFCell;
cell.CellStyle = style;
if (i == 0)
{
HSSFRichTextString richString;
if (condition == null)
{
richString = new HSSFRichTextString("");
}
else
{
richString = new HSSFRichTextString(condition);
}
cell.SetCellValue(richString);
}
else
{
cell.SetCellValue("");
}
}
ExcelUtilsForNPOI.SetCellRangeAddress(sheet, row, row, 0, lscol);
}
public static void Out2ExcelDataReader(System.Data.SqlClient.SqlDataReader dr, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFRow dataRow = null;
HSSFCell cell = null;
int sheetMaxRowIndex = 65535;
int currentRowIndex = 0;
List <string> drColumnNameList = new List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
drColumnNameList.Add(dr.GetName(i));
}
while (dr.Read())
{
if (currentRowIndex == 0)
{
//填充表头
dataRow = sheet.CreateRow(currentRowIndex) as HSSFRow;
for (int i = 0; i < drColumnNameList.Count; i++)
{
cell = dataRow.CreateCell(i) as HSSFCell;
cell.SetCellValue(drColumnNameList[i]);
}
currentRowIndex++;
}
dataRow = sheet.CreateRow(currentRowIndex) as HSSFRow;
for (int i = 0; i < drColumnNameList.Count; i++)
{
cell = dataRow.CreateCell(i) as HSSFCell;
cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cell.SetCellValue(dr[i].ToString());
}
currentRowIndex++;
if (currentRowIndex > sheetMaxRowIndex)
{
sheet = workbook.CreateSheet() as HSSFSheet;
currentRowIndex = 0;
}
}
dr.Close();
dr.Dispose();
//保存
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//sheet.Dispose();
//workbook.Dispose();
cell = null;
dataRow = null;
sheet = null;
workbook = null;
Out2Client(ms, fileName);
}
}
private void FillExcelAccount(HSSFSheet sheet1, DataTable dt, int startRow)
{
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
HSSFCell cell = sheet1.Items((startRow + i + 1), j);
if (j == 4)
{
cell.SetCellValue(double.Parse(dr[j].ToString()));
}
else if (j == 1)
{
DateTime dtBooking;
if (DateTime.TryParse(dr[j].ToString(), out dtBooking))
{
cell.SetCellValue(dtBooking);
}
else
{
cell.SetCellValue(dr[j].ToString());
}
}
else
{
cell.SetCellValue(dr[j].ToString());
}
}
}
}
public void TestWriteSheetSimple()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet s = wb1.CreateSheet() as HSSFSheet;
for (int rownum = 0; rownum < 100; rownum++)
{
HSSFRow r = s.CreateRow(rownum) as HSSFRow;
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
HSSFCell c = r.CreateCell(cellnum) as HSSFCell;
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1) as HSSFCell;
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
sanityChecker.CheckHSSFWorkbook(wb1);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
sanityChecker.CheckHSSFWorkbook(wb2);
s = wb2.GetSheetAt(0) as HSSFSheet;
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
wb2.Close();
wb1.Close();
}
private void InsertCell3(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, HSSFWorkbook excelWorkBook)
{ ///品牌
string drValue = drSource[Keys[0]].ToString().Trim();
HSSFCell newCell = null;
newCell = (HSSFCell)currentExcelRow.GetCell(0);
newCell.SetCellValue(drValue);
///款号
drValue = drSource[Keys[1]].ToString().Trim();
newCell = (HSSFCell)currentExcelRow.GetCell(1);
newCell.SetCellValue(drValue);
///商品名称
drValue = drSource[Keys[2]].ToString().Trim();
newCell = (HSSFCell)currentExcelRow.GetCell(2);
newCell.SetCellValue(drValue);
//数量
int intV = 0;
drValue = drSource[Keys[3]].ToString().Trim();
int.TryParse(drValue, out intV);
newCell = (HSSFCell)currentExcelRow.GetCell(3);
newCell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
newCell.SetCellValue(intV);
//金额
double decV = 0;
drValue = drSource[Keys[4]].ToString().Trim();
double.TryParse(drValue, out decV);
newCell = (HSSFCell)currentExcelRow.GetCell(4);
newCell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
newCell.SetCellValue(decV);
}
public void TestCachedTypeChange()
{
HSSFSheet sheet = (HSSFSheet) new HSSFWorkbook().CreateSheet("Sheet1");
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("A1");
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(123);
NPOI.HSSF.Record.Record[] recs = RecordInspector.GetRecords(sheet, 0);
if (recs.Length == 28 && recs[23] is StringRecord)
{
throw new AssertionException("Identified bug - leftover StringRecord");
}
ConfirmStringRecord(sheet, false);
// string to error code
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellErrorValue((byte)ErrorConstants.ERROR_REF);
ConfirmStringRecord(sheet, false);
// string to boolean
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(false);
ConfirmStringRecord(sheet, false);
}
/// <summary>
/// 写入小数
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="val"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static bool WriteCellDot(int row, int col, string val, ref HSSFSheet sheet)
{
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
if (isNumber(val))
{
SetCellFormat(row, col, CellFormat.Point2, ref myExcelWork);
double valNum = double.Parse(val);
t_cell.SetCellValue(valNum);
}
else
{
t_cell.SetCellValue(val);
}
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
if (isNumber(val))
{
SetCellFormat(row, col, CellFormat.Point2, ref myExcelWork);
double valNum = double.Parse(val);
t_cell.SetCellValue(valNum);
}
else
{
t_cell.SetCellValue(val);
}
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
if (isNumber(val))
{
SetCellFormat(row, col, CellFormat.Point2, ref myExcelWork);
double valNum = double.Parse(val);
t_cell.SetCellValue(valNum);
}
else
{
t_cell.SetCellValue(val);
}
}
}
return(true);
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2003ToString(string path, string name)
{
HSSFWorkbook hssfworkbook;
var fpath = HttpContext.Current.Server.MapPath(path + name);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
if (dr == "$ClientName$")
{
cell.SetCellValue("这是写名称");
}
if (dr == "$Content$")
{
cell.SetCellValue("这是写联系人");
}
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
} /*
* ②：将文档保存到指定路径
*/
var destFileName = HttpContext.Current.Server.MapPath(path + "new_" + name);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
sb.Append("</table>");
return(sb.ToString());
}
public static void ExportLanguages()
{
string path = EditorUtility.SaveFilePanel("Export Languages", "", "", "xls");
if (string.IsNullOrEmpty(path))
{
return;
}
using (Stream writer = File.Create(path))
{
LanguageItem[] allLanguageItems =
TypeHelper.GetAllTypes(AllTypeCategory.All)
.Where(type => typeof(IInfo).IsAssignableFrom(type))
.Select(type => TypeHelper.FindType(InfoResolver.Resolve(type), typeof(LanguageItem <string>)))
.SelectMany(objects => objects)
.Cast <LanguageItem>()
.ToArray();
LanguageEditorInfo languageEditorInfo = EditorInfoResolver.Resolve <LanguageEditorInfo>();
string[] languageNames = languageEditorInfo.Languages.Select(info => info.Name).ToArray();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Languages");
HSSFRow header = (HSSFRow)sheet.CreateRow(0);
HSSFCell hssfCell = (HSSFCell)header.CreateCell(0);
hssfCell.SetCellValue("Item Id");
for (int i = 0; i < languageNames.Length; i++)
{
HSSFCell cell = (HSSFCell)header.CreateCell(i + 1);
cell.SetCellValue(languageNames[i]);
}
for (int i = 0; i < allLanguageItems.Length; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
HSSFCell itemIdcell = (HSSFCell)row.CreateCell(0);
itemIdcell.SetCellValue(allLanguageItems[i].Id);
for (int j = 0; j < languageNames.Length; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j + 1);
if (languageEditorInfo.Languages[j].LanguageDatas.ContainsKey(allLanguageItems[i].Id))
{
object languageData = languageEditorInfo.Languages[j].LanguageDatas[allLanguageItems[i].Id];
cell.SetCellValue(languageData == null?string.Empty:languageData.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
}
for (int i = 0; i < languageNames.Length + 1; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(writer);
}
}
public void FillStatisticIntoExcelSheet(HSSFSheet sheet)
{
AssignCombinedGroupColumn();
CalculateGroupStatistic();
List <Group> list = groups;
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
int i = 0;
HSSFCell cell = (HSSFCell)row.CreateCell((short)i);
cell.SetCellValue("Name");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Description");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Note");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Combined Group");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Unique Machines");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Unique UserID");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Count F");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("Count H");
cell = (HSSFCell)row.CreateCell((short)++i);
cell.SetCellValue("EULA Machines");
for (int j = 0; j < list.Count; j++)
{
Group param = list[j];
HSSFRow r = (HSSFRow)sheet.CreateRow(j + 1);
param.FillDataIntoRow(r);
}
}
protected static void SetCellValueDate(HSSFCell cell, DateTime date, string emptyText)
{
if (date == DateTime.MinValue)
{
cell.SetCellValue(emptyText);
}
else
{
cell.SetCellValue(date);
}
}
private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.Index;
XSSFCellStyle sourceCellStyle = null;
if (styleMap.TryGetValue(stHashCode, out sourceCellStyle))
{
}
HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
// destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
if (!styleMap.Any(p => p.Key == stHashCode))
{
styleMap.Add(stHashCode, sourceCellStyle);
}
destnCellStyle.VerticalAlignment = VerticalAlignment.Top;
newCell.CellStyle = (HSSFCellStyle)destnCellStyle;
}
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;
}
}
/// <summary>設定儲存格資料/會自動轉換成數字</summary>
/// <param name="cell">儲存格物件</param>
/// <param name="value">字串</param>
private void SetCellValue(HSSFCell cell, string value)
{
double _double;
if (double.TryParse(value, out _double) == true)
{
cell.SetCellValue(_double);
return;
}
cell.SetCellValue(value);
}
public static Stream ExportDataTableToExcelToSchedule(DataTable sourceTable, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream stream = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
row.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rownum = 1;
foreach (DataRow row2 in sourceTable.Rows)
{
HSSFRow row3 = (HSSFRow)sheet.CreateRow(rownum);
foreach (DataColumn column2 in sourceTable.Columns)
{
row3.CreateCell(column2.Ordinal).SetCellValue(row2[column2].ToString());
if (((column2.Ordinal == 9) || (column2.Ordinal == 10)) || (((column2.Ordinal == 11) || (column2.Ordinal == 12)) || (column2.Ordinal == 13)))
{
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 3;
HSSFCell cell = (HSSFCell)row3.GetCell(column2.Ordinal);
if (cell.StringCellValue == "1")
{
font.Color = 10;
cell.CellStyle.SetFont(font);
cell.SetCellValue("●");
}
else if (cell.StringCellValue == "0")
{
font.Color = 0x7fff;
cell.CellStyle.SetFont(font);
cell.SetCellValue("●");
}
}
}
rownum++;
}
workbook.Write(stream);
stream.Flush();
stream.Position = 0L;
sheet = null;
row = null;
workbook = null;
return(stream);
}
private void SaveDownloadResult(string filePath)
{
if (ConfigProperty.dictFileInfo.Count >= 1)
{
MessageHelper.MsgWait("正在保存抵扣发票下载信息，请耐心等待...");
try
{
string path = filePath + @"\抵扣发票下载结果" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("下载结果");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("序号");
HSSFCell cell2 = (HSSFCell)row.CreateCell(1);
cell2.SetCellValue("文件名");
HSSFCell cell3 = (HSSFCell)row.CreateCell(2);
cell3.SetCellValue("下载情况");
int rownum = 1;
foreach (KeyValuePair <string, string> pair in ConfigProperty.dictFileInfo)
{
row = (HSSFRow)sheet.CreateRow(rownum);
((HSSFCell)row.CreateCell(0)).SetCellValue((double)rownum);
((HSSFCell)row.CreateCell(1)).SetCellValue(pair.Key);
cell3 = (HSSFCell)row.CreateCell(2);
if (pair.Value == "1")
{
cell3.SetCellValue("已下载");
}
else
{
cell3.SetCellValue("未下载");
}
rownum++;
}
using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
}
catch (Exception exception)
{
MessageHelper.MsgWait();
MessageBox.Show(exception.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Hand);
this.loger.Error("抵扣发票批量下载：保存下载结果异常：" + exception.ToString());
}
MessageHelper.MsgWait();
}
}
public new void TestUpdateCachedFormulaResultFromErrorToNumber_bug46479()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet("Sheet1") as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cellA1 = row.CreateCell(0) as HSSFCell;
HSSFCell cellB1 = row.CreateCell(1) as HSSFCell;
cellB1.CellFormula = "A1+1";
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
cellA1.SetCellErrorValue(FormulaError.NAME.Code);
fe.EvaluateFormulaCell(cellB1);
cellA1.SetCellValue(2.5);
fe.NotifyUpdateCell(cellA1);
try
{
fe.EvaluateInCell(cellB1);
}
catch (InvalidOperationException e)
{
if (e.Message.Equals("Cannot get a numeric value from a error formula cell"))
{
Assert.Fail("Identified bug 46479a");
}
}
Assert.AreEqual(3.5, cellB1.NumericCellValue, 0.0);
wb.Close();
}
public void SaveValue(int rowIndex, int columnIndex, System.DateTime val)
{
HSSFCell cell = GetCell(rowIndex, columnIndex, true);
cell.SetCellValue(val);
cell.SetAsActiveCell();
}
public void SaveValue(int rowIndex, int columnIndex, float val)
{
HSSFCell cell = GetCell(rowIndex, columnIndex, true);
cell.SetCellValue(NPOIEx.Round2D(val, 2));
cell.SetAsActiveCell();
}
public void AddFooterCellToCurrentRow(string text, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.LEFT)
{
//HSSFCellStyle style = FooterCellStyle(xAlign);
int idx = (index < 0) ? columnIndex:index;
ManualAdjustColumnWidth(text, idx);
//HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.GetCell(idx);
HSSFFont fFooter = (HSSFFont)workbook.CreateFont();
fFooter.FontName = "Calibri";
fFooter.Boldweight = (short)FontBoldWeight.BOLD;
fFooter.Color = HSSFColor.BLACK.index;
HSSFCellStyle cFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cFooterStyle.SetFont(fFooter);
cFooterStyle.BorderTop = CellBorderType.THIN;
cFooterStyle.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.CreateCell(idx, CellType.STRING);
cellFtr.CellStyle = cFooterStyle;
cellFtr.CellStyle.BorderLeft = CellBorderType.NONE;
cellFtr.CellStyle.BorderRight = CellBorderType.NONE;
cellFtr.SetCellValue(text);
ManualAdjustColumnWidth(text, idx);
cellFtr.CellStyle.Alignment = xAlign;
columnIndex++;
}
public void TestRepeatingColsRows()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sheet = wb1.CreateSheet("Test Print Titles") as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(1) as HSSFCell;
cell.SetCellValue(new HSSFRichTextString("hi"));
CellRangeAddress cra = CellRangeAddress.ValueOf("A1:B1");
sheet.RepeatingColumns = (cra);
sheet.RepeatingRows = (cra);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
sheet = wb2.GetSheetAt(0) as HSSFSheet;
Assert.AreEqual("A:B", sheet.RepeatingColumns.FormatAsString());
Assert.AreEqual("1:1", sheet.RepeatingRows.FormatAsString());
wb2.Close();
wb1.Close();
}
public void TestWriteDataFormat()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet s1 = wb1.CreateSheet() as HSSFSheet;
HSSFDataFormat format = wb1.CreateDataFormat() as HSSFDataFormat;
HSSFCellStyle cs = wb1.CreateCellStyle() as HSSFCellStyle;
short df = format.GetFormat("0.0");
cs.DataFormat = (df);
HSSFCell c1 = s1.CreateRow(0).CreateCell(0) as HSSFCell;
c1.CellStyle = (cs);
c1.SetCellValue(1.25);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
HSSFSheet s2 = wb2.GetSheetAt(0) as HSSFSheet;
HSSFCell c2 = s2.GetRow(0).GetCell(0) as HSSFCell;
format = wb2.CreateDataFormat() as HSSFDataFormat;
Assert.AreEqual(1.25, c2.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(df), "0.0");
Assert.AreEqual(format, wb2.CreateDataFormat());
wb2.Close();
wb1.Close();
}
/// <summary>
/// Function to add a new column to the Excel worksheet
/// </summary>
/// <param name="columnHeader">The column header to be added</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the column header</param>
public void AddColumn(String columnHeader, ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(0); //0 because header is always in the first row
int lastCellNum = row.LastCellNum;
if (lastCellNum == -1)
{
lastCellNum = 0;
}
HSSFCell cell = (HSSFCell)row.CreateCell(lastCellNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(columnHeader);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}
public void AddCell(string text, int index = -1, HorizontalAlignment xAlign = HorizontalAlignment.GENERAL)
{
if (index >= 0)
{
columnIndex = index + offset;
}
HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(columnIndex, CellType.STRING);
cell.CellStyle = GetDefaultFontStyle();
cell.CellStyle.BorderLeft = CellBorderType.NONE;
cell.CellStyle.BorderRight = CellBorderType.NONE;
cell.SetCellValue(text);
if (text.Length > 255)
{
cell.CellStyle.WrapText = true;
}
if (text.Length > 255)
{ //todo: edit this part so we don't hardcode
_HSFFGetSheet.SetColumnWidth(columnIndex, 100 * 256);
cell.CellStyle.WrapText = true;
cell.CellStyle.Alignment = HorizontalAlignment.JUSTIFY;
}
else
{
ManualAdjustColumnWidth(text);
cell.CellStyle.Alignment = xAlign;
}
columnIndex++;
}
public void SaveValue(int rowIndex, int columnIndex, string val)
{
HSSFCell cell = GetCell(rowIndex, columnIndex, true);
cell.SetCellValue(val);
cell.SetAsActiveCell();
}
public void ExportExcel(string fileName, DataGridView dgv, int limit)
{
if (dgv.Rows.Count == 0)
{
MessageBox.Show("请先导入「网管导出的表格」，然后再次尝试");
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2003格式|*.xls";
sfd.FileName = DateTime.Now.ToString("yyyy-MM-dd") + "批量保存表格";
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
int lie = dgv.Columns.Count;
if (limit != 0)
{
lie = limit;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName);
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < lie; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < lie; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
}
}
for (int i = 0; i < lie; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
wb.Close();
}
public void AddCurrencyFooterCellToCurrentRow(decimal amount, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.RIGHT)
{
HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(index, CellType.STRING);
ManualAdjustColumnWidth(Convert.ToString(amount), index);
HSSFCellStyle style = FooterCellStyle(xAlign, df.GetFormat("$#,##0.00"));
cell.SetCellValue(Convert.ToDouble(amount));
cell.CellStyle = style;
columnIndex++;
}
public void InsertPageBreaks(HSSFWorkbook workbook, string shtName)
{
//Find instances of the word "pagebreak" and replace with a page break
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(shtName);
int cellctr = 0;
HSSFCell range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
while (range == null || range.ToString() != "EOF")
{
if (range != null && range.ToString() == "pagebreak")
{
sheet.SetRowBreak(cellctr);
range.SetCellValue(string.Empty);
}
cellctr++;
range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
}
range.SetCellValue(string.Empty);
range = (HSSFCell)sheet.GetRow(0).GetCell(0);
}
/// <summary>
/// 将一个源文件的某个单元格的值复制到目标文件的指定单元格
/// 以富文本的形式，保留原格式
/// </summary>
/// <param name="dst_row">目标行</param>
/// <param name="dst_col">目标列</param>
/// <param name="src_row">源行</param>
/// <param name="src_col">源列</param>
/// <param name="dst">目标文件</param>
/// <param name="src">源文件</param>
/// <returns></returns>
public static bool CopyCell(int dst_row, int dst_col, int src_row, int src_col, ref HSSFSheet dst, ref HSSFSheet src)
{
if (src.GetRow(src_row - 1) == null)
{
return(false);
}
else
{
HSSFRow t_src_row = (HSSFRow)src.GetRow(src_row - 1);
if (t_src_row.GetCell(src_col - 1) == null)
{
return(false);
}
else
{
HSSFCell t_src_cell = (HSSFCell)t_src_row.GetCell(src_col - 1);
if (dst.GetRow(dst_row - 1) == null)
{
HSSFRow t_row = (HSSFRow)dst.CreateRow(dst_row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
else
{
HSSFRow t_row = (HSSFRow)dst.GetRow(dst_row - 1);
if (t_row.GetCell(dst_col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
}
}
}
return(true);
}
