Datatype to save excel file in sql server?

问题:

I have a table in which there are two columns : 1. import type, 2. Excel import template.

The second column - "Excel import template" should store the whole excel file.

How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ?

Thanks in advance !

回答1:

Yes, you can use a binary file type. VARBINARY(MAX) is likely to fit the purpose best.

Regarding how to "convert the Excel file to bytes" (it really is bytes from the beginning), we will need to know more about your programming environment in order to help. If you are using .NET, you should be able to do something like this:

var insert = new SqlCommand("INSERT INTO tbl (xls) VALUES (@xls)", conn);
insert.Parameters.AddWithValue("xls", File.ReadAllBytes("template.xls"));
insert.ExecuteNonQuery();

回答2:

varbinary(max)

An Excel file is binary so you can't use characters like varchar(max)

And you can't use IMAGE because it's deprecated as MSDN says

回答3:

Excel does support saving to XML. You could use this and store the sheet in an XML datacolumn, or a varchar(max)