//static public void ExportDataGrid(DataTable dt, string FileType, string FileName) //从DataGrid导出
//{
// DataGrid dg = new DataGrid();
// //DataSet dg = new DataSet();
// dg.DataSource = dt;
// dg.DataBind();
// //定义文档类型、字符编码
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
// HttpContext.Current.Response.Charset = "UTF-8";
// HttpContext.Current.Response.ContentEncoding = Encoding.Default;
// HttpContext.Current.Response.ContentType = FileType;
// dg.EnableViewState = false;
// //定义一个输入流
// StringWriter tw = new StringWriter();
// HtmlTextWriter hw = new HtmlTextWriter(tw);
// //目标数据绑定到输入流输出
// dg.RenderControl(hw);
// HttpContext.Current.Response.Write(tw.ToString());
// HttpContext.Current.Response.End();
//}
static public void Output(DataTable dt, string type, string name)
{
org.in2bits.MyXls.XlsDocument doc = new org.in2bits.MyXls.XlsDocument();
doc.FileName = DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();//excel文件名称
//org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");//Excel工作表名称
org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");
org.in2bits.MyXls.Cells cells = sheet.Cells;
int colnum = dt.Columns.Count;//获取DataTable列数
for (int i = 0; i < colnum; i++)
{
cells.Add(1, (i + 1), dt.Columns[i].Caption.ToString());//导出DataTable列名
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < colnum; j++)
{
cells.Add((i + 2), (j + 1), dt.Rows[i][j].ToString());
}
}
doc.Save(HttpContext.Current.Server.MapPath("file/"));
string strFilePath = HttpContext.Current.Server.MapPath("file/") + doc.FileName;
FileInfo fi = new FileInfo(strFilePath);//excelFile为文件在服务器上的地址
HttpResponse contextResponse = HttpContext.Current.Response;
contextResponse.Clear();
contextResponse.Buffer = true;
contextResponse.Charset = "utf8"; //设置了类型为中文防止乱码的出现
contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", name)); //定义输出文件和文件名
contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
contextResponse.ContentEncoding = Encoding.Default;
contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
contextResponse.WriteFile(fi.FullName);
contextResponse.Flush();
contextResponse.End();
}
/// <summary>
/// Writes a DataTable to this Worksheet, beginning at the provided Row
/// and Column indices. A Header Row will be written.
/// </summary>
/// <param name="table">The DataTable to write to this Worksheet.</param>
/// <param name="startRow">The Row at which to start writing the DataTable
/// to this Worksheet (1-based).</param>
/// <param name="startCol">The Column at which to start writing the DataTable
/// to this Worksheet (1-based).</param>
public void Write(DataTable table, int startRow, int startCol)
{
if ((table.Columns.Count + startCol) > BIFF8.MaxCols)
{
throw new ApplicationException(string.Format("Table {0} has too many columns {1} to fit on Worksheet {2} with the given startCol {3}",
table.TableName, table.Columns.Count, BIFF8.MaxCols, startCol));
}
if ((table.Rows.Count + startRow) > (BIFF8.MaxRows - 1))
{
throw new ApplicationException(string.Format("Table {0} has too many rows {1} to fit on Worksheet {2} with the given startRow {3}",
table.TableName, table.Rows.Count, (BIFF8.MaxRows - 1), startRow));
}
int row = startRow;
int col = startCol;
foreach (DataColumn dataColumn in table.Columns)
{
Cells.Add(row, col++, dataColumn.ColumnName);
}
foreach (DataRow dataRow in table.Rows)
{
row++;
col = startCol;
foreach (object dataItem in dataRow.ItemArray)
{
object value = dataItem;
if (dataItem == DBNull.Value)
{
value = null;
}
if (dataRow.Table.Columns[col - startCol].DataType == typeof(byte[]))
{
value = string.Format("[ByteArray({0})]", ((byte[])value).Length);
}
Cells.Add(row, col++, value);
}
}
}
public void SstContinueRecordNoSplit()
{
// This is an example of the CONTINUE record where the spillover
// string is not actually split across two SST records. Once the
// first record is filled up with 632 strings, the 633th string
// is placed in the CONTINUE record.
//
// This means that there are 632 whole 10-char strings (13 bytes
// per 10-char string * 632 = 8216) in the first record with 1
// whole 10-char string in the CONTINUE record.
XlsDocumentDelegate continueDocDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 633; i++)
{
cells.Add(i + 1, 1, (i + 1000000001).ToString());
}
Assert.AreEqual(633, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string continueFile = WriteDocument(continueDocDelegate);
AssertPropertyViaExcelOle(1, 633, 1, continueFile, CellProperties.Value, "1000000633", "Read last string after CONTINUE");
List <Record> continueWorkbookRecords = null;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
continueWorkbookRecords = records;
};
new XlsDocument(continueFile, bytesReadCallback);
Assert.IsNotNull(continueWorkbookRecords, "Workbook records list");
Record sst = GetSstRecord(continueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(1, sst.Continues.Count, "SST Continues");
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss"));
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Bold = true;
titleXF1.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "NO#", titleXF1);
cells.Add(1, 2, "Claim Type", titleXF1);
cells.Add(1, 3, "Amount", titleXF1);
cells.Add(1, 4, "Owner", titleXF1);
cells.Add(1, 5, "Process", titleXF1);
cells.Add(1, 6, "Current Approver", titleXF1);
cells.Add(1, 7, "Submit Date", titleXF1);
cells.Add(1, 8, "Remark", titleXF1);
//添加数据
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml.InnerXml);
for (int i = 0; i < doc.SelectNodes("records").Item(0).SelectNodes("record").Count; i++)
{
if (!string.IsNullOrEmpty(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml))
{
cells.Add(2 + i, 3, Convert.ToDouble(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml), titleXF);
}
else
{
cells.Add(2 + i, 3, "", titleXF);
}
cells.Add(2 + i, 1, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("No").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 2, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Type1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 4, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Person").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 5, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Status1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 6, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Approver").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 7, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedDate").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 8, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Remark").Item(0).InnerXml, titleXF);
}
xls.Send();
}
public void SstTwoContinueRecords()
{
//8,216 bytes available for string records in SST Record
//(before CONTINUE record is necessary)
//8,223 bytes available for string records in CONTINUE Record
//(before next CONTINUE record is necessary)
//A 7-char single-byte (compressed unicode) text string, plus
//the 3 bytes at the beginning of the Unicode
//string data, gives 10 bytes per 7-char string record.
//This gives us 821 7-char strings, the 822nd
//spilling 4 bytes across to the first CONTINUE record.
//Then another 821 (1,643 total), the 1,644th spilling 1 byte
//over to the second CONTINUE Record.
int stringsToWrite = 821;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < stringsToWrite; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(stringsToWrite, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string fileName = WriteDocument(docDelegate);
List <Record> workbookRecords = null;
int continueRecordCount = 0;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
workbookRecords = records;
};
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
Record sst = GetSstRecord(workbookRecords);
Assert.AreEqual(0, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 822;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 1643;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 1644;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(2, sst.Continues.Count, "SST CONTINUE Records");
AssertPropertyViaExcelOle(1, 1644, 1, fileName, CellProperties.Value, (stringsToWrite + 1000000).ToString(),
"Last String value via Excel OLE");
}
public void SstContinueRecord()
{
//8,216 bytes available for data in SST Record
//(before CONTINUE record is necessary)
//A 7-char single-byte (compressed unicode) text string, plus
//the 3 bytes at the beginning of the Unicode
//string data, gives 10 bytes per 7-char string.
//This gives us 821 7-char strings, the 822nd
//spilling across to a CONTINUE record.
XlsDocumentDelegate noContinueDocDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 821; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(821, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string noContinueFile = WriteDocument(noContinueDocDelegate);
AssertPropertyViaExcelOle(1, 821, 1, noContinueFile, CellProperties.Value, "1000821", "Read last string before CONTINUE");
List <Record> noContinueWorkbookRecords = null;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
noContinueWorkbookRecords = records;
};
new XlsDocument(noContinueFile, bytesReadCallback);
Assert.IsNotNull(noContinueWorkbookRecords, "Workbook records list");
Record sst = GetSstRecord(noContinueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(0, sst.Continues.Count, "SST Continues");
XlsDocumentDelegate continueDoc = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 822; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(822, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string continueFile = WriteDocument(continueDoc);
AssertPropertyViaExcelOle(1, 822, 1, continueFile, CellProperties.Value, "1000822", "Read string split over to CONTINUE");
List <Record> continueWorkbookRecords = null;
bytesReadCallback = delegate(List <Record> records)
{
continueWorkbookRecords = records;
};
new XlsDocument(continueFile, bytesReadCallback);
Assert.IsNotNull(continueWorkbookRecords, "Workbook records list");
sst = GetSstRecord(continueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
}
private void AddCells(Record record)
{
Bytes bytes = record.Data;
ushort rowIndex = bytes.Get(0, 2).GetBits().ToUInt16();
ushort colIndex = bytes.Get(2, 2).GetBits().ToUInt16();
ushort lastColIndex = colIndex;
ushort offset = 4;
byte[] rid = record.RID;
bool isMulti = false;
if (rid == RID.MULBLANK)
{
isMulti = true;
rid = RID.BLANK;
}
else if (rid == RID.MULRK)
{
isMulti = true;
rid = RID.RK;
}
if (isMulti)
{
lastColIndex = bytes.Get(bytes.Length - 2, 2).GetBits().ToUInt16();
}
while (colIndex <= lastColIndex)
{
Cell cell = Cells.Add((ushort)(rowIndex + 1), (ushort)(colIndex + 1));
ushort xfIndex = bytes.Get(offset, 2).GetBits().ToUInt16();
offset += 2;
Bytes data;
if (rid == RID.BLANK)
{
data = new Bytes();
}
else if (rid == RID.RK)
{
data = bytes.Get(offset, 4);
offset += 4;
cell.SetValue(rid, data);
}
else
{
data = bytes.Get(offset, bytes.Length - offset);
if (rid == RID.FORMULA)
{
FormulaRecord formulaRecord = record as FormulaRecord;
cell.SetFormula(data, formulaRecord.StringRecord);
}
else
{
cell.SetValue(rid, data);
}
}
colIndex++;
}
}
protected void Page_Load(object sender, EventArgs e)
{
org.in2bits.MyXls.XlsDocument doc = new XlsDocument();
doc.FileName = "TestingAgain.xls";
//doc.Workbook.ProtectContents = true;
for (int s = 1; s <= 5; s++)
{
string sheetName = Request.Form["txtSheet" + s].Replace(",", string.Empty);
if (sheetName.Trim() == string.Empty)
{
continue;
}
int rowMin, rowCount, colMin, colCount;
try
{
rowMin = int.Parse(Request.Form["txtRowMin" + s]);
rowCount = int.Parse(Request.Form["txtRows" + s]);
colMin = int.Parse(Request.Form["txtColMin" + s]);
colCount = int.Parse(Request.Form["txtCols" + s]);
}
catch
{
continue;
}
if (rowCount > 65535)
{
rowCount = 65535;
}
if (rowCount < 0)
{
rowCount = 0;
}
if (rowMin < 1)
{
rowMin = 1;
}
if (rowMin > 32767)
{
rowMin = 32767;
}
if (colCount > 255)
{
colCount = 255;
}
if (colCount < 1)
{
colCount = 1;
}
if (colMin < 1)
{
colMin = 1;
}
if (colMin > 100)
{
colMin = 100;
}
if (sheetName.Length > 35)
{
sheetName = sheetName.Substring(0, 35);
}
Worksheet sheet = doc.Workbook.Worksheets.Add(sheetName);
Cells cells = sheet.Cells;
for (int row = 0; row <= rowCount; row++)
{
if (row == 0)
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, "Fld" + col);
cell.TopLineStyle = 2;
cell.TopLineColor = Colors.Black;
cell.BottomLineStyle = 2;
cell.BottomLineColor = Colors.Black;
if (col == 1)
{
cell.LeftLineStyle = 2;
cell.LeftLineColor = Colors.Black;
}
cell.RightLineStyle = 2;
cell.RightLineColor = Colors.Black;
cell.Font.Weight = FontWeight.Bold;
cell.Pattern = FillPattern.Solid;
cell.PatternColor = Colors.Silver;
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, /*row + col*/ 1.001);
}
}
}
}
doc.Send();
Response.Flush();
Response.End();
}
